Getting error when using Me.Dirty = False

[Seth Schrock]

I have some code on a form that asks you if you want to save the changes to the record before moving on to the next record or closing the form. The code is in the form's BeforeUpdate event. This is what I have currently:

If intResponse = vbCancel Then
     DoCmd.RunCommand acCmdUndo
Else
     Docmd.RunCommand acCmdSave
End If

This works. However, the acCmdSave is very slow; usually about 2 - 3 seconds. However, I also have a Save button that uses:

If Me.Dirty = True Then Me.Dirty = False

This works in about .25 - .5 seconds. My idea was to use this same code in the form's BeforeUpdate event in place of the DoCmd.RunComman d acCmdSave. When I do, I get the following error message:

Run-time error '2115':
The macro of function set to the BeforeUpdate or ValidationRule property for this field is preventing IT Inventory from saving the data in the field.

I checked and there is no validation rule on the field that was changed. Here is the entire BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim intResponse As Integer

If intSaved = 0 Then
    strMsg = "you have made one or more changes to this Record.  Do you wish to save this record" _
             & "with those changes?" & vbCrLf & vbCrLf & "Click OK to save changes, or Cancel to" _
             & "Undo these changes."
             
    intResponse = MsgBox(strMsg, vbQuestion + vbOKCancel + vbDefaultButton1, "Prompt to Save Record")
    
    If intResponse = vbCancel Then
        DoCmd.RunCommand acCmdUndo
    Else
        'DoCmd.RunCommand acCmdSave
        Me.Dirty = False
        
    End If

Else
    intSaved = 0
End If

End Sub

Just to let you know, intSaved is publicly declared at the top of the form's VBA page. I use it to know if the Save button has been clicked (which sets the variable to 1).

What is wrong with my code?

[zmbd]

Think of it as a record locking conflict.
The Before Update event has fired.
Locking the record for validation.
Then If Me.Dirty = True Then Me.Dirty = False line executes in your event...
Because there is a difference between the old and new values this line of code causes the Before Update Event to fire again and when the second itteration attempts to lock the record it finds the record is already locked and thus you get the error message.
:)
(you can see the order of trip by using a debug.print "eventname" in the before insert, after insert, before update, after update etc... there's also a list of event order: Form Objects

[Seth Schrock]

Then why does it work with the save button? Because the BeforeUpdate event is bypassed with the intSaved variable set to 1? Does acCmdSave not lock the recordset? Or is that why the difference in time?

A lot of questions :) I just want to understand how this works so that I can use each way in the most advantageous way.

[Seth Schrock]

In looking for other methods of saving a record using VBA, I found an article that says that acCmdSave doesn't save the record, "acCmdSave saves *design changes to the structure of the object*" Alternative to acCmdSave. It also says that I should use acCmdSaveRecord instead. Is this correct? If so, then I would expect that acCmdSaveRecord would have the same issue as Me.Dirty = False. I will test this out shortly. At least I know what the issue is and what I need to work around.

[Seth Schrock]

Just tested acCmdSaveRecord . I got the same error message.

[zmbd]

Seth,
Without knowing your form it's hard to give you too much...

1) acSave - yes, technically, it is for saving the form, table, etc... it will most often force a save on a dirty record; however, not always.

2) acSaveRecord - yes, technically, it is the one you more than likely should be using. This will cause the same error as you've already seen for the reason given in #2

3) If you are in the before update event, the record is already attempting to be saved... trying to resave it in this event seems, well, odd. However, I don't know the design of your form...

4) Code to check if the form is dirty before close is a good idea. I usually work around that by locking all of my record forms and using the footer as a "new" record or "Edit" current record zone that has a "save" button (you've seen that I think in one of the other threads yes?), which leads to...

4a) You can place your command button to save the record and in the onclick event use the If Me.Dirty = True Then Me.Dirty = False You can have the button show up in the on-dirty event and hide itself when clicked or in the after update event. Nice thing here is that if the record isn't dirty (new or old) usually no errors. It will cause the before-insert, after-insert, before-update events to fire (all if new record just update if old) so that you can use validation code. I will do this in my "management " level forms as a visual... the caption reads "SAVE Changes?" and is usually Bright-Red letters on Light-Blue background.... hurts the eyes due to colour contrast!

[Seth Schrock]

So basically, all I need to do is if the answer to the messages box is not vbYes (the else portion) then just exit sub and the database will save the record for me.

And yes, I have seen your New and Edit portion in the form footer.

Edit: I just tried taking out the Else portion of my code and it works just fine. The records are saved just like I need them to be and very quickly too.

[zmbd]

Yep... you got it then.
One thing I would do is just before the undo command (between lines 12 and 13 in OP is insert Cancel=True by setting this true, you're telling Access to abort the changes and handle everything else related there to; however, make sure you leave the undo command in there to revert the record.
If you set it false, then you need to make sure that the control handles all of the changes.

[Seth Schrock]

Setting Cancel=True would stop the move to another record or closing of the form, correct?

[zmbd]

Yes/No... yes if the event is triggered from a record move then setting the cancel to true should prevent the record movement (notice I used the word "should").
If the event was triggered do to a form close then it will prevent the record update however I the form will close; however, access should (notice I used the word "should" again) warn the user that edits will be lost.