list box and go to command

  • Thread starter Thread starter Dan
  • Start date Start date
D

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?
 
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
 
Dan said:
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?
Click to expand...

You can deal with the apostrophes by changing the code slightly. Change
this:
rs.FindFirst "[Full Name] = '" & Me![List101] & "'"
Click to expand...

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