Not in list error

  • Thread starter Thread starter John F
  • Start date Start date
J

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?
 
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, "'", "''") & "');"
 
Thanks error fixed.

Graham Mandeno said:
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


John F said:
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?
Click to expand...
Click to expand...
 
Back
Top