Before Update Event on form and error 2169

[MarcEur]

To handle the use of the X in the right top corner of a form i am using the before_update event of the form and set the Cancel = true. This brings up a message
"You can't save this record at this time.
<my app name> may have encountered an error while trying to save a record. If you close this object now , the data changes you made will be lost. Do you wan to close the database object anyway?

No error code number is indicated.

YES and NO buttons
Yes will reset the information on the screen and no will put me back to the form.
NO will bring me back to the form

I want to avoid the above message and have the user just returned to the form, which is the NO option

I was able to catch the error with a custom message in the following way

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 2169 Then
MsgBox (&quot;test&quot
Response = acDataErrContinue
End If
End Sub

But using Response = acDataErrContinue will trigger YES
If I do not use Response = acDataErrContinue then I get the message with the YES and NO buttons.

I have spent no several hours on trying to figure this out without any result
What am I doing wrong
Any help is appreciated

 

[RickSpr]

What you're doing wrong is that you're trying to stop them from using the X button. When you cancel the BeforeUpdate event, it cancels the update just fine, but it doesn't cancel the form closure. Microsoft didn't give us any way to do that. (Regular Visual Basic forms have a QueryClose event that allows you to cancel form closure. That would've been just what you need.)

But here's an alternative that should work. It depends on which version of Access you're using.
Access 97:
For every control that's bound to a field and can be updated, create an AfterUpdate event procedure that sets Me.CloseBox to False.
Access 2000 or 2002:
Create a Form_Dirty event procedure that sets Me.CloseBox to False.
Both versions:
Create a Form_AfterUpdate event procedure that sets Me.CloseBox to True.

In effect, this disables the X button (and the Close item in the control menu) whenever there are unsaved changes, and reenables it when the changes are saved.

One drawback: If they choose Edit|Undo, the button will remain disabled. To enable it again, they'll have to do a &quot;dummy&quot; update, such as typing a space on the end of a text box and saving the changes. The only way to avoid this drawback is to replace all Undo buttons, menu and shortcut menu items, and keyboard entry of Ctrl-Z or Esc with custom buttons, menu items, and keyboard trapping code. Sheesh!
Rick Sprague