Error handling does not provide enough clues

[Storyteller]

Hello All,

I have incorporated the following error handling from the Help files into a form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim Msg
' If an error occurs, construct an error message
On Error Resume Next ' Defer error handling.

' Check for error, then show message.
If Err.Number >= 0 Then
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Chr(10) & Err.Description
MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
End If

End Sub

The reason is that I have been getting a persistant error message: "The Value you entered isn't valid for this field." The weird thing is that this error message only shows up some of the time. Mostly when scrolling through the records and not after entering data into fields.

After adding the error handling I get the following message box: "Error # 0 was generated by" now the part that is missing is what field is actually causing the error. Ideally, what I would like to be able to do is to SetFocus on the field causing the error.

Any suggestions on how I could tweak the code to show which field(s) is causing the problem.

Thanks for your advice.

Regards,
Michael

 

[SkipVought]

Michael,

Please post the code where the error is occurring, including how you call this routine.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Storyteller]

Hello Skip,

The routine is on the "On Error" event procedure for frmPurchaseOrder. It is not attached to a specific field or control.

The error will occur when using the mousewheel to scroll through the records.

Further investigation has the "The Value you entered isn't valid for this field." error appearing when I open tblPurchaseOrders even stranger the error message appears when the table is empty. Go figure.

I think I will go through every lookup value for each field in the table to see if that might be the problem.

Very strange indeed.

Regards,
Michael

 

[willir]

Michael

"Error # 0

...suggests that the error has been reset. This is why your error source and description has been lost.

As I understand it, any successful code run after the error will reset the error stack.

An approach would be as follows...
- assign the Err.Number to a numeric value, lngError = Err.Number
- soft code the calling module, strSource = Me.Name
- You can grab the text error message by using the error function, strError = Err(lngError)

...Lastly, your error check should not include 0, ie, use <>

Sample code for form

Dim lngError as Long, strSource as String
On Error Goto Error_Routine
'...Code for form


:Error_Routine
'Error routine[COLOR=blue][b]
lngError = Err.Number[/b][/color] [COLOR=red][b]
strSource = Me.Name[/b][/color]
[COLOR=blue]
If [b]Form_error (lngError, strSource)[/b] = vbOk Then[/color]
'....
Else
'....
End If

End Sub

Function Form_Error(lngError As Integer, strSource As String) as Integer

Dim strMsg
' If an error occurs, construct an error message
On Error Resume Next    ' Defer error handling.

' Check for error, then show message.[COLOR=blue]
If [b]lngError <>[/b] 0 Then[/color]
    strMsg = "Error # " & lngError & " was generated by " _
            & strSource & vbCRLF & [COLOR=blue][b]Error(lngError)[/b][/color] _
            & vbCRLF & "Continue ?"
    Form_Error = MsgBox (strMsg, vbOkCancel, "Error", Err.HelpFile, Err.HelpContext)
Else
    'Doubtful this code would ever run
    Form_Error = 0
End If

End Sub

You will have to tweak this code per your needs, but hopefully, you see how this works.

A function call is usually a better solution because you can call it from anywhere. Create it in the module section. (Why type in the same error routine for all your forms??)

The one thing about a function is that it has to return a value which is why I have...[tt]
Form_Error = MsgBox (strMsg, vbOkCancel, "Error", Err.HelpFile, Err.HelpContext)

and...
Form_Error = 0[/tt]


Note that I have replaced...[tt]
Chr(13) & Chr(10)
with
vbCRLF[/tt]


Richard

 

[Storyteller]

Hello All,
Sorry for the delay in responding to the wonderful suggestions everyone made.

Instead of working with code, I was able to find that yes indeed it was a lookup table datatype that was causing the problem.

I've fixed all the lookup tables and I no longer get the error.

Richard, I am going to spend some time in the future experimenting with the code you posted. - Thanks.

Regards,
Michael