List Box Error

[Guest]

Used LIST BOX to search a table of surnames to find a record. Works as
expected until you have a surname which includes an apostrophe, eg O'Dwyer
when you get runtime error 3077 syntex error (missing operator) in
expression. Code was generated by the Wizard.

Private Sub List40_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SURNAME] = '" & Me![List40] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Obviously line rs.FindFirst needs to be amended, but how?

Work around is not to include apostrophe in peoples' name, but that may
offend some.

 

[Graham Mandeno]

Hi Hugh

You can use double-quotes instead of singles (apostrophes). To put a
double-quote in a string, you use two adjacent ones:
rs.FindFirst "[SURNAME] = """ & Me![List40] & """"

Of course, this also will fail if one of your names has a double-quote in it
(which is unlikely). To cover all eventualities, use a function like this:

Public Function SqlQuote( sText as String ) as String
SqlQuote = "'" & Replace( sText, "'", "''" ) & "'"
End Function

Then you can say:
rs.FindFirst "[SURNAME] = " & SqlQuote(Me![List40])