In Microsoft Office Access 2007, by default, users are not prompted to confirm changes after modifying and saving records on a form. But often you might want to prompt users to confirm their changes before the record is saved.
You can use a BeforeUpdate event procedure to display a confirmation prompt and handle a user's response to either cancel or continue with the save.
This visual how-to topic illustrates how to display a custom dialog box to prompt users to cancel or continue with saving changes to a record.
To illustrate this process, this section describes the following steps:
Adding a BeforeUpdate Form Event Procedure
The first step is to load the form and display it in Design View. Then you add the BeforeUpdate form event procedure to the form.
To add the event procedure
In the Navigation Pane, right-click the form name and select Design View.
In Design View, select the Design tab on the Office Fluent Ribbon.
In the Tools group, click Property Sheet.
On the property sheet, in the Selection list, select Form.
Select the Event tab to display the form's events.
Locate and click the Before Update event, and then click the small down arrow.
Click [Event Procedure], and then click the ellipsis button (...) next to the arrow.
This loads Visual Basic and displays the body of the Form_BeforeUpdate event procedure.
Adding Code to the BeforeUpdate Event Procedure
The next step is to modify the Form_BeforeUpdate event procedure so that it uses the MsgBox function to prompt the user to confirm changes and save a record in the form.
The following code checks the return value from the MsgBox call. If the user responds No to the confirmation, the code sets the Cancel argument that is passed to the event procedure to True, which tells Access to cancel the record save.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer
' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."
' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
' Check the user's response.
If iResponse = vbNo Then
' Undo the change.
' Cancel the update.
Cancel = True
Frequently you want users to confirm that they want to save changes to records that they have modified in Access 2007 forms. You can use a BeforeUpdate event procedure to display a confirmation prompt and handle a user's response to either cancel or continue with the save.
The key steps in this process are as follows:
Add a BeforeUpdate form event procedure to a form.
Add the record save confirmation code to the BeforeUpdate event procedure.