Awhile ago, I had a topic written regarding forcing users to stay
on a form's current record if one or more errors existed. What I
needed to was to have them either either correct the error(s) or to
cancel the update.

After getting some help and doing some experimentation, it seems
that manually firing off DoCmd.CancelEvent will work in the
BeforeUpdate subroutine within a form, which I have gotten to work
successfully. If the user has one or more errors, issuing the
DoCmd.CancelEvent will cause Access to generate the following error
message to the user: "You cannot save the record at this time.
Microsoft Access may have encountered an error while trying to save
the record. If you close this object now, the data changes you made
will be lost. Do you want to close this database object anyway?".
This is exactly what I need to pop up.

The problem that I am having is that this CancelEvent code does not
execute when using the BeforeUpdate is called after a DoCmd.Close is
executed from within a form's background code. For example, I have
this check in the BeforeUpdate() of a form:

If IsNull(Me.txtOrderID) Then
   MsgBox "You must specify an order id", vbExclamation, "ERROR"
   Me.txtOrderID.SetFocus
   DoCmd.CancelEvent
   Exit Sub
End If

Normally, this code should fire off if the user tries to close the
form, go to another existing record, or go to a new record - all
three events will trigger the form's BeforeUpdate if any changes have
been made to the form.

If I close the form using the form's close button, selecting
File | Close from the menu, or using Ctrl-F4, the code works exactly
as shown with my error message displaying, followed by Access' error
message, which is fine. However, if I invoke a form close by using
DoCmd.Close using an command button, MY error message will be
displayed, but the DoCmd.CancelEvent does not seem to execute in the
same way by displaying Access' error message - it looks as if it is
being executed when stepping through the code with the debugger, but
it continues through the rest of the BeforeUpdate code and the form
closes without saving the changes. It's as if the user had indicated
to cancel the update without ever being prompted without being warned
of the data loss. I need that prompt! :)

So far, I have been able to successfully get this trigger to work by
substituting SendKeys "^{F4}" for the DoCmd.Close within the code
in my form's close command button. But when I use any of the
following options,

 - DoCmd.Close
 - DoCmd.Close acForm, Me.Name
 - DoCmd.acRunCommand acCmdClose
 - DoCmd.acRunCommand acCmdCloseWindow

I get the exact same results as the DoCmd.Close.

Does anyone know of some other command that can do what the
"SendKeys ^F4" does, but maybe using a RunCommand or other
option/command? This application on which I am working has
over 150 forms that will need to be updated.

Thank You,

Warren
:D