Goto record in subform based on change in parent textbox? Help!

[Bretto]

I almost have this working; I have a form with a text box (txtcusfnd) in the parent form and a subform with a Datasheet table (SF_CusFnd). As you type the customer name in txtCusFnd it bookmarks the record in the SF_CusFnd with the closest value to txtCusFnd moving to a closer value with each letter you type.

HERE IS THE CODE I HAVE USED SO FAR:

Private Sub txtCusFnd_Change()
' Find the record that closest matches the control as you type.

Dim strCusFnd As String

strCusFnd = Me.txtCusFnd.Text
strCusFnd = strCusFnd & "*"

Forms!f_cusFnd.SF_CusFnd.Form.RecordsetClone.FindFirst _
"[CusNam] like '" & strCusFnd & "'"

Forms!f_cusFnd.SF_CusFnd.Form.Bookmark = _
Forms!f_cusFnd.SF_CusFnd.Form.RecordsetClone.Bookmark


End Sub

THE PROBLEM IS:

If you type an apostrophe (') it will create an invalid string error!. Can I get around this somehow by replacing the apostrophe (') with a star (*) in the code? If so how do I do it? Or if there are any other ways around this problem it would be cool to know.

Your help will be appreciated.

Cheer's
Bretto.

 

[cdidion]

You can use the Replace function :

strCustFind = Replace(strCustFind, "'", "*&quot

This will replace each occurence (sp?) of an apostrophe with an asterisk. -Chris Didion
Matrix Automation, MCP

 

[Bretto]

Sorry chris this does'nt work, keep getting a "Compile Error", "Sub or function not defined".

Do you have this function, so I may use it or can you help me write it. I would imagine that it would be a (for, next loop). Using various len(), mid(), right() and Left() statements. Am I on the right track?

Cheers Anyway,

Bretto.