databasedev.co.uk - database solutions and downloads for microsoft access

Confirm Changes using the Before Update Event

When creating the user interface in Microsoft Access, the database designer should always be looking at ways to simplify the data entry process, prevent bad data from getting into the database or ask for confirmation when changes have been made to existing data.

This article concentrates on changes made to existing data, and provides the user with a confirmation option when modifying data in a Microsoft Access form.

If we take the following example Microsoft Access form, and view the data that is currently entered into each control in the form:

Form displaying the original data in the record.

If the database user makes any changes to the current record, we would like to pop up a message box, confirming that the user is sure that they wish to make these changes before moving to a different or new record.

To do this, we need to add a procedure to the Before Update event of the Microsoft Access form. The BeforeUpdate event occurs before changed data in a control or record is updated.

The BeforeUpdate event applies only to controls on a form, not controls on a report.

The BeforeUpdate event does not apply to option buttons, check boxes, or toggle buttons in an option group. It applies only to the option group itself.

Using the following code, we can display the message box to the user, asking them to confirm if they would like to make the changes or not:

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Provide the user with the option to save/undo
'changes made to the record in the form

    If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            DoCmd.Save
        Else
            DoCmd.RunCommand acCmdUndo
    End If
End Sub

When changes are made to data in the form, and the user tries to move to a new/next record, or they try to close down the form they will be presented with the following message box:

Message Box displayed when changes are made to data in the record (note change made to the Asking Price data in this example)

If the user chooses Yes from the message box, the code will run the following statement, saving the new data:

DoCmd.Save

If the user chooses No from the Access message box, the code will run the following statement, undoing any changes made to the data:

DoCmd.RunCommand acCmdUndo

Readers of this article may also like to check out other similar articles on this subject:

• Validating Required Data in Microsoft Access Text boxes
• Requiring Data Entry into Form Fields
• Undo changes made to current record