Access Form Data Updates

When using a form in Microsoft Access, you can typically have a gateway directly to your database. Once you make an update, it is updated in the database, sometimes without having to press a save button or the like. So what happens if you make a mistake and add, delete or mangle what you have in a field? You might go to the next database record and not know what you did. Worse, it can create a problem for your users or others who rely on your data.

The best solution is to add a confirmation yes or no type box that presents itself to you. Below is a good example of code that will help keep you save and ask you before the changes are committed. Add this code by right clicking in the field in design mode, then clicking over to event and choose "Before Update."

   If MsgBox("You have made changes to this record." _
       & vbCrLf & vbCrLf & "Do you want to save the changes?" _
       , vbYesNo, "Changes Made") = vbYes Then
           DoCmd.Save
       Else
           Me.Undo
   End If

Now you are set, but at a client site recently, they asked that the message box only pop up if the field was not null before. I agreed because having to confirm on every field could slow you down. Here is the code if you only want to display if you updated a non-null or even an empty field. Otherwise, it will come up every time. Add the code here, based on the previous example:

    If IsNull([Data].OldValue) Then
        DoCmd.Save
    Else
        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
            Me.Undo
        End If
    End If