Dealing with apostrophes

[J.C.]

I am using the following module to locate records:

---------------------------------------------------
Private Sub CFRecordLocatorCBP1_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CF_Name] = '" & _
Me![CFRecordLocatorCBP1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
---------------------------------------------------

It works well as long as there is no apostrophe in the Me!
[CFRecordLocatorCBP1] string. For obvious reasons, it
fails if there is one.

Has anyone discovered an easy/simple workaround?

jc

 

[Dirk Goldgar]

I am using the following module to locate records:

---------------------------------------------------
Private Sub CFRecordLocatorCBP1_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CF_Name] = '" & _
Me![CFRecordLocatorCBP1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
---------------------------------------------------

It works well as long as there is no apostrophe in the Me!
[CFRecordLocatorCBP1] string. For obvious reasons, it
fails if there is one.

Has anyone discovered an easy/simple workaround?

If the string to be sought won't contain a double-quote, use that as the
string delimiter inside the SQL statement:

rs.FindFirst "[CF_Name] = " & _
Chr(34) & Me![CFRecordLocatorCBP1] & Chr(34)

If the string to be sought may contain a double-quote, use the Replace()
function to double it up:

rs.FindFirst "[CF_Name] = " & _
Chr(34) & _
Replace(Me![CFRecordLocatorCBP1], """", """""") & _
Chr(34)

 

[Guest]

-----Original Message-----

I am using the following module to locate records:

---------------------------------------------------
Private Sub CFRecordLocatorCBP1_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CF_Name] = '" & _
Me![CFRecordLocatorCBP1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
---------------------------------------------------

It works well as long as there is no apostrophe in the Me!
[CFRecordLocatorCBP1] string. For obvious reasons, it
fails if there is one.

Has anyone discovered an easy/simple workaround?

If the string to be sought won't contain a double-quote, use that as the
string delimiter inside the SQL statement:

rs.FindFirst "[CF_Name] = " & _
Chr(34) & Me![CFRecordLocatorCBP1] & Chr(34)

If the string to be sought may contain a double-quote, use the Replace()
function to double it up:

rs.FindFirst "[CF_Name] = " & _
Chr(34) & _
Replace(Me![CFRecordLocatorCBP1], """", """""") & _
Chr(34)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Dirk -

Worked great. Thanks!

jc