On Error Handler Creates Error

[PJFry]

I am developing a new database and I want to be able to capture errors in a
table so I can sort through them without having the users try and send me
screen shots and such. Below is the code that I have on my OnCurrent event.

This code did not produce an error. It does exactly what I want it to do as
long as there is a value in the combo box. If the value is missing I should
get a syntax error. What is puzzling me is that when there is *no* error,
the error handler passes an error with a null description to the Error Log.
When there is an actual error, the sub shows me the error and stops without
recording that error.

I am somewhat new to error handling. Thoughts?

Private Sub Form_Current()
On Error GoTo Err_Handler

Set rs = New ADODB.Recordset
Set cn = CurrentProject.Connection

strSQL = "SELECT * FROM tParish WHERE ID = " & Me.cboParish.Column(0)

rs.Open strSQL, cn

txtParishPhone = rs!txtPhone

rs.Close

Set rs = Nothing

Err_Handler:
'MsgBox Err.Description

strErr = "INSERT INTO tErrorLog(txtErrDesc,txtUserID,dtmErr,txtForm) " & _
"VALUES ('" & Err.Description & "','" & fOSUserName() & "','" & Now() &
"','" & Me.Name & "')"

DoCmd.RunSQL strErr

Exit Sub

End Sub

Or can someone suggest a simplier way to do this?

Thanks!
PJ

 

[Jeanette Cunningham]

It needs a little re-arrangement of the lines like this:
------------------------------------------------------------------
SubExit:
Exit Sub

Err_Handler:
'MsgBox Err.Description

strErr = "INSERT INTO tErrorLog(txtErrDesc,txtUserID,dtmErr,txtForm) " & _
"VALUES ('" & Err.Description & "','" & fOSUserName() & "','" & Now() &
"','" & Me.Name & "')"

DoCmd.RunSQL strErr
Resume SubExit

End Sub
--------------------------------------------------------------------
Note that developers usually insert entries into an error log table using a
public function.
I have an error table called ztblErrorLog
I use this function:

Public Sub ErrorLog(pstrProc As String, _
pstrMdl As String, _
varErr As Variant, _
strError As String)
'-----------------------------------------------------------
' Inputs: Name of the procedure and subproc trapping the error
' Err value
' Error string
' Output: Writes an entry to ErrorLog table
' Created By: JC 11/10/06
' Last Revised:
'-----------------------------------------------------------
On Error GoTo SubErr

Dim db As Database
Dim rstE As Recordset
Set db = CurrentDb()
Set rstE = db.OpenRecordset("ztblErrorLog")

Select Case varErr
'ignore the following errors
Case 0: ' no error
Case 2001: ' previous action cancelled
Case 2501: ' action cancelled
Case 2169: ' action cancelled
Case 2474: 'requires control to be in active window
Case 2475: 'requires control to be in active window
Case 2452: 'requires control to be in active window to dropdown
Case 2455: 'invalid reference to property (dropdown, or other)
Case 3021: 'no current record
Case 2501: 'open form action cancelled

Case Else:

rstE.AddNew
rstE!Module = pstrMdl
rstE!ActiveForms = Forms.Count
rstE!ErrorDate = now
rstE!CallingProc = pstrProc
rstE!ErrorCode = varErr
rstE!ErrorText = strError
rstE.Update
rstE.Close
End Select

SubExit:
If Not db Is Nothing Then
Set db = Nothing
End If
If Not rstE Is Nothing Then
Set rstE = Nothing
End If
Exit Sub
SubErr:
'nothing useful can be done now
Resume SubExit

End Sub


Jeanette Cunningham

 

[PJFry]

Thank you, wonderful answer. I figured that there was a better way to do
this.