Coding issue for a search

  • Thread starter Thread starter RH
  • Start date Start date
R

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
 
hi Ray,
Dim rs As Object
Click to expand...
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?
Click to expand...
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 Hoffmann laid this down on his screen :
hi Ray,
Dim rs As Object
Click to expand...
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?
Click to expand...
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 <--
Click to expand...

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
 
Use the Replace function to replace each occurrence of ' in the string
with two single quotes:

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

Carl Rapson
 
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.
Click to expand...
This is not quite correct. It will now fail when your title contains a ".


mfG
--> stefan <--
 
Back
Top