I have spent a couple of days looking for a way to confirm data changes with the option of cancelling to continue editing and found nothing that worked the way I wanted.
Finally I have managed to work a little code that does exactly what I am after.
I have attached this to a cmdClose button on my form.
Code:
Private Sub cmdClose_Click()
If Me.Dirty = True Then 'Check for data changes
Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel, "Save Changes")
Case vbYes 'Save changes, close form
DoCmd.Close
Case vbNo 'Undo changes, close without saving
Me.Undo
DoCmd.Close
Case vbCancel 'Cancel close, continue editing
DoCmd.CancelEvent
End Select
Else 'No data changes, close form
DoCmd.Close
End If
End Sub
Nice and simple. If you have changed the data, you get the option to save and close (Yes), undo and close (No) or continue editing (Cancel).
Hopefully anyone else who has been searching for this without much luck can get use of it.
~Matt