MS Access 2010 - Prompting user to save changes

Question

I have developed a form and I would like to prompt the user to save changes before they navigate away from the modified record.

If the user attempts to navigate away, I want a prompt to appear asking if they wish to save changes, upon which they may select "Yes" or "No".

I have been informed that the Before Update event is the one I need to focus on, but I keep receiving the "the expression before update you entered as the event property setting produced the following error" message.

These are the steps I take before reaching the error:

1. Change view to Form View
2. Make a change anywhere in the record via the form
3. Attempt to navigate away from form via navi-buttons that I put into my form (which work just fine if no changes are made but which fail to do anything if a single change has been made)
4. Nothing happens, so I revert to Design View, to receive the following error notification

5. I press OK and then receive this message:

And then I go back to Square One.

Furthermore, any Conditional Formatting has stopped working altogether since this problem has arisen; I do not know for certain if the two are linked, but thought it worth mentioning.

Any ideas how this can be achieved (ideally error-free)? Unfortunately, I cannot post my system up as it deals with highly confidential data.

UPDATE:

I have tried a variety of codes which I have modified, all to no avail. At present, I have removed any such code altogether, but the code I have tried in the past is something to the effect of:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control

On Error GoTo Err_BeforeUpdate

If Me.Dirty Then
   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 1

1. Open your form in Design View

2. Right-click on the form and click on "Properties" from the pop-up menu

3. Scroll down to the "Before Update" event

4. All the way on the right you should see a box with an ellipse ("...") in it. Click that box to open the code associated with the Before Update event.

5. If it opens to a blank sub, you haven't assigned the event properly. Make sure the event is called "BeforeUpdate" (one word) and not "Before Update" (two words).

Your error indicates that this is likely the cause of the issue.

Answer 2

The default behaviour for a Microsoft Access bound form is to automatically save the record whenever the user does the following:

Moves to another record, Closes The form or explicitly clicks the save button on the ribbon

Therefore I thinks it may be redundant asking the user to confirm if they wish to save.

The forms before update event is generally used for validation so you can check the data that has been entered into the controls and decide in code whether to allow the data to be saved.

To prevent the data from being saved you would change the Cancel variable for example:

Cancel=True
MsgBox "There is a problem with the data entry", vbExclamation, "Please Check Your Data"

The user would then have to press escape or click Undo on the ribbon to escape out of edit mode

Regarding the error you are getting please look into and try the decompile switch documented at the following web page:

Decompile Switch

I occasionally get errors when working with and saving VBA code over and over. Doing the decompile can usually fix this problem. However make sure you do a backup of the database before you start.

Also you could try exporting out your form as a text file and then importing it back in again using the following code which will help if the form has become corrupted:

    Application.SaveAsText acForm, stringFormName, stringFolderName & "\" & stringFormName & ".txt"
    Application.LoadFromText acForm, stringFormName, stringFileName