can an apostrophe (') in a combo box create an error?

  • Thread starter Thread starter maura
  • Start date Start date
M

maura

I have a combo box so that I can look up a company's name.
Some of these companies have apostrophes, ie: Bill's
Transport; PK's Auto Transport. When I use the combo box
to find these companies I get an error: Run time error
3077 Syntax error (missing operator) in expression. The
other companies without ', come up fine. What am I missing?

Thanks, Maura.


Private Sub Combo30_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TransportCompany] = '" & Me![Combo30]
& "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Private Sub Form_Current()

Combo30.Value = Me.TransportCompany

End Sub
 
maura said:
I have a combo box so that I can look up a company's name.
Some of these companies have apostrophes, ie: Bill's
Transport; PK's Auto Transport. When I use the combo box
to find these companies I get an error: Run time error
3077 Syntax error (missing operator) in expression. The
other companies without ', come up fine. What am I missing?

Thanks, Maura.


Private Sub Combo30_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TransportCompany] = '" & Me![Combo30]
& "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

Private Sub Form_Current()

Combo30.Value = Me.TransportCompany

End Sub
Click to expand...

Since the criteria expression you're passing to FindFirst uses
apostrophes to delimit the company name to be searched for, having an
apostrophe in the middle of it gums up the works. If you are confident
the company name won't contain a double-quote character ("), use that as
the delimiter:

rs.FindFirst "[TransportCompany] = " & _
Chr(34) & Me![Combo30] & Chr(34)


or

rs.FindFirst "[TransportCompany] = """ & _
Me![Combo30] & """"

Note that Chr(34) returns the double-quote character, while doubling-up
the quote characters inside a quoted literal also puts one quote
character in there. So the above two statements are equivalent.

If you think a company name might contain the double-quote, too, like
maybe

Joe's "Really Fast" Towing

then you have to force the embedded quotes to be doubled up. you can do
that like this:

rs.FindFirst "[TransportCompany] = " & _
Chr(34) & _
Replace(Me![Combo30], """", """""") & _
Chr(34)
 
Back
Top