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.

• Setting the Cancel argument will prevent the form from closing in the same manner as setting the Form Unload cancel argument. However you can still close the form by going into Design View, closing the Database or closing Access.
  

• Using Me.Undo within the event procedure may cause a memory fault and cause Access to close. The workaround is to use the trusty...
  DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
  

• Not setting the XTrap Form property to nothing in the from Close event is to be avoided. You will receive a warning at runtime if you have forgotten. Although I dispose of the Form object reference and clean up as best I can, it may leave Access with a stuck reference. It hasn't yet but...
  

• The library will also warn you (at runtime) if you try to set the XTrap Form property again without first setting it to nothing (which 'unhooks' the form) This is not a serious error, just fix your code.
  

• It will also warn you (at runtime) if you set the XTrap Form property to nothing when it was never set in the first place. (it was never 'hooked') This is not a serious error, just fix your code
  .

• Be careful to set the XTrap Form property to the Form the class is instanced by. (Of course adventurous souls playing with the idea of hooking another forms closing event do so at their own risk<g>)
  

• A CODE RESET IS FATAL. Do not reset code with out unhooking.
  Save your work regularly.
  

• Do not be put off by the above caveats. There are other Access tools in common use that do not cleanup or protect the system as well as this one.

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.

• Just create a folder where you would prefer the dll the live.
• Copy the database to that folder.
• Open the database.
• Press the ctrl-F10 key or call the EnumRefs() function.

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