insert into statement giving 3075 error 1

[fizzlesquirt]

I am trying to use a SQL insert statement to automatically add a record to a table to capture errors.

The table setup:
errRecordID - primary key, no duplicates, autonumber
Date - Date/Time
FormName - text
errorNumber - text
errorDescription - memo
Notes - memo

The code in VBA is as follows:
-----

Public Function errorCapture(frmName As String, errNum As String, errDesc As String)

MsgBox "An error has occured" & vbNewLine _
        & "Form: " & frmName & vbNewLine _
        & "Error: " & errNum & vbNewLine _
        & errDesc & vbNewLine & vbNewLine _
        & "This info has been added to the errors table", vbCritical _
        , "Error"

Dim mySQL As String

mySQL = "INSERT INTO tblErrors (FormName, ErrorNumber, ErrorDescription) VALUES (" & "'" & frmName & "', " & "'" & errNum & "', " & "'" & errDesc & "' )"
Debug.Print mySQL
DoCmd.RunSQL mySQL


End Function

---

The error I receive is:
Run-time error 3075
Syntax error (missing operator) in query expression "You can't go to the specified record')'.

I've been working on this for the last 3 hours without any success. All help is appreciated!

 

[PHV]

mySQL = "INSERT INTO tblErrors (FormName,ErrorNumber,ErrorDescription) VALUES (" _
 & "'" & frmName & "','" & errNum & "','" & Replace(errDesc, "'", "''") & "')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fizzlesquirt]

Thanks PHV! The addition of the replace(errDesc... made all the difference.

The only other code I had to add was to turn off warning messages at the beginning and then turn back on at the end.

Public Function errorCapture(frmName As String, errNum As String, errDesc As String)
DoCmd.SetWarnings False

Dim mySQL As String

mySQL = "INSERT INTO tblErrors (FormName,ErrorNumber,ErrorDescription) VALUES (" _
 & "'" & frmName & "','" & errNum & "','" & Replace(errDesc, "'", "''") & "')"

DoCmd.RunSQL mySQL

DoCmd.SetWarnings True

End Function

In order to better understand the change, could you explain why the replace code needed to be there in order to work? All the documentation found online and in Access help just showed the code as in my first post.

 

[PHV]

Before: 'You can't go to the specified record'
After: 'You can''t go to the specified record'

you have to double the embedded single quotes to keep a valid SQL literal constant.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886