Dirty records and how to correctly save the form´s changes to the underlying query.

[DBApprentice]

Hey folks!

I have been working on the Save_Click event of my form and I have raised error 2046 sometimes, saying the SaveRecord wasn´t available. I have used RuralGuy´s advice to work this around:

 If Me.Dirty Then Docmd.Save acForm End If

But then I found a similar command:

 DoCmd.RunCommand acCmdSaveRecord

Unfortunately they both raise the same error sometimes.

Studying a little more and I now understand Access will save the form automatically if I close the form or move to next/previous record and that´s why you have to check the Dirty state before actually saving it to avoid raising the error - please correct me if I am wrong.

That made me wonder what would be the correct code to avoid raising errors and save the record through the form and also canceling dirty records. I am thinking maybe I could embed Undo code on the cmdExit_Click and cmdClearForm_Click events - my forms do not have arrows to move fwd or bwd, but do have a "cmdEdit" button to enable the mandatory fields to be changed.

So the bottom line is given there are two similar commands ("DoCmd.Save" and "DoCmd.RunCommand acCmdSaveRecord"):

1. How to correctly code to save a form´s changed information to the underlying query?

2. Besides, am I right to assume I can add Undo on the ClearForm_Click()?

I appreciate all the help!

 

[Ranman256]

I always remove the Me.Dirty.
it is more trouble than its worth.

 

[JHB]

..

... Docmd.Save acForm End If

That is actually for the form not the data in it.

..But then I found a similar command:

DoCmd.RunCommand acCmdSaveRecord

That is however for data, but if you are in a database where the command is not available, it failed.
Eg. if you are debugging the code in the VBA window, here is the command not available and an error arise

 

[DBApprentice]

...
That is however for data, but if you are in a database where the command is not available, it failed.
Eg. if you are debugging the code in the VBA window, here is the command not available and an error arise

If I use the "DoCmd.RunCommand acCmdSaveRecord" then I will be putting my user on a , let´s say, error safe mode? At least regarding the 2046.

 

[isladogs]

If you have a bound form then Access will save the record as soon as you move to another record or close the form. In such cases, it isn't necessary to save explicitly.

However if you have an unbound form or you wish to save changes whilst still on the same record, you can use various commands to save including those you gave in your earlier posts.
I prefer to use If Me.Dirty Then Me.Dirty=False
Of course that that can be shortened to just Me.Dirty=False.

If you need to update a form based on changes made elsewhere e.g. Update main form based on subform changes, you can requery or possibly recalc or refresh. These all (together with repaint) do similar things but aren't interchangeable.

For more info on the four methods see http://www.projectperfect.com.au/blog/microsoft-access-requery-recalc-refresh-and-repaint/ http://www.projectperfect.com.au/blog/microsoft-access-requery-recalc-refresh-and-repaint/

Recommend you use error handling in all your procedures.
Not at my computer right now, but from memory I think error 2046 occurs when you are trying to update something that isn't available e.g. Its been closed. Does that seem relevant here?

 

[Dreamweaver]

Eny errors you may get should be handled in your error code you can track error 2046 if need be.


On Error GoTo Err_HandleErr



Your code here

Exit_HandleErr:
    Exit Sub
    
Err_HandleErr:
    Select Case Err.Number
        [COLOR=Navy]Case 2501[/COLOR] 'Cancel = True
            Exit Sub 

       Case 2046 'Your Error
           'Do Something about it
            Exit Sub
        Case Else
            MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
            Resume Exit_HandleErr
        Resume
    End Select

 

[The_Doc_Man]

You've got a bunch of discussion. I'll just add to / corroborate what you've got, hopefully in a compact statement.

Two issues can trigger that 2046 (Save not available).

When you do the DoCmd.Save acForm, ... you are saving the form itself, which can only be done in design mode. So that is why you get the 2046 from something in FormView mode.

When you do the DoCmd.RunCommand acSaveRecord, you are saving form contents. This can only be run from a bound form that is dirty and capable of being saved. So you have to have (a) a form bound to something that (b) is updateable and (c) the form's controls must have at least one value that is different from the value in the underlying form. The tricky one is (b) in that whatever you are using for .RecordSource on that form has to be an updateable SQL SELECT query (or the name of a table or updateable stored query).