List Box Error



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])

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Box Error 3077 - Access 2003 1
Search combo box 2
More apostrophes!! 6
Combo Box Syntax Error 1
RunTime Error 3070 8
Error 2147352567 2
Textbox Filter 4
Error 2237 8