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.

Why?

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.

xTrap.ocx

Caveats and Naughtiness etc.
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

ActiveXCtl1.Object.Enabled = True >>> Closeing event IS called. Default.
Simply drop on the form.
http://www.papwalker.com/public/xtrap2.ocx

 


zxTrap.dll

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
Option Explicit


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
End Sub

In the form Close event...

Private Sub Form_Close()
Set FormX.Form = Nothing
Set FormX = Nothing
End Sub
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.

Freeware.
Use this Library at your own discretion.
Report Faults to info@papwalker.com