CODEWITHSUNDEEP
ms-access
Ryan Miller
Ryan Miller

Reputation: 315

What VBA event is triggered when closing a form?

If I don't fill out all the required fields in my form, I get an ugly error when I click the "x" in the upper right corner. I would like to override the default error with a custom error message, but I can't figure out which VBA event to associate this code with. the Form_Close event doesn't seem to work when I put an error handler in there.

Access 2010

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Err_Unload          ' Initialize error handling.
    'insert routine
Exit_Unload:                      ' Label to resume after error.
    Exit Sub                      ' Exit before error handler.
Err_Unload:                       ' Label to jump to on error.
    'MsgBox Err & " " & Error$    ' Place error handling here.
    Resume Exit_Unload
End Subenter code here

Still receiving errors even while using this code.

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Err_Unload          ' Initialize error handling.
    ' Code to do something here.
Exit_Unload:                          ' Label to resume after error.
    Exit Sub                  ' Exit before error handler.
Err_Unload:                           ' Label to jump to on error.
    'MsgBox Err & " " & Error$           ' Place error handling here.
    Resume Exit_Unload
End Sub

Upvotes: 3

Views: 9895

Answers (4)

Bobort
Bobort

Reputation: 3218

There is actually a form event called On Error, which has two arguments: what the error is and what response Access should perform.

Upvotes: 1

HansUp
HansUp

Reputation: 97100

When you attempt to close a form which has unsaved changes to the form's current record, Access will first attempt to commit those changes. You can't intercept that action from Form_Unload or Form_Close.

BTW, although you didn't mention it, I would expect you to see the same thing when navigating to a different record if the record you're navigating away from includes unsaved changes with missing values for required fields.

In the case where your form's record source includes a table with an autonumber field, use the form's before update event to check whether required values are present. If there is no autonumber field involved, consider doing the checking from both the form's before insert and before update events.

However if the whole point of this is to get a friendlier message for a missing value in a required field, see whether changing the table's properties is satisfactory. For example, you could set Required to No, then use Is Not Null for the field's Validation Rule which would allow you to assign your friendly text message as the field's Validation Text property.

Table design Required and Validation properties

Upvotes: 3

Simon Kingston
Simon Kingston

Reputation: 495

From http://office.microsoft.com/en-us/access-help/order-of-events-for-database-objects-HP005186761.aspx,

Similarly, when you close a form, the following sequence of events occurs:

Exit (control) → LostFocus (control) → Unload (form) → Deactivate (form) → Close (form)

If you've changed data in a control, the BeforeUpdate and AfterUpdate events for both the control and the form occur before the Exit event for the control.

What's likely causing your error is the BeforeUpdate event when the form tries to save your changes, but fails because you haven't filled in required fields. You can create some validation code and run it in the BeforeUpdate event for the form to make sure everything is OK and send the appropriate message to the user.

Upvotes: 3

Fionnuala
Fionnuala

Reputation: 91356

You might like to use:

Private Sub Form_Unload(Cancel As Integer)

End Sub

Upvotes: 2

Related Questions