Not in list error

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?
 
G

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, "'", "''") & "');"
 
J

John F

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NotInList Event 4
MakeSchedule function 6
Not In List 1
acDialog - Form Invisible? 1
One Combo Box won't work?? 8
Sub to Function 5
Requery form record source 2
Another Not in List not working 11

Top