List Box Error

H

Hugh McK

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.
 
G

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
RunTime Error 3070 11
Error 2147352567 2
Error 2237 8
Textbox Filter 4
Searching 3
Copy Record Button Function 1

Top