syntax error (missing operator) in query expression

[p-rat]

I have a NotInList that is not working. If I enter in something in the
text box such as SALTY'S #3 it will return the error. If I take out
the single quote it works fine. It is suppose to accept the new text
and add it to the list. Here is the code:

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


I think this is something simple, but being a beginner I can't figure
it out. Thanks.

 

[Beetle]

Modify the query like;

sqlAddState = "Insert Into DisposalSite ([DisposalSite])
values (""" & NewData & """)"

replacing each single quote with two double quotes so it won't hang
when there is an apostrophe in the name.