Trapping error messages in a form

[Amit]

MS Access 2K, Windows XP
=====================
I'm trying to trap the error messages in a form, and replace them with
custom-messages which are less technical. I'm having a problem trying to
figure out how best to do this.

The form is based on a table with three fields, all of which are required,
and one of them is AutoNumber. I have the following code in the "On Error"
event of the form
----------------------------------------------------------------------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)
' MsgBox "Error#: " & DataErr ' Display the error number
' Response = acDataErrDisplay ' Display Default message
If DataErr = 3314 Then
Response = acDataErrContinue ' Don't display the default message
MsgBox "Enter data in " & Me.ActiveControl.Name
ElseIf DataErr = 2169 Then
Response = acDataErrContinue ' Don't display the default message
MsgBox "Enter data in " & Me.ActiveControl.Name
Else
MsgBox "Error#: " & DataErr
Response = acDataErrDisplay ' Display Default message
End If
End Su
--------------------------------------------------------------------------------
The problem I'm having is in the special case when I delete the data from
the required field and then try to close the form using the "x" in the
right-hand top corner. In that case, I get the error message twice, and then
the form closes. Obviously, I'm not handling the DataErr = 2169 properly.

What I'd like to happen is if someone tries to close the form, s/he gets the
same message and action as for DataErr = 3314.

Would someone have an example of how to handle this error, or maybe point me
in the right direction? It'll be helpful to get an explanation of the
correct logic to be used in this example.

Thanks!

-Amit

PS. I also have code in the Before_Update event of the form to check for the
value, and give an error message if a field is not filled in.

 

[Jeff]

Amit,

I also was having a similar problem. What I believe is happening is when
you close the first Err displays the Msgbox & then checks again as the form
is closing finds the Err 2169 & hence repeats the display of the msgbox.

I have tried there is no way around using this method, the Msgbox will
display at least once if there is an err in the form when you close. With
some more logic you can get it down to one rather than 2. But rather than go
into that a way I found around this so you will get NO msgboxes on the close
of a form if there is an err is to put the logic on the GotFocus of every
control on the form. Basically check the validation u want on the Got Focus
of every control. So when user clicks on X to close the form no validation
will be done & hence the form closes with no Msgbox. It works for me but it
is cumbersome as u need to put the same logic in every got focus of every
control. To help u can just put a procedure for logic of Validation & then
just call that procedure of every Got Focus of every control.

I hope this helps.
Jeff

 

[Amit]

Jeff,

Thanks for your response, but I'm not sure I want to use this solution
unless someone comes up with a better one. There has to be a way to cancel
the "close form" event in this special case, so that the form is not closed,
and the user is forced to type in data. I'm just not able to figure out how
and where to put this....

-Amit

 

[Jeff]

Amit,

To cancel the Form Close Event is easy, just use the

Private Sub Form_Unload(Cancel As Integer)
'Set cancel=True & form will not close
'...put logic & then
Cencel=True
End Sub

For a control if you dont want to exit then can use
BeforeUpdate Event & Set Cancel = True if you dont want to Exit the control
until data is good.

Although with this again if user clicks X & data is no good msgbox will come
up.

Jeff



To