Coding issue for a search

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RH - 15 Mar 2007 16:15 GMT

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

Reply to this Message

Stefan Hoffmann - 15 Mar 2007 16:45 GMT

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 <--

Reply to this Message

RH - 15 Mar 2007 17:09 GMT

Stefan Hoffmann laid this down on his screen :

> hi Ray,
>
[quoted text clipped - 30 lines]
> 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

Reply to this Message

Stefan Hoffmann - 15 Mar 2007 17:21 GMT

hi Ray,

> Thanks for the reply. I actually finally just found that by replacing
> the code
[quoted text clipped - 5 lines]
> 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 <--

Reply to this Message

Carl Rapson - 15 Mar 2007 17:12 GMT

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

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

Carl Rapson

>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:
[quoted text clipped - 15 lines]
>
> Ray

Reply to this Message