AfterUpdate event FindFirst 1

[BrockLanders]

I have a combo box on a form that finds a record on a subform. The source of the combo box is a text field in a query. The code for the event was created by Access' combo box wizard. It works fine unless there's a ' in the value of the field, for example, 2' waves. I'll get a syntax error. However, if I remove the ' so the value is 2 waves, it works fine. Here is my code:

Sub Combo22_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Program] = '" & Me![Combo22] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

The error is stopping at the line Me.RecordsetClone...... so I'm guessing it is the "[Program] = '" & Me![Combo22] & "'" portion.

Any ideas how to get the code to lookup values with or without a '

Thanks in advance for any help!

 

[RoyVidar]

Try double quoting, which works well with DAO recordsets:

[tt]Me.RecordsetClone.FindFirst "[Program] = """ & Me![Combo22] & """"[/tt]

Roy-Vidar

 

[BrockLanders]

Right again Roy-Vidar. THANKS!

 

[BrockLanders]

I justed noticed a new problem when I use the combo box. If the field the combo box is using contains a double quotation (") I get a syntax error.

Is there a way to use double quotes without getting an error?

Thanks in advance

 

[PHV]

This is what I personally use:
Me.RecordsetClone.FindFirst "[Program]='" & Replace(Me![Combo22], "'", "''") & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BrockLanders]

Thanks much PH, it worked perfectly.