Error trapping

[isxaa1]

I have a form with four input boxes, all of which are required and two
buttons, "OK" and "Cancel", for storing information in a table and closing
the form respectively. I have managed to get my error trapping code to work
for the above 2 buttons, but when I try to close the form from the "X"
button at the right top hand corner of the form, it gives me some errors
which I would like to have control by doing some error trapping. Can anyone
help me with this?

P.S: I tried the 'On Unload' and 'On Close' events but it doesn't work. The
weird thing is that i put a code to get the error number (Err.Number) and
although when i close the form from the 'X' it gives me an error, my extra
code shows me that the error number is 0. I am using Access 2000. Could
anyone help me as it is the fourth day and I am stuck.

 

[Pat Hartman]

Move your error trapping code to the BeforeUpdate event of the FORM. Be sure to include
Cancel = True
In situations where you do NOT want the update to proceed.

 

[isxaa1]

The first error disappears but the second error still comes up. Here is my code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ERRCONTROL

Cancel = True

Exit_This_Sub:
Exit Sub

ERRCONTROL:

MsgBox "Error: " & Err.Number
Resume Exit_This_Sub

End Sub

Can you please tell me what is going wrong?
Thank you for your previous reply

 

[Pat Hartman]

You can't just put - Cancel = True - in the BeforeUpdate event of the FORM. NOTHING will ever be saved. You need to use the cancel in conjunction with your validation code.

To avoid the "x" problem gets a little complicated, I use a global variable to warn me if there is a pending error. To use this technique:
1. Dim the public variable in the form's class module right after the Option Explicit.
2. In the form's Current event set the value to false -
gPendingError = False
3. In the form's BeforeUpdate event, in addition to the Cancel = True statement when you find a validation error, set the pending error to true -
gPendingError = True
If there are no errors, set the pending error variable to False -
gPendingError = False
4. In the Unload event of the Form, check the pending error variable. If it is true, cancel the closing of the form -
If gPendingError = True Then
msgbox "You must fix the error before closing the form",vbOKOnly
Cancel = True
End If

This method works because if you press the "x" when the form is dirty (pending updates), the order of event exection is:
BeforeUpdate, AfterUpdate, UnLoad, Close. The BeforeUpdate event has an argument (Cancel) that can be used to cancel the pending update and that also cancels the AfterUpdate event. Also, the Unload event has an argument (Cancel) that can be used to cancel the Unload event and that also cancels the Close event.

When you write VBA (especially error handling), it is important to understand the event model so that you know which events to use for what purpose. If you have questions as to event order, just put msgbox's in various events to see the order in which they fire.

 

[isxaa1]

I have tried it but again it doen't work. I will give you all the details below:

'In the class module
Option Compare Database
Option Explicit
Dim gPendingError

'On Current
Private Sub Form_Current()
gPendingError = False
End Sub

'BeforeUpdate
Private Sub Form_BeforeUpdate(Cancel As Integer)
gPendingError = False

On Error GoTo ERRCONTROL

Cancel = True
gPendingError = True

Exit_This_Sub:

Exit Sub

ERRCONTROL:

MsgBox "Error: " & Err.Number

Resume Exit_This_Sub

End Sub

'On Unload
Private Sub Form_Unload(Cancel As Integer)
If gPendingError = True Then
MsgBox "You must fix the error before closing the form", vbOKOnly
Cancel = True
End If

End Sub


Please tell me what is the problem because it is driving me crazy!

 

[isxaa1]

Just in case that I didn't clarify my intentions. What I would like is that instead of having the standard Access errors being displayed, to have the errors but with my words that the end user will understand, as my system will be deployed in a company.

 

[Pat Hartman]

Did you read the first sentence of my previous post? You need to put your error checking along with the messages you want to display in the form's BeforeUpdate event.

 

[isxaa1]

Could you please tell me explicitly what do you mean, as I am a novice in Access. Can you please make the corrections on the code that I have supplied. I would be very greatfull as I really truggle with this one. Thanks

 

[Pat Hartman]

'BeforeUpdate
Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo ERRCONTROL
gPendingError = False

If IsNull(Me.SomeField) Then
msgbox "This field may not be empty.", vbOKOnly
Cancel = True
gPendingError = True
End If

If IsNull(Me.SomeOtherField) Then
msgbox "This field may not be empty.", vbOKOnly
Cancel = True
gPendingError = True
End If


---- any other error checking you want to do

Exit_This_Sub:

Exit Sub

ERRCONTROL:

MsgBox "Error: " & Err.Number

Resume Exit_This_Sub

End Sub