"P" <plavallee (AT) rcn (DOT) com> wrote

The acSavePrompt is referring to saving design changes to the form, not to data
changes which are saved by default. If you want a "Do you want to save..." prompt
you'll need to code it in the BeforeUpdate of the form.

The "acSavePrompt" is about saving the form (e.g. its filter property). It
has nothing to do with saving the record.

If you want to be prompted before the record is saved, you must use the
BeforeUpdate event of the *form*:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MsgBox("Save changes?", vbYesNoCancel)
Case vbYes 'Do nothing: it saves by default.
Case vbNo 'Undo the changes, and close.
Cancel = True
Me.Undo
Case vbCancel 'Don't close, and don't save.
Cancel = True
end Select
End Sub

Now your code should explicitly try to save before exiting. If that fails
(as it will in the Cancel case), you need error handling:

Private Sub cmdClose_Click()
On Error Goto Err_Handler
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.Name

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 3314, 2101, 2115 'can't save: just ignore these.
Case Else
MsgBox "Error " & Err.Number & " - " & Err.Description
End Select
Resume Exit_Handler
End Sub

Note that the explicit save (setting Dirty to false) is necessary due to a
bug in Access that just silently discards your edits and closes the form
with no warning if there is any reason why the record can't be saved (e.g. a
validation rule is not meet, or a required field is not present).
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")
"P" <plavallee (AT) rcn (DOT) com> wrote

Thank you Allen for your help. It works fine whenever the form is closed
either using the Close button or the x mark in upper-right corner. I also
have a Save button which runs: If frm.Dirty Then frm.Dirty = False
How can I prevent the code frm_beforeupdate() from executing when users
click on the Save button. I don't want them to be prompted for saving when
they are clicking Save.
Thank you. P

"Allen Browne" <abrowne1_SpamTrap (AT) bigpond (DOT) net.au> wrote

I use this code and it works fine. However, when my form is dirty, if I
close the form using the x mark and answer No to "Save Changes?, I get error
2169: Application-defined or object-defined error. I am not sure about its
meaning. I am tempted to disregard it since the form closes fine without
saving my changes after I acknowledge the error. It seems to happen when
Me.Undo executes. Any thoughts? Thank you.


"Allen Browne" <abrowne1_SpamTrap (AT) bigpond (DOT) net.au> wrote

Error 2169 is just warning you that you cannot save the record. You can
ignore the message, but I'm not sure how you can prevent it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")
"P" <plavallee (AT) rcn (DOT) com> wrote