How do you enter a string that contain's an "'"? Is this a bug?

[sandi]

Access 2000
I have a cboIngredients box with NotInList code that asks if I want to
add new record to underlying ingredients table.
My problem is when I add an ingredient that contains and apostrophe I
get and error message.
My code is this:

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

Dim strsql As String, x As Integer
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into Ingredients ([Ingredient]) values ('" &
NewData & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Everything works great until I tried to add"Bush's baked beans" and got
error!

Any suggestions?

Thanks in advance!

 

[Guest]

Hi Sandi,

Try substituting Chr(34) in place of the single quotes:

strsql = "Insert Into Ingredients ([Ingredient]) values (" & Chr(34) &
NewData & Chr(34) & ")"


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Access 2000
I have a cboIngredients box with NotInList code that asks if I want to
add new record to underlying ingredients table.
My problem is when I add an ingredient that contains and apostrophe I
get and error message.
My code is this:

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

Dim strsql As String, x As Integer
x = MsgBox("Do you want to add this value to the list?", vbYesNo)
If x = vbYes Then
strsql = "Insert Into Ingredients ([Ingredient]) values ('" &
NewData & "')"
'MsgBox strsql
CurrentDb.Execute strsql, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Everything works great until I tried to add"Bush's baked beans" and got
error!

Any suggestions?

Thanks in advance!

 

[Douglas J Steele]

Rather than

strsql = "Insert Into Ingredients ([Ingredient]) values ('" & NewData & "')"

try

strsql = "Insert Into Ingredients ([Ingredient]) values (" & Chr$(34) &
NewData & Chr$(34) & ")"

(Note that I've removed the two ' you had)

 

[sandi]

Great! I will give that a whirl!
Thanks
Sandi