Prompt for User to Save a Form

Question

I have one last question for the moment for my class database. For certain forms where a user can view data, I want them to be able to close the form and not be prompted if they did not make any changes. HOWEVER, if they chose to change some data in the form, say to edit a record, I then want them to get a pop-up box prompting the user if they want to save the change. If they select yes, then the old record is overwritten and the change is saved to the record, but if they select no, then the record isn't changed at all and the form closes.

How can I do this? I'm using Access 2016, and what I've tried so far results in errors. Here are 2 methods I've tried in the forms BeforeUpdate event in VBA.

Method 1:

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

Error - "The expression Before Update you entered as the event property setting produced the following error: A problem occured while Microsoft Access was communicated with the OLE server or ActiveX Control."

Method 2:

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

Error - Same as above.

Any help is greatly appreciated!

Thanks!

user_7296893 · Answer

This problem can arise due to the language setting for non-unicode programs being different on the computer it was created at, and on the computer it is running at, or due to changes in the language settings.

Review this Microsoft support page

If that doesn't help, you can also review this answer. While it's very thorough and some of the steps might not be applicable to you, both decompiling + recompiling and rebuilding might fix this error.

Also note: your code doesn't do what you think it does. To save the current record, use DoCmd.RunCommand acCmdSaveRecord. DoCmd.Save saves any changes made to the form object, not to the record. You don't need to save the record in the BeforeUpdateevent, however, since it's going to save at the end of that event as long as Cancel = False

user_6566741 · Answer

Your DoCmd methods do not make any sense at all. Plus, you only need to check whether the reply was no. Try this:

If Me.Dirty Then 'has existing data been changed?
   If MsgBox("Changes have been made to this record." _
       & vbCrLf & vbCrLf & "Do you want to save these changes?", _
       vbYesNo, "Changes Made...") = vbNo Then
      Cancel = True
      Me.Undo
   End If
End If

user_12905679 · Answer

 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 changes?", vbYesNo + vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
      End If
   End If

Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate

Prompt for User to Save a Form

5 Answers