Not in list error

[John F]

I am using:

Private Sub itmDescription_NotInList(NewData As String, Response As Integer)

Dim str As String
Dim cmd As New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" & NewData & "');"
cmd.CommandText = str

If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
Response = acDataErrAdded
cmd.Execute
Else
Response = acDataErrContinue
Me.itmDescription.Undo
End If

Set cmd = Nothing

End Sub

It works fine as long as I do not use special characters in the field such
as ' .If I do then I get an Syntax error (missing operator) in query
expression "Whatever I typed";'
When I leave out the ' it works fine.

How can I fix this?

 

[Graham Mandeno]

Hi John

It should be only a single quote that causes the error. The reason is that
SQL sees the quote character as the end of the string you are inserting.

To remedy this, replace every single quote in NewData with TWO single
quotes:

str = "Insert Into lutblDescript (dscDescription) Values ('" _
& Replace(NewData, "'", "''") & "');"

 

[John F]

Thanks error fixed.

Hi John

It should be only a single quote that causes the error. The reason is that
SQL sees the quote character as the end of the string you are inserting.

To remedy this, replace every single quote in NewData with TWO single
quotes:

str = "Insert Into lutblDescript (dscDescription) Values ('" _
& Replace(NewData, "'", "''") & "');"


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using:

Private Sub itmDescription_NotInList(NewData As String, Response As
Integer)

Dim str As String
Dim cmd As New ADODB.Command

Set cmd.ActiveConnection = CurrentProject.Connection
str = "Insert Into lutblDescript (dscDescription) Values ('" & NewData &
"');"
cmd.CommandText = str

If MsgBox("Value is not in list. Add it?", vbOKCancel) = vbOK Then
Response = acDataErrAdded
cmd.Execute
Else
Response = acDataErrContinue
Me.itmDescription.Undo
End If

Set cmd = Nothing

End Sub

It works fine as long as I do not use special characters in the field such
as ' .If I do then I get an Syntax error (missing operator) in query
expression "Whatever I typed";'
When I leave out the ' it works fine.

How can I fix this?