ACC2: How to Prompt User to Save Changes to Record in Form

• Microsoft Access 2.0

SUMMARY

Moderate: Requires basic macro, coding and interoperability skills.

When you move to the next record or close a Microsoft Access form, Microsoft Access automatically saves any changes that you have made to the current record. This article demonstrates how to use a BeforeUpdate event procedure that prompts you to verify the save operation before continuing.

MORE INFORMATION

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file and perform these steps on a copy of the database.

This example uses the BeforeUpdate event procedure in the Customers form to prompt the user to confirm changes before saving.

1. Start Microsoft Access and open the sample database NWIND.MDB.

2. In the Database window, click the Form tab and select the Customers form. Then click Design.

3. On the View menu, click Properties and select Event Properties from the combo box at the top of the dialog box.

4. Click the BeforeUpdate property box, and then click the Build (...) button.

5. In the Choose Builder dialog box, click Code Builder. Click OK.

6. Set the BeforeUpdate property of the form to the following event procedure.

   NOTE: In the following sample statements, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these statements.

         Sub Form_BeforeUpdate (Cancel As Integer)
            ' This procedure checks to see if the data on the form has
            ' changed. If the data has changed, the procedure prompts the
            ' user to continue the save operation or cancel it. Then the
            ' action that triggered the BeforeUpdate event is completed.
   
            On Error GoTo Err_BeforeUpdate
   
            ' The Dirty property is True if the record has been changed.
            If Me.Dirty Then
               ' Prompt to confirm the save operation.
               If MsgBox("Do you want to save?", 4 + 32, _
                     "Save Record") = 7 Then
                  Cancel = True
                  SendKeys "{ESC}"
               End If
            End If
   
         Exit_BeforeUpdate:
            Exit Sub
   
         Err_BeforeUpdate:
            MsgBox CStr(Err) & " " & Error(Err)
            Resume Exit_BeforeUpdate
   
         End Sub
   
   

7. On the Run menu, click Compile Loaded Modules.

8. If no errors occur, save the form.

REFERENCES

For more information about how to prompt a user to save a record in Microsoft Access 7.0 or 97, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q175911
   TITLE     : ACC: How to Prompt User to Save Changes to Record in
               Form(95/97)

   ARTICLE-ID: Q97525
   TITLE     : ACC: UndoCurrentRecord in BeforeUpdate Property Causes Error