Error Handler

[Guest]

Hi

I am using the code below as a simple error handler which should append
details of errors to tblerrorlog.

It works fine until the error description contains the name of an object and
then access encloses the object in '' thus causing some kind of problem with
my code. Can anyone help fix this?

Public Sub ErrorHandler()
Dim strerror As String
Dim strErrorNo As String
Dim strErrorDescription As String
Dim strSQL As String
Dim strCurrentObjectName As String
Dim strCurrentUser As String
Dim strDate As String
Dim strTime As String

strErrorNo = Err.Number
strErrorDescription = Err.Description
strCurrentUser = Application.CurrentUser
strCurrentObjectName = Application.CurrentObjectName
strDate = Date
strTime = Time

MsgBox ("This has caused an error." & Chr(13) & Chr(13) & _
"Error Number: " & Err.Number & Chr(13) & _
"Error Description: " & Err.Description)

strSQL = "INSERT INTO [tblErrorLog] ([ErrNo], [ErrDescription],
[CurrentUser], " & _
"[Date],[Time], [ObjectName]) " & _
"VALUES ('" & strErrorNo & "'" & ",'" & Err.Description & "' "
& ",'" & _
strCurrentUser & "'" & ",'" & strDate & "'" & ",'" & strTime &
"'" & ",'" & _
strCurrentObjectName & "');"
Debug.Print strSQL

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub

Also is it possible to add a line of code that will record the Sub which the
error occured in?

Thanks

 

[Douglas J. Steele]

Instead of

.... & ",'" & _
strCurrentObjectName & "')"

try

.... & "," & Chr$(34) & _
strCurrentObjectName & Chr$(34) & ")"

Alternatively, assuming you're using Access 2000 or newer, try:

strCurrentObjectName = Replace(Application.CurrentObjectName, "'", "''")

Exagerated for clarity, that's

strCurrentObjectName = Replace(Application.CurrentObjectName, " ' ", " '
' ")