Creating User Prompts in Access 2007

Article
05/05/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to display a custom dialog box in Microsoft Office Access 2007 to prompt users to confirm changes before saving a record.

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007

Joel Krist, Akona Systems

May 2007

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 to a form.
Adding the record save confirmation code to the event procedure.

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.
       DoCmd.RunCommand acCmdUndo

       ' Cancel the update.
       Cancel = True
   End If
End Sub

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: