Creating an Error Log In Access

[jcapri22]

Well here's what I am trying to accomplish. I want to add Error handling code to an my Access database that creates a new record in the 'Error' table, each time there is an error, and stores information about that type of error that occured and when it occurred.

When I want to view the error logs, I want to display all of the information on a form. Then, when I press a command button on that form, the OnClick event issues the Msgbox command with the info from the record. Consequently, it is displayed to me just like the user saw it.

I read the thread posted by FancyPrairie but because I am a beginner I need more elaboration. Can FancyPrairie or someone step me through the process? Please..

 

[Norris68]

Create a public sub routine in a code module. Define one of the parameters as an ErrObject and you will be able to pass the Err object straight in. The following code assumes you have a table set up with the required fields - some of it is a bit messy because we need to put single quotes around any text fields. I would advise recording the name of the function, and possibly what your code was trying to do at the time to help with debugging - create your log table & parameter list accordingly.

Public Sub LogError(FunctionName As String, ErrOb As ErrObject)
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblErrorLog (FuncName,ErrCode,ErrDesc) VALUES ('" & FunctionName & "'," & ErrOb.Number & ",'" & ErrOb.Description & "')"
DoCmd.SetWarnings True
End Sub

Call as e.g.:
Private Sub DoSomething()
On Error Goto MyErrHandler
...
MyErrHandler:
LogError "DoSomething", Err 'Pass in the Err object
Resume
...

 

[hwkranger]

I use the following for in a table, it get's the active FORM name, and if you pass the function name that calls it it'll give you that also.

this is used in the following manner

eg.
'demo of code that calls myerrorhandler
sub mybutton_onclick()
on error goto mybutton_err

'some code

exit sub
mybutton_err:
myerrorhandler("mybutton_onclick&quot
exit sub
end sub

the following is the error handling code

Public Function MyErrorHandler(Optional location As String)
'On Error GoTo MyErrorHandler_Err

Exit Function 'breaks function so that Sights don't log anymore.

Dim db As Dao.Database
Dim myrecord As Dao.Recordset

Set db = CurrentDb()
Set myrecord = db.OpenRecordset("ErrorsInSystem&quot

'From the form object, you can get the name from the .Name property:
Dim Frm As Form
Dim strFormName As String

Set Frm = Screen.ActiveForm 'error may occur here 2475 if no active window
strFormName = Frm.Name 'form name

'makes sure location is set.
If Len(location) < 3 Then
location = &quot;Unknown&quot;
End If

'updates ErrorInSystem table
With myrecord
.AddNew
![When] = Now()
'I HAVE A HIDDEN FORM THAT shows WHO THE ERROR OCCURED TO - edit for yourself
'![Who] = [Forms]![Operator]![OperatorID]
If Err.Number > 0 Then
![ErrorNumber] = Err.Number
End If

If Len(Err.DESCRIPTION) > 0 Then
![ErrorMessage] = Err.DESCRIPTION
End If

If Len(strFormName) > 0 Then
![Form] = strFormName
End If
![ErrorLocation] = location


.Update
End With

set db = nothing
set rst = nothing
'MsgBox (&quot;There was an Error. Error has been logged, Error Number: &quot; & Err.Number & &quot;: &quot; & Err.DESCRIPTION)

Exit Function
MyErrorHandler_Err:
If Error.Number = 2475 Then
MsgBox (&quot;Unhandled Error In Error Handler.&quot
End If
Resume Next
Exit Function

End Function

This was made for a table named &quot;ErrorsInSystem&quot;, with fields, &quot;Who&quot;, &quot;when&quot;, &quot;errorlocation&quot;, &quot;errormessage&quot;, &quot;erornumber&quot;, &quot;ErrorId&quot; (autonumber)

The sql way is shorter, you can add the form information to that if you like.... i do it this way.

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[FancyPrairie]

Check out thread705-626368

 

[jcapri22]

Would I need to add this code to every form where I am calling the error handler? I am just a beginner and would like to know exactly where I need to place this code.

I really appreciate your help

 

[FancyPrairie]

If you look at the thread Thread705-626368 you will see that what you do is place the code that handles logging the error messages, etc. in a library database. Then, in your databases, you reference your library database. To report an error, just call the function that reports the error.

 

[jcapri22]

Thanks for your help FancyP.