Apostrophe Won't Allow Data To Be Added

[cokeitis via AccessMonster.com]

I have the code below on my form to allow users to add data to a table if
it's not in the list. The problem I'm having is that if the name that I'm
adding to the list have an apostrophe in it I get the following message
(syntax error - mission operator in query expression).

Can someone please tell me how to work around this...I have no idea.

Thanks

------------------------- CODE --------------------------------------
Private Sub cboCompanyName_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboCompanyName
Dim ctl As Control

Set ctl = Screen.ActiveControl

If MsgBox("Do you want to add '" & NewData & "' to the list?", vbYesNo +
vbQuestion, "Add To List") = vbYes Then
CurrentDb.Execute "INSERT INTO tblCompany (Company) SELECT '" & NewData & "'"
Response = acDataErrAdded

Else
Response = acDataErrContinue
ctl.Undo

End If
Exit_cboCompanyName:

Set ctl = Nothing

Exit Sub
Err_cboCompanyName:

MsgBox Err.Number & ": " & Err.Description

Resume Exit_cboCompanyName

End Sub

 

[Michel Walsh]

Hi,


Use DoCmd.RunSQL and FORMS!FormName!ControlName:

DoCmd.RunSQL "INSERT INTO tblCompany (Company)
VALUES(FORMS!myFormNameHere!ControlNameHere)"



In situations where you cannot use FORMS!Formname!ControlName, such as when
you are obliged to use CurrentDb, rather than DoCmd, double the ' through a
Replace:

"... ='" & Replace(myVar, "'", "''" ) & "' ; "
2 12 212 2112 21 2


where, as indication, 2 is below a " and 1 below a '


Hoping it may help,
Vanderghast, Access MVP

 

[Allen Browne]

Use double-quotes instead of single quotes as your delimiter.

See:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html