List Box Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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])
 
Back
Top