Error Handling

[David]

Hello
I am trying to collect errors and record them in a table instead of a
popup message stopping my code. It seems to work ok, but when I try to
add ERR.Description to my code it fails on Syntax Error (missing
operator).
Any help would be very much appreciated
Thanks Dave

Dim strSql As String
strSql = "INSERT INTO tbllogoftimedev ents(Log)VALUES ('" &
("Routine Failed " & "Error No " & Str(Err.Number) & Err.Description &
"');")
DoCmd.RunSQL strSql

Resume Exit_RMesc

[Salad]

Re: Error Handling

David wrote:[color=blue]
> Hello
> I am trying to collect errors and record them in a table instead of a
> popup message stopping my code. It seems to work ok, but when I try to
> add ERR.Description to my code it fails on Syntax Error (missing
> operator).
> Any help would be very much appreciated
> Thanks Dave
>
> Dim strSql As String
> strSql = "INSERT INTO tbllogoftimedev ents(Log)VALUES ('" &
> ("Routine Failed " & "Error No " & Str(Err.Number) & Err.Description &
> "');")
> DoCmd.RunSQL strSql
>
> Resume Exit_RMesc
>[/color]
What happens if you put a space between
tbllogoftimedev ents(Log)
and a space between
(Log)VALUES(
so it looks like
strSql = "INSERT INTO tbllogoftimedev ents (Log) VALUES ('" &

[Salad]

Re: Error Handling

David wrote:[color=blue]
> Hello
> I am trying to collect errors and record them in a table instead of a
> popup message stopping my code. It seems to work ok, but when I try to
> add ERR.Description to my code it fails on Syntax Error (missing
> operator).
> Any help would be very much appreciated
> Thanks Dave
>
> Dim strSql As String
> strSql = "INSERT INTO tbllogoftimedev ents(Log)VALUES ('" &
> ("Routine Failed " & "Error No " & Str(Err.Number) & Err.Description &
> "');")
> DoCmd.RunSQL strSql
>
> Resume Exit_RMesc
>[/color]
What happens if you put a space between
tbllogoftimedev ents(Log)
and a space between
(Log)VALUES(
so it looks like
strSql = "INSERT INTO tbllogoftimedev ents (Log) VALUES ('" &

[David]

Re: Error Handling

Salad
It does exactly the same

[David]

Re: Error Handling

Salad
It does exactly the same

[Allen Browne]

Re: Error Handling

David, take a look at:
Error Handling in VBA
at:

http://allenbrowne.com/ser-23a.html

The article explains how you can pass the error number, description, name of
the calling routine, optional information, and whether to show the error
message to the user or not.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <david.roebuck@ btinternet.com> wrote in message
news:1123142772 .231032.77930@g 43g2000cwa.goog legroups.com...[color=blue]
>
> I am trying to collect errors and record them in a table instead of a
> popup message stopping my code. It seems to work ok, but when I try to
> add ERR.Description to my code it fails on Syntax Error (missing
> operator).
> Any help would be very much appreciated
> Thanks Dave
>
> Dim strSql As String
> strSql = "INSERT INTO tbllogoftimedev ents(Log)VALUES ('" &
> ("Routine Failed " & "Error No " & Str(Err.Number) & Err.Description &
> "');")
> DoCmd.RunSQL strSql
>
> Resume Exit_RMesc[/color]

[Allen Browne]

Re: Error Handling

David, take a look at:
Error Handling in VBA
at:

http://allenbrowne.com/ser-23a.html

The article explains how you can pass the error number, description, name of
the calling routine, optional information, and whether to show the error
message to the user or not.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <david.roebuck@ btinternet.com> wrote in message
news:1123142772 .231032.77930@g 43g2000cwa.goog legroups.com...[color=blue]
>
> I am trying to collect errors and record them in a table instead of a
> popup message stopping my code. It seems to work ok, but when I try to
> add ERR.Description to my code it fails on Syntax Error (missing
> operator).
> Any help would be very much appreciated
> Thanks Dave
>
> Dim strSql As String
> strSql = "INSERT INTO tbllogoftimedev ents(Log)VALUES ('" &
> ("Routine Failed " & "Error No " & Str(Err.Number) & Err.Description &
> "');")
> DoCmd.RunSQL strSql
>
> Resume Exit_RMesc[/color]

[Justin Hoffman]

Re: Error Handling

"David" <david.roebuck@ btinternet.com> wrote in message
news:1123142772 .231032.77930@g 43g2000cwa.goog legroups.com...[color=blue]
> Hello
> I am trying to collect errors and record them in a table instead of a
> popup message stopping my code. It seems to work ok, but when I try to
> add ERR.Description to my code it fails on Syntax Error (missing
> operator).
> Any help would be very much appreciated
> Thanks Dave
>
> Dim strSql As String
> strSql = "INSERT INTO tbllogoftimedev ents(Log)VALUES ('" &
> ("Routine Failed " & "Error No " & Str(Err.Number) & Err.Description &
> "');")
> DoCmd.RunSQL strSql
>
> Resume Exit_RMesc[/color]


Hi David
Have you fixed this already? If not, my advice would be to build up the SQL
string more slowly to make your code more readable. Before you run the SQL
why not do a Debug.Print strSQL or MsgBos strSQL to see what you've actually
got. This should show you syntax problems with brackets, semi-colons,
quotes, etc.
You need to watch out when using text values in SQL as they could contain
quotes which will mess up the interpretation of the string. You could also
structure your code, to allow for the fact that even the error logging
procedure might fail for some reason.
Here is an example, which you could paste into a new module and allows the
error description to contain quotes. Rather than using DoCmd.RunSQL, I have
used the .Execute method which allows me to see whether I successfully added
a record or not.
A couple of extra things to consider:
Allen Browne's method uses an append-only recordset which gets around the
problem of quotes altogether, and although executing an SQL string is
generally faster than opening a recordset, in the case of a single record it
makes no difference.
You may need to consider if you really want to hide all error messages from
the user. Sure you can log them to a table, but often the user needs to
know something went wrong.
If you are writing a global error handler, then you may need to consider the
difference between vba runtime errors and dao data errors. Error handling
can be more complex than Err.Number - you may need to look at the
DbEngine.Errors collection which can contain multiple errors. This sort of
thing occurs when, for example, you try to execute an update query on a
linked odbc database.


Option Compare Database
Option Explicit

Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
End Enum

Public Function LogError(strErr or As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO tblError (ErrDescription ) VALUES ("

strSQL = strSQL & AddQuotes(strEr ror, DoubleQuote)

strSQL = strSQL & ")"

Set dbs = CurrentDb

dbs.Execute strSQL, dbFailOnError

If dbs.RecordsAffe cted = 1 Then
LogError = True
End If

Exit_Handler:

On Error Resume Next

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
' No error message - just return false
Resume Exit_Handler

End Function


Public Function AddQuotes(strVa lue As String, _
Q As QuoteType) As String

Dim strReturn As String

Select Case Q

Case QuoteType.Singl eQuote
strReturn = Replace(strValu e, "'", "''")
strReturn = "'" & strReturn & "'"

Case QuoteType.Doubl eQuote
strReturn = Replace(strValu e, """", """""")
strReturn = """" & strReturn & """"

Case Else
strReturn = strValue

End Select

AddQuotes = strReturn

End Function

[Justin Hoffman]

Re: Error Handling

"David" <david.roebuck@ btinternet.com> wrote in message
news:1123142772 .231032.77930@g 43g2000cwa.goog legroups.com...[color=blue]
> Hello
> I am trying to collect errors and record them in a table instead of a
> popup message stopping my code. It seems to work ok, but when I try to
> add ERR.Description to my code it fails on Syntax Error (missing
> operator).
> Any help would be very much appreciated
> Thanks Dave
>
> Dim strSql As String
> strSql = "INSERT INTO tbllogoftimedev ents(Log)VALUES ('" &
> ("Routine Failed " & "Error No " & Str(Err.Number) & Err.Description &
> "');")
> DoCmd.RunSQL strSql
>
> Resume Exit_RMesc[/color]


Hi David
Have you fixed this already? If not, my advice would be to build up the SQL
string more slowly to make your code more readable. Before you run the SQL
why not do a Debug.Print strSQL or MsgBos strSQL to see what you've actually
got. This should show you syntax problems with brackets, semi-colons,
quotes, etc.
You need to watch out when using text values in SQL as they could contain
quotes which will mess up the interpretation of the string. You could also
structure your code, to allow for the fact that even the error logging
procedure might fail for some reason.
Here is an example, which you could paste into a new module and allows the
error description to contain quotes. Rather than using DoCmd.RunSQL, I have
used the .Execute method which allows me to see whether I successfully added
a record or not.
A couple of extra things to consider:
Allen Browne's method uses an append-only recordset which gets around the
problem of quotes altogether, and although executing an SQL string is
generally faster than opening a recordset, in the case of a single record it
makes no difference.
You may need to consider if you really want to hide all error messages from
the user. Sure you can log them to a table, but often the user needs to
know something went wrong.
If you are writing a global error handler, then you may need to consider the
difference between vba runtime errors and dao data errors. Error handling
can be more complex than Err.Number - you may need to look at the
DbEngine.Errors collection which can contain multiple errors. This sort of
thing occurs when, for example, you try to execute an update query on a
linked odbc database.


Option Compare Database
Option Explicit

Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
End Enum

Public Function LogError(strErr or As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO tblError (ErrDescription ) VALUES ("

strSQL = strSQL & AddQuotes(strEr ror, DoubleQuote)

strSQL = strSQL & ")"

Set dbs = CurrentDb

dbs.Execute strSQL, dbFailOnError

If dbs.RecordsAffe cted = 1 Then
LogError = True
End If

Exit_Handler:

On Error Resume Next

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
' No error message - just return false
Resume Exit_Handler

End Function


Public Function AddQuotes(strVa lue As String, _
Q As QuoteType) As String

Dim strReturn As String

Select Case Q

Case QuoteType.Singl eQuote
strReturn = Replace(strValu e, "'", "''")
strReturn = "'" & strReturn & "'"

Case QuoteType.Doubl eQuote
strReturn = Replace(strValu e, """", """""")
strReturn = """" & strReturn & """"

Case Else
strReturn = strValue

End Select

AddQuotes = strReturn

End Function

[Darryl Kerkeslager]

Re: Error Handling

This does the job for me. I wrap all strings for SQL that may contain
apostrophes.

Public Function S2SQL(ByVal inString As String) As String
Dim s As String
Dim i As Long
Do
i = InStr(inString, "'")
If i = 0 Then Exit Do
s = s & Left$(inString, i - 1) & "''"
inString = Mid$(inString, i + 1)
Loop
S2SQL = "'" & s & inString & "'"
End Function

So ..

..Execute "INSERT INTO db_error_log " & _
"(err_numbe r, err_location, err_desc, err_user, err_time) " & _
"VALUES (" & errNo & ", '" & errLoc & "', " & _
S2SQL(Left(errD esc, 255)) & ", " & GetUserLogin() & ", #" & Now() & "#)"


--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927


"David" <david.roebuck@ btinternet.com> wrote in message
news:1123147129 .047871.27500@g 49g2000cwa.goog legroups.com...[color=blue]
> Salad
> It does exactly the same
>[/color]

[Darryl Kerkeslager]

Re: Error Handling

This does the job for me. I wrap all strings for SQL that may contain
apostrophes.

Public Function S2SQL(ByVal inString As String) As String
Dim s As String
Dim i As Long
Do
i = InStr(inString, "'")
If i = 0 Then Exit Do
s = s & Left$(inString, i - 1) & "''"
inString = Mid$(inString, i + 1)
Loop
S2SQL = "'" & s & inString & "'"
End Function

So ..

..Execute "INSERT INTO db_error_log " & _
"(err_numbe r, err_location, err_desc, err_user, err_time) " & _
"VALUES (" & errNo & ", '" & errLoc & "', " & _
S2SQL(Left(errD esc, 255)) & ", " & GetUserLogin() & ", #" & Now() & "#)"


--
Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927


"David" <david.roebuck@ btinternet.com> wrote in message
news:1123147129 .047871.27500@g 49g2000cwa.goog legroups.com...[color=blue]
> Salad
> It does exactly the same
>[/color]