Microsoft Access automatically saves any changes we make to a record, whether we close the form or move to a new record.
I will show you a way of prompting a user to save the changes.
Here is my form before I make any changes to the record.
Now I add the word ‘Test’ into the ‘Last Name’ field.
I then move to the next record and get the message informing me that the record has changed and do I want to save it.
If I answer ‘Yes’ the changes will be saved.
If I answer ‘No’ my changes will not be saved.
In order to do this I added a bit of VBA code.
Go into form design and bring up the form properties.
Click on the top part of the form:
Go up to the ‘View’ menu and select ‘Properties’:
We want to look at the event properties, specifically the ‘Before Update’ event:
Click the small button with the dots, the ellipsis (...), to bring up the code window:
You will see the code procedure for the ‘Before Update’ event:
Private Sub Form_BeforeUpdate(Cancel As Integer) End Sub
Add the following code between the Private Sub Form and the End Sub:
'If the form data has changed a message is shown asking if 'the changes should be saved. If the answer is no then 'the changes are undone On Error GoTo BeforeUpdate_Error If Me.Dirty Then 'if record has been changed the dirty property ' is set to true Display message to save the record If MsgBox("The record has changed - do you want to save it?", _ vbYesNo + vbQuestion, "Save Changes") = vbNo Then Me.Undo End If End If BeforeUpdate_Exit: Exit Sub BeforeUpdate_Error: MsgBox Err.Description Resume BeforeUpdate_Exit
You should end up with:
Private Sub Form_BeforeUpdate(Cancel As Integer) 'If the form data has changed a message is shown asking if 'the changes should be saved. If the answer is no then 'the changes are undone On Error GoTo BeforeUpdate_Error If Me.Dirty Then 'if record has been changed the dirty property ' is set to true Display message to save the record If MsgBox("The record has changed - do you want to save it?", _ vbYesNo + vbQuestion, "Save Changes") = vbNo Then Me.Undo End If End If BeforeUpdate_Exit: Exit Sub BeforeUpdate_Error: MsgBox Err.Description Resume BeforeUpdate_Exit End Sub
Now save the code and close the window.
Run the form.
Enter something in your form fields and try moving to another record or even closing the form.
You should get the message asking if you want to save changes.
Although having Microsoft Access automatically save our changes is nice, sometimes we may wish to have greater control on this.
By using the ‘Me.Dirty’ property in code we can check for changes and control if they get saved.