MS Access – Save Changes Check Before Saving Record

Often, when a user has changed the contents of a form, they may move away from the current record without actually saving the record.

The following code simply displays a message box asking the user if the want to save their changes.

If they select “Yes” then the changes are saved, if “No” then the changes are disgarded.

The code is placed in the Form_BeforeUpdate event (which is fired, surprisingly, before the form is updated).

Here’s the code:

If MsgBox("Save your changes?",vbYesNo,"Save Changes") = vbYes Then
DoCmd.RunCommand acCmdUndo
End If


Using VBA or a macro containing the SetValue action doesn’t trigger this event.

However, if you then move to another record or save the record, the form’s BeforeUpdate event does occur.

The BeforeUpdate event does not apply to option buttons, check boxes, or toggle buttons in an option group. It applies only to the option group itself.


2 Responses to “MS Access – Save Changes Check Before Saving Record”

  1. 1 AJAY JHA July 29, 2011 at 5:17 pm

    MS Access: Prompt for changes to be saved in Access 2003/XP/2000/97


    Question: Access 2003/XP/2000/97 automatically saves changes to the database when entering information on a form. How can I prompt a user to save changes like in other applications?

    Answer: Since Access automatically save changes directly to the database, the only way to stop this from happening is to intervene on the Form_BeforeUpdate event. At this point, you can prompt the user to save or cancel changes.

    For example, you could place the following code on the BeforeUpdate event for the form.

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim LResponse as integer Dim LMsg as string

    LMsg = “Do you wish to save changes?”
    LResponse = msgbox(LMsg, vbYesNo, “Save changes”)

    If LResponse = vbNo then
    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    End if

    End Sub

    In this example, before the changes are saved, the user is prompted with a message box. This message box asks the user if he/she wishes to save the changes. If the user responds “yes”, then the changes are saved. If the user responds “No”, then the changes are undone and the record is not updated.

  2. 2 Stephen Batich December 19, 2012 at 9:22 pm

    Private Sub Form_Close()
    ‘ If data has changed:
    If DataChange = 1 Then
    Answer = MsgBox(“Do you want to save your outstanding changes?”, vbYesNo + vbQuestion, “Save changes?”)
    ‘ 6 = Yes
    ‘ 7 = No
    If Answer = 6 Then
    ‘save data then exit
    End If
    ‘Do not save, then quit
    End If
    End Sub

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog Stats

  • 226,374 hits




%d bloggers like this: