MS Access 2003 - Combo box Search fails when there is a apostrophe in the Search field

I have a form that uses the code listed below on the after update of a combo box to search for the selected company name. However, if the company name contains an apostrophe (Example: Dave's Trucking,  George's Service Center) the seasrch fails and generates a VBA Error statement Run-Time Error 3077, Syntax error (missing operator )in expression.

Any suggestions as to how to get around this problem other than removing the apostrophe out of each name (not practical).

After Update event on company (combo25) search field.
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[COMPANY] = '" & Me![Combo25] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Any suggestions on how to handle this problem.

Lou DufresneIT Business Analyst CSM / Project ManagerAsked:
Who is Participating?
Leigh PurvisConnect With a Mentor Database DeveloperCommented:

rs.FindFirst "[COMPANY] = """ & Me![Combo25] & """"
Leigh PurvisDatabase DeveloperCommented:
You could try

rs.FindFirst "[COMPANY] = '" & Replace(Me![Combo25],"'","''") & "'"
Rey Obrero (Capricorn1)Connect With a Mentor Commented:

 rs.FindFirst "[COMPANY] = " & chr(34) & Me![Combo25] & chr(34) & ""
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Leigh PurvisDatabase DeveloperCommented:
< :-S >
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Actually, every time you manipulate strings in VBA, or import/export, or a whole lot of other stuff, it will fail if there are apostrophe marks in the string.
RgGray3Connect With a Mentor Commented:
My record search routines simplify the process,  I allow the user to type a minimum of 3 characters into a text control. Once the minimum number of characters have been entered I make a Search Button Appear onthe form.

The search populates a subform with all records that START with those characters.  You could type in DAV and it would retrieve Daves tree...  Dave's tree...   Davis Food ....   and then use the subform to select the desired record.

Works fast and clients like it because they don't even need to remember the whole name.

You could also incorporate a SOUNDEX function (many samples can be found)

Has complete source code and explains the usage

It Ingnores Case and punctuation

Drawback it might make multiple matches on initial call... IE:   Smith and Smyth may be concidered a match.  If you are dealing with longer names it should be less of a concern.

What I would do at that point is use the SOUNDEX function to filter my total list.  If only one potential match is found it selects that record.  If more than one match exists, populate a small grid subform (as described above) and make the final selection.  

Of you could do a find first, find next.

You would store the SOUNDEX code for each name in a seperate field in the table, and do the Soundex of the name you are searching for and match Soundex codes.

To speed and simplify the process you could do a Soundex only on the first X number of characters  allow the user to enter the same X number of characters in a search text box.

Lou DufresneIT Business Analyst CSM / Project ManagerAuthor Commented:
Thanks everyone for the great ideas

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.