Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 18th, 2003, 12:14 PM
Authorized User
Join Date: Oct 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default Alternative Ways to Close A Form Using Code

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"
   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,


Similar Threads
Thread Thread Starter Forum Replies Last Post
Close all MdiChield form from open one form/Button salman .NET Framework 2.0 6 December 10th, 2007 03:21 AM
Close all MdiChield form from open one form salman .NET Framework 1.x 0 November 8th, 2007 12:32 AM
Code Alternative - Managing Roles ryandoah BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 1 August 1st, 2007 06:14 PM
Alternative of Form.ShowDialog(this) for .NET CF? arif_1947 VS.NET 2002/2003 0 November 9th, 2004 05:12 AM
Code alternative for "View / Zoom / Fit for Report MelP Access VBA 2 December 28th, 2003 07:39 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.