list box and go to command

[Dan]

I have a list box with a list of first and last names in
it and I just used the wizard to create it. It created it
so that when you click on one of the entries, it will go
to that record. However if there is an apostasies in the
persons name, like O'Connell, it gives an error and won't
go to the record. The error number is 3077 and it
says "syntax error (missing operator) in expression".
This is the code that was created:

'Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Full Name] = '" & Me![List101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Is there anyway to get rid of this error?

 

[Dan Artuso]

Hi,
One way is to change the code to:
rs.FindFirst "[Full Name] = """ & Me![List101] & """"

that is , replace each single quote with two doubles.

Dan Artuso, MVP

 

[Dirk Goldgar]

I have a list box with a list of first and last names in
it and I just used the wizard to create it. It created it
so that when you click on one of the entries, it will go
to that record. However if there is an apostasies in the
persons name, like O'Connell, it gives an error and won't
go to the record. The error number is 3077 and it
says "syntax error (missing operator) in expression".
This is the code that was created:

'Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Full Name] = '" & Me![List101] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Is there anyway to get rid of this error?

You can deal with the apostrophes by changing the code slightly. Change
this:

rs.FindFirst "[Full Name] = '" & Me![List101] & "'"

to this:

rs.FindFirst "[Full Name] = " & Chr(34) & Me![List101] & Chr(34)

That replaces the apostrophes that were being used to delimit the name,
using double-quotes (") instead. That's what the Chr(34) character is,
the double-quote. If you ever encounter a name that includes a
double-quote, though, this code will fail. You can cover that base,
too, if you want to, like this:

rs.FindFirst "[Full Name] = " & _
Chr(34) & _
Replace(Me![List101], """", """""") & _
Chr(34)

But probably you don't have to do that.