Help with SQL Insert to avoid -2147217900(80040e14) runtime error 1

[tgikristi]

I have an error handler module with the code below that keeps breaking on the SQL statement in the LogError function with a runtime error of -2147217900(80040e14) Syntax Error (Missing Operator).... When I take out the ErrorString and str.Message portions of the code, I don't get the error. I got this code straight from a development book--can anyone help me understand how to get it to work with the Err.Description part?

Thanks!!!
tgikristi


Option Compare Database
Option Explicit
Const FormName2 = "basGenericErrorHandler"

'Type structure used to hold error information
Type typErrors
datDateTime As Variant
strUserName As String
lngErrorNum As Long
strMessage As String
strRoutine As String
strForm As String

End Type
------------------------------------------------
'Declaration of public type structure variable
Public gtypError As typErrors

'Constants used by global error handler
Public Const ERR_CONTINUE = 0 'Resume Next
Public Const ERR_RETRY = 1 'Resume
Public Const ERR_QUIT = 2 'End
Public Const ERR_EXIT = 3 'Exit Sub or Func
Function ErrorHandler(lngErrorNum As Long, _
strErrorDescription As String, _
strFormName As String, _
strRoutineName As String) As Integer

'Populate elements of the type structure variable
'with information about the error that occurred
gtypError.datDateTime = Now
gtypError.strUserName = CurrentUser()
gtypError.lngErrorNum = lngErrorNum
gtypError.strMessage = strErrorDescription
gtypError.strRoutine = strRoutineName
gtypError.strForm = strFormName

'Log the error
Call LogError

'Locate the error number in tblErrors to
'determine how you should respond to the error
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "Select Response from tblErrors Where ErrorNum = " & lngErrorNum, _
CurrentProject.Connection, adOpenStatic

'If the error number that occurred is not found
'in tblErrors, return ERR_QUIT to the problem routine
If rst.EOF Then
MsgBox "Error Number" & Err.Number & ": " & Err.Description
ErrorHandler = ERR_EXIT

'If the error is in tblErrors, evaluate the contents of
'the Response field. Respond appropriately, returning the appropriate value to the offending routine
Else
Select Case rst!Response
Case ERR_QUIT
MsgBox "Critical Error: Database will close." & vbCrLf & _
"Error Number" & Err.Number & ": " & Err.Description
ErrorHandler = ERR_QUIT
Case ERR_RETRY
ErrorHandler = ERR_RETRY
Case ERR_EXIT
MsgBox "Severe Error: Procedure could not complete." & vbCrLf & _
"Error Number" & Err.Number & ": " & Err.Description
ErrorHandler = ERR_EXIT
Case ERR_CONTINUE
ErrorHandler = ERR_CONTINUE
End Select
End If

End Function
--------------------------------------------------
Sub LogError()

'Declare a Connection object
Dim Cnn As ADODB.Connection
Dim strSQL As String

'Point the Connection object at the connection
'associated with the current project
Set Cnn = CurrentProject.Connection

'Build a SQL statement that inserts error information
'into the tblErrorLog table
strSQL = "INSERT INTO tblErrorLog ( ErrorDate, ErrorTime, UserName, ErrorNum, ErrorString, RoutineName, FormName) "
strSQL = strSQL & "Select #" & gtypError.datDateTime & "#, #" _
& gtypError.datDateTime & "#, '" _
& gtypError.strUserName & "', " _
& gtypError.lngErrorNum & ", '" _
& gtypError.strMessage & "', '" _
& gtypError.strRoutine & "', '" _
& gtypError.strForm & "'"

'Execute the SQL statement
Cnn.Execute strSQL, , adExecuteNoRecords

End Sub

 

[cmmrfrds]

The variable is probably NULL, put something in the variable when null.

& nz(gtypError.strMessage," &quot & "', '" _

 

[tgikristi]

I tried that and it didn't work. For example, I tried to click an 'Undo' command button and still got the #2046 error of The command or action 'Undo' isn't available now.

Could it have something to do with the single quotes around Undo? (Probably a stupid question)

When I ran some code to create a table of all the error messages, I get messsages returned with @ symbols and I don't know what that means (for example, 2046 shows up as:

"The command or action '|' isn't available now.@* You may be in a read-only database or an unconverted database from an earlier version of Microsoft Access.
* The type of object the action applies to isn't currently selected or isn't in the active view.@Use only those commands and macro actions that are currently available for this database.@1@@1&quot

but I only ever see the first part--could Err.Descriptions like these be causing this error?

 

[cmmrfrds]

It looks more like a case of having either quotes or double quotes inside the text message. This depends on what database you are using. For example, Double quotes in Access, Single quote in SQL Server.

Probably need a function that will look at the text string and replace either the quotes or double quotes - see above. Do this before trying to store the text message in a table.

For Access, check out the Replace function. For SQL Server, check out the Stuff function.

 

[tgikristi]

Thanks, I will try that. I tried to replace the error message in my table with something plain without any quotes(I thought this table was being used to derive the messages that are presented), but I didn't get my custom message back.

I found this post that sounds similar to my problem--of course, I hardly understand any of the terminology. If my problem is due to multiple errors, and if implementing the 'solution' code provided in this post would help, would you mind briefly explaining where in my error handling code I would need to place any or the whole function/how to reference it?

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20112015.html

Thanks for all your suggestions,
tgikristi

 

[cmmrfrds]

Before you build the sql statement replace the quotes inside of the message field. You can look up the Replace function, but you would pass gtypError.strMessage to the replace function and return from the function the message with the quotes cleaned up. For example, replace a quote " with 2 quotes "", which would resolve to 1 " when the message field was inserted in the database. You could create a string variable to put the cleaned up message into.

Dim mymessage as String


'Build a SQL statement that inserts error information
'into the tblErrorLog table
strSQL = "INSERT INTO tblErrorLog ( ErrorDate, ErrorTime, UserName, ErrorNum, ErrorString, RoutineName, FormName) "
strSQL = strSQL & "Select #" & gtypError.datDateTime & "#, #" _
& gtypError.datDateTime & "#, '" _
& gtypError.strUserName & "', " _
& gtypError.lngErrorNum & ", '" _
& mymessage & "', '" _
& gtypError.strRoutine & "', '" _
& gtypError.strForm & "'"

 

[tgikristi]

Thank you Thank you Thank you Thank you Thank you Thank you
Thank you Thank you Thank you Thank you Thank you Thank you
Thank you Thank you Thank you!!!!!