Combo Box on Form with Apostrophe Problem

[jwjw]

I have a combo box on a form that finds records in a table or query. It's a lastname, firstname lookup. It works fine except for people with an apostrophe in their name - i.e. O'Malley, George. It gives me a 3077 runtime error (Missing operator in expression). The code looks like:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[lfname] = '" & Me![Combo15] & "'"
Me.Bookmark = rs.Bookmark
Me.Combo15 = lfname
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,acMenuVer70
Me.Combo15.Requery
Me.Combo15 = Null

Help in solving my problem is greatly appreciated.

Jack

 

[jebry]

Hi Jack!

In a General Module (not in a form) declare the following constant:

Public Const Quote = """"

Then change your code to read:

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[lfname] = " & Quote & Me![Combo15] & Quote
Me.Bookmark = rs.Bookmark
Me.Combo15 = lfname
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,acMenuVer70
Me.Combo15.Requery
Me.Combo15 = Null

hth
Jeff Bridgham

 

[TorF]

Yes you have to replace ' by ''

Note that you can use the strings.replace(...) function :

rs.FindFirst = strings.replace("[lfname] = '" & Me![Combo15] & "'","'","''&quot

 

[jwjw]

Thanks Jeff. That worked great. I appreciate the help.

Jack

 

[jebry]

Hi Jack!

Glad to help, but I must admit that I got the idea from the Access Programmers Handbook. Lot's of good stuff in there!

Jeff Bridgham