On Not In List - NewData includes " ' " (1 Viewer)

[deadman]

Access 2K. I have a form w/ a combo box. The combo box has the following for the NotInList event.

Private Sub cboStorecategory_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
If NewData = "" Then Exit Sub
Msg = "The Store Category '" & NewData & "' is not in the Store Categories list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmstorecategory", , , , acAdd, acDialog, NewData
End If

Result = DLookup("[storecategory]", "tblstorecategory", "[storecategory]='" & NewData & "'")
If IsNull(Result) Then
Response = acDataErrContinue
MsgBox "Please select a Store Category from the list!"
Else
Response = acDataErrAdded
End If
End Sub

It works fine except when the new data, or in this case, the new "Store category" contains a single quote in the name such as "joe's place". How can I get around this to let the user enter data that contains the single quote. It is giving me a error '3075', Syntax error (missing operator) in query expression '[Storecategory].'xxx'x" Thanks in advance.

 

[Pat Hartman]

Whenever your data may contain single quotes, you need to use double quotes as the delimiter. I find the multiple quotes required to do this hard to read so I create a public constant in all of my databases. I use a separate module to hold ALL public variables.

Public Const QUOTE As String = """"

...
Msg = "The Store Category " & QUOTE & NewData & QUOTE & " is not in the Store Categories list." & CR & CR
...
Change the other references the same way.