One of the biggest issues with Access for novice users is that it is atypical in its saving methodology in the sense that unlike pretty much every other piece of software, it automatically commits changes without user intervention, or even prompting to do so.

So what about making Access behave like most every other application, that is to not have it save changes unless the user specifically asks the system to do so?!

Can we make it so forms employ a Save button, otherwise any changes are NOT committed?

 

The Basic Idea

As stated above, the general idea here is to replicate what is done in Excel, Word, PowerPoint, NotePad, … and pretty much every other piece of software known to man!  I wanted to have the ability to exit a form without changes being saved, unless I specifically told the form to do so!

I wanted my form to basically employ a Save button. I then also decided to add a Cancel button to rollback changes if so desired.

So how can we achieve this type of behavior?
 

The Code

The code is pretty simple at the end of the day and utilizes a few Form level events in combination with a Command button click event.

Now, this can be setup in a couple different manners, below is one option where the user must select Save or Cancel to be able to move to another record when ever they’ve made any change to the current record.

Assuming a Save button named ‘cmd_Save’ and a Cancel button named ‘cmd_Cancel’, we could implement this by doing:

Option Compare Database
Option Explicit

Private frm_bSaveRecord       As Boolean


Private Sub cmd_Cancel_Click()
    If Me.Dirty = True Then Me.Undo
    frm_bSaveRecord = True
    Me.cmd_Save.Enabled = False
    Me.cmd_Cancel.Enabled = False
    'We could set the focus on the previous control or a specific control at this point in time!
End Sub

Private Sub Form_AfterUpdate()
    frm_bSaveRecord = True    'Reset variable
    Me.cmd_Save.Enabled = False
    Me.cmd_Cancel.Enabled = False
    'We could set the focus on the previous control or a specific control at this point in time!
End Sub

Public Sub Form_BeforeUpdate(Cancel As Integer)
    If frm_bSaveRecord = False Then
        If vbNo = MsgBox("This record has been modified.  Would you like to save the changes?", _
                         vbYesNo Or vbQuestion, "Save Current Changes?") Then
            Me.Undo
        End If
    End If
End Sub

Private Sub Form_Current()
    frm_bSaveRecord = True    'Initialize/reset variable
    Me.cmd_Save.Enabled = False
    Me.cmd_Cancel.Enabled = False
    'We could set the focus on the previous control or a specific control at this point in time!
End Sub

Private Sub cmd_Save_Click()
    frm_bSaveRecord = True 'Enable saving
    If Me.Dirty = True Then Me.Dirty = False 'Save
End Sub

Private Sub Form_Dirty(Cancel As Integer)
    frm_bSaveRecord = False
    Me.cmd_Save.Enabled = True
    Me.cmd_Cancel.Enabled = True
End Sub

Now, the user must click the Save button for any changes to be committed to the tables.

If we wanted, since we repeat some code in 3 places, we could clean things up a little and do:

Option Compare Database
Option Explicit

Private frm_bSaveRecord       As Boolean


Private Sub cmd_Cancel_Click()
    If Me.Dirty = True Then Me.Undo 'DoCmd.RunCommand acCmdUndo
    Call ResetSave
End Sub

Private Sub Form_AfterUpdate()
    Call ResetSave
End Sub

Public Sub Form_BeforeUpdate(Cancel As Integer)
    If frm_bSaveRecord = False Then
        If vbNo = MsgBox("This record has been modified.  Would you like to save the changes?", _
                         vbYesNo Or vbQuestion, "Save Current Changes?") Then
            Me.Undo
        End If
    End If
End Sub

Private Sub Form_Current()
    Call ResetSave
End Sub

Private Sub cmd_Save_Click()
    frm_bSaveRecord = True 'Enable saving
    If Me.Dirty = True Then Me.Dirty = False 'Save
End Sub

Private Sub Form_Dirty(Cancel As Integer)
    frm_bSaveRecord = False
    Me.cmd_Save.Enabled = True
    Me.cmd_Cancel.Enabled = True
End Sub

Private Sub ResetSave()
    frm_bSaveRecord = True    'Initialize/reset variable
    Me.cmd_Save.Enabled = False
    Me.cmd_Cancel.Enabled = False
    'We could set the focus on the previous control or a specific control at this point in time!
End Sub

 

Class Module

Just a quick note that this type of behavior, if you are wishing to implement it throughout your database, would best be implemented using a Class Module. Centralize the code, standardize the behavior and then simply initialize it in each form by calling the class module.