Apostrophe error

[Tomm]

hi all.

I have a searchbox which pulls from a sql database a list of surnames, when clicked, it then populates the forms below.

It works fine except on names that have an apostrophe, such as O'Connor

I then get this message "Access error syntax error (missing operator) in expression"

Here is my code

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

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

searching the web so far has brought me no answers. Cheers all.

 

[Rabbie]

Try this

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

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Surname] = " & chr(34) & Me![Searchbox] &  chr(34)
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This should resolve the issue for you

 

[Tomm]

Thanks Rabbie.

Found another alternative actually:

rs.FindFirst "[Surname]='" & Replace(Me!Searchbox, "'", "''") & "'"

But I've implemented yours as it is slightly shorter code and makes more sense to me