Error in Lookup Combobox.

[Gideon]

I used the Wizard to create a combo box to find a record. It made the
following code:
=========================================================
Private Sub CompanyCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[COMPANY] = '" & Me![CompanyCombo] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
==========================================================

It works fine except when trying to find a record of a company with an
apostrophe, i.e. --> ' <-- (a single quotation mark)

It gets stuck in the statement:

rs.FindFirst "[COMPANY] = '" & Me![CompanyCombo] & "'"

The reason for the error is quite clear: It is due to the apostrophe in
the company name.

Is there an elegant way to solve this problem?

Thanks

Gideon

 

[Van T. Dinh]

You need to replace the single quotes with 2 single quotes. Try:

rs.FindFirst "[COMPANY] = '" &
Replace(Me![CompanyCombo], "'", "''") & "'"

The second argument of Replace is DQ + SQ + DQ
and the 3rd argument is DQ + SQ + SQ + DQ