Coding issue for a search

[RH]

I am working on a database to organize a set of books. Access
generated the following code for a combo box to locate a record by it's
title:

Private Sub Combo127_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Title] = '" & Me![Combo127] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The code works fine unless the book title contains an ' , then it gives
me a syntax error on the FindFirst statement. Anyone have any ideas on
how to correct this?

Thanks,

Ray

 

[Stefan Hoffmann]

hi Ray,

Dim rs As Object

Better:
Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[Title] = '" & Me![Combo127] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
The code works fine unless the book title contains an ' , then it gives
me a syntax error on the FindFirst statement. Anyone have any ideas on
how to correct this?

Just visualize it, when your title contains a ' then the string looks
like this:

"[Title] = 'o'banion'"

There is a single quotation mark missing. This will do it:

rs.FindFirst "[Title] = '" & Replace(Me![Combo127], "'", "''") & "'"


I'm using a public function, requires Access 2000 or higher:

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'") As String

SQLQuote = Replace(AString, ADelimiter, ADelimiter & ADelimiter)

End Function


This function also avoids SQL injection.


mfG
--> stefan <--

 

[RH]

Stefan Hoffmann laid this down on his screen :

hi Ray,

Dim rs As Object

Better:
Dim rs As DAO.Recordset

Set rs = Me.Recordset.Clone
rs.FindFirst "[Title] = '" & Me![Combo127] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
The code works fine unless the book title contains an ' , then it gives me
a syntax error on the FindFirst statement. Anyone have any ideas on how to
correct this?

Just visualize it, when your title contains a ' then the string looks like
this:

"[Title] = 'o'banion'"

There is a single quotation mark missing. This will do it:

rs.FindFirst "[Title] = '" & Replace(Me![Combo127], "'", "''") & "'"


I'm using a public function, requires Access 2000 or higher:

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'") As String

SQLQuote = Replace(AString, ADelimiter, ADelimiter & ADelimiter)

End Function


This function also avoids SQL injection.


mfG
--> stefan <--

Stefan,

Thanks for the reply. I actually finally just found that by replacing
the code
rs.FindFirst "[Title] = '" & Me![Combo127] & "'"

with
rs.FindFirst "[Title] = """ & Me![Combo127] & """"

also resolves the problem. It had to do with the way the Jet Engine
passes the delimiters.

Ray

 

[Carl Rapson]

Use the Replace function to replace each occurrence of ' in the string
with two single quotes:

rs.FindFirst "[Title] = '" & Replace(Me![Combo127],"'","''") & "'"

Carl Rapson

 

[Stefan Hoffmann]

hi Ray,

Thanks for the reply. I actually finally just found that by replacing
the code
rs.FindFirst "[Title] = '" & Me![Combo127] & "'"

with
rs.FindFirst "[Title] = """ & Me![Combo127] & """"

also resolves the problem. It had to do with the way the Jet Engine
passes the delimiters.

This is not quite correct. It will now fail when your title contains a ".


mfG
--> stefan <--