Special characters INSERT INTO command

[Guest]

To insert Form data into the database I use the following command on the
button:

CurrentDb.Execute ("INSERT INTO TableName ([FieldName]) VALUES ('" & cboName
& "') "), dbFailOnError

When I put a single quote in the ComboBox, it returns a RunTime error 3075
(Syntax Error). Does anybody know how to correct this?

Thanks in advance!
Jochem

 

[Allen Browne]

Use double-quote characters around the value in the string.
And double-them up to VBA understands it is not the end of the string.

strSql = "INSERT INTO TableName ([FieldName]) VALUES (""" & cboName & """);"

 

[Stefan Hoffmann]

hi Jochem,

To insert Form data into the database I use the following command on the
button:

CurrentDb.Execute ("INSERT INTO TableName ([FieldName]) VALUES ('" & cboName
& "') "), dbFailOnError

When I put a single quote in the ComboBox, it returns a RunTime error 3075
(Syntax Error). Does anybody know how to correct this?

You are trying to Excute this
"INSERT INTO TableName ([FieldName]) VALUES (''')"

use Replace to escape the single quote:

Dim SQL As String

SQL = Replace(cboName.Value, "'", "''")
SQL = "INSERT INTO TableName ([FieldName]) " & _
"VALUES ('" & SQL & "') ")

CurrentDb.Execute SQL, dbFailOnError


mfG
--> stefan <--