The X Files
by Peter Walker - papwalker.com
The zxTrap.dll (light weight) is designed to add functionality to Access '97 / 2000. It provides a 'Closing' event.
The xTrap2.ocx does excactly the same but using a control and requires no Form code other than the event procedure.
If when closing a 'dirty' form via the 'X' button or a menu, and the record can not be saved because of table constraints or user constraints (such as setting the Cancel argument in the before update event to true) then an Access message is displayed.
You can prevent this message by using the On Error event and setting response = acDataErrContinue. This will undo the record and let the form close quietly. Whilst you can trap the error and display your own message, You can't alter the outcome. The record will be dumped. If you want to stop the form from closing, you have to Cancel the Unload event. However, by that time the record is gone. You can not easily keep the dirty record and avoid this message. (Unless you use the zXtrap dll of course!)
Bottom line - cancelling the form before update will not stop the form from closing.
What you can do is, during the error event, trap the failed save error and save all the control values away to an array and optionally set a form level flag. In the form unload event you can inspect the flag, (or check UBound of the array. This means the record was dumped by the acDataErrContinue so you can the restore the control contents from your array, tell the user about the problem and offer the choice to keep working with the data or dump it. You could ask this prior to restoring the data but it may be worrying to the user to see the data gone behind the message. There are still traps with this method.
Here is the normal flow of events for closing a dirty form with an un-saveable record.
BeforeUpdate > Error > Unload > Deactivate > Close.
The flow of events when using the zXTrap.dll
Closing > BeforeUpdate > Error > Unload > Deactivate > Close.
The Closing event added by the XTrap is
called prior to the Before Update. Cancelling it will stop the
process there. No error.
No known issues as this time. Tested with > 5000 iterations of Form open - Close - Open Design - Close.
Same built in safegaurds as dll version.
Code control properties
ActiveXCtl1.Object.Enabled = False >>> Closeing event is NOT called
= True >>> Closeing event IS
Simply drop on the form.
Caveats and Naughtiness etc.
Instructions - READ CAREFULLY
There are numerous
approaches to using this new event.
See http://www.papwalker.com/public/closingevent.mdb for ideas.
The database above has the dll included.
The Autokeys macro will
activate code to unpack the dll to the database folder and
register it. You are ready to go.
Or you can just download the dll from http://www.papwalker.com/public/zxtrap.dll
It must be then registered with regsvr32. Requires VB6 runtime libraries.
A VB5 version http://www.papwalker.com/public/zxtrapv.dll
How to Register your Active-X library in the system registry using Access
The ABSOLUTE requirement are several lines of code in the form module.
In the Declarations section...
Option Compare Database
Private WithEvents FormX As xTrap
In the Form Open event...
Private Sub Form_Open(Cancel As Integer)
Set FormX = New xTrap
Set FormX.Form = Me
In the form Close event...
Private Sub Form_Close()
Set FormX.Form = Nothing
Set FormX = Nothing
You can of course name the variable FormX anything thing you like.
To set the event, select the class in the Code Window object box.
Use this Library at your own discretion.
Report Faults to firstname.lastname@example.org