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!

Answer 1

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

Answer 2

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

Answer 3

1. 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. ** this is Access standard behavior - nothing needs to be done

2. 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. ** this is not standard, and generally not a recommended concept, as all it does is add overhead. One must determine the correct point in time for this trigger and for a screen/form with many fields - it can only be done upon close or record change - in which case one would trigger a Message Box if Dirty (you can look up those terms)

3. 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. ** look up the UnDo method

** but in general the standard design of the product is that all is saved as you go without any prompts. this is the way it should be. there is no real value added to a Save Prompt in that the user's probability of making an error during the data entry versus at the Save Prompt is identical.

Answer 4

Found this code on Microsoft's website, and it works as intended with 0 errors.

Private 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 with the save operation or to cancel it. Then
   ' the action that triggered the BeforeUpdate event is completed.

   Dim ctl As Control

   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?", 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
End Sub

Answer 5

 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