ACC2: How to Prompt User to Save Changes to Record in Form (181820)

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.

When you make a change to a record, and then you either move to a different record or close the form, you are prompted to confirm that you want to save the current record. If you choose No, the record is reset and the operation continues as normal.