Runtime error 3075 - Syntax error in query expression 3

[micang]

Access 2003

Hi All,

I am running the following code - it inserts data from a combobox and a textbox into a table.

Dim pid as Integer
Dim infotodo as String

pid = combopid.Value

infotodo = txtboxtodo.Value

DoCmd.RunSQL "insert into info_todo(Priority,info)values ('" & pid & "','" & infotodo & "')"

I get a "Runtime error 3075 - Syntax error in query expression" error if the txtbox(infotodo) has an apostrophe(') in the txt. I works fine if there is no apostrophe, but as soon as there is one, this error occurs.

eg: "Hello, you are great!" - works fine, but
"Hello, you're great!" - does not work.

I am really stumped and would appreciate any info in the right direction.

Thank you.

Michael

 

[MADDPM]

I had the same problem with a look-up combo box with last names; e.g., D'Lorenzo.

Solution was to create a module with the following code:
Public Function fHandleApostrophe(strPass As String) As String
Dim strRet As String

strRet = strPass
If InStr(1, strRet, "'", vbTextCompare) > 0 Then
strRet = "'" & Replace(strRet, "'", "''", , , vbTextCompare) & "'"
Else
strRet = "'" & strRet & "'"
End If
fHandleApostrophe = strRet

End Function


Hope this helps.

 

[micang]

MADDPM, thank you very much, work's 100%, appreciate your prompt assistance.

Michael

 

[PHV]

why not simply this ?

DoCmd.RunSQL "insert into info_todo(Priority,info)values ('" & Me!combopid & "','" & Replace(Me!txtboxtodo, "'", "''") & "')"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[micang]

Thanks PHV,a great solution.

Michael

 

[TheAceMan1]

How are ya micanguk . . .

If you want to keep apostrophe's in your text fields, construct [blue]substrings[/blue] instead:

[blue]   Dim DQ as String

   DQ = """"

   DoCmd.RunSQL "INSERT INTO info_todo (Priority, info) " & _
	            "VALUES (" & [purple][b]DQ[/b][/purple] & Me!combopid & [purple][b]DQ[/b][/purple] & "," & [purple][b]DQ[/b][/purple] & Me!txtboxtodo & [purple][b]DQ[/b][/purple] & ")"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[micang]

Hi TheAceMan1,

Awesome stuff, thanks to you and all for your solutions, appreciate it.

Michael