Dirty records and how to correctly save the form´s changes to the underlying query. (2 Viewers)

DBApprentice

On Error GoTo AWF *****:
Local time
Yesterday, 22:39
Joined
Dec 24, 2018
Messages
150
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:

Code:
 If Me.Dirty Then Docmd.Save acForm End If
But then I found a similar command:
Code:
 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

Well-known member
Local time
Yesterday, 21:39
Joined
Apr 9, 2015
Messages
4,339
I always remove the Me.Dirty.
it is more trouble than its worth.
 

JHB

Have been here a while
Local time
Today, 02:39
Joined
Jun 17, 2012
Messages
7,732
..
Code:
... Docmd.Save acForm End If
That is actually for the form not the data in it.
..But then I found a similar command:
Code:
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

On Error GoTo AWF *****:
Local time
Yesterday, 22:39
Joined
Dec 24, 2018
Messages
150
...
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. :confused:
 

isladogs

MVP / VIP
Local time
Today, 01:39
Joined
Jan 14, 2017
Messages
18,186
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?
 
Last edited:

Dreamweaver

Well-known member
Local time
Today, 01:39
Joined
Nov 28, 2005
Messages
2,466
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






Code:
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

Immoderate Moderator
Staff member
Local time
Yesterday, 20:39
Joined
Feb 28, 2001
Messages
27,001
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).
 

Users who are viewing this thread

Top Bottom