DoCmd.FindRecord with Apostrophe

[hdsjunk]

I have a combo box containing all database records on my form that the user
can select from. When a selection is made I move the focus to a Store Name
field where I use the DoCmd.FindRecord method to move to the record selected
in the combo box. Here is the specific code:

DoCmd.FindRecord cboMbrNbr.Column(1)

However, this does not work for any records where an apostrophe exists
within the store name. I have tried using the Replace function to change the
apostrophe to either Chr(180) or 2 single quotes, but neither worked. I also
notice if I try to use the Find/Replace dialog box for the same store name,
it doesn't find it either. Anyone help would be greatly appreciated.

Thanks!

 

[Randy Balbuena]

I have a combo box containing all database records on my form that the user
can select from. When a selection is made I move the focus to a Store
Name
field where I use the DoCmd.FindRecord method to move to the record
selected
in the combo box. Here is the specific code:

DoCmd.FindRecord cboMbrNbr.Column(1)

However, this does not work for any records where an apostrophe exists
within the store name. I have tried using the Replace function to change
the
apostrophe to either Chr(180) or 2 single quotes, but neither worked. I
also
notice if I try to use the Find/Replace dialog box for the same store
name,
it doesn't find it either. Anyone help would be greatly appreciated.

Thanks!

Hdsjunk,

The code should work as follow:

Private Sub cboMbrNbr_AfterUpdate()
txtMbrNbr.SetFocus
DoCmd.FindRecord cboMbrNbr.Column(1)
End Sub

If it does not work you are either grabbing the wrong column index (1) or
the apostrophes on both tables don't match (different Character codes).

Apostrophe: ASCII char code 39 (Next to the ENTER key)
Tilde ASCII char code 96 (Below the ESC key)
http://randyb.home.att.net/docs/ASCII-Reference/

If you are using Unicode there are many other characters that look just
alike.

 

[John Vinson]

Try this: Create Public Function, then apply to your variable in expression

Public Function ReplaceApostrophe(strCompanyName As String) As String
ReplaceApostrophe = "'" & Replace( strCompanyName, "'", "''') & "'"
End Function

Minor but critical typo: should be

ReplaceApostrophe = "'" & Replace( strCompanyName, "'", "''") & "'"

easy to do when there are so many ''"'"''"''"''"' in succession!

John W. Vinson[MVP]