Combobox lookup function gives syntax error

[Ronny Sigo]

Hello all,
I have made a form containing a combox which must look up values (names) in
a table. It works fine until the moment I try to put in a name containg an
apostrophe. (e.g. d'Haen). At this moment I receive an errormsg (syntax
error). After closing the msg Access freezes.
Can anybody tell me if there is a solution for this problem? I included the
code below.
Any help very much appreciated!

Ronny Sigo

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

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

[Trevor Best]

Ronny Sigo wrote:

Hello all,
I have made a form containing a combox which must look up values (names) in
a table. It works fine until the moment I try to put in a name containg an
apostrophe. (e.g. d'Haen). At this moment I receive an errormsg (syntax
error). After closing the msg Access freezes.
Can anybody tell me if there is a solution for this problem? I included the
code below.
Any help very much appreciated!

Ronny Sigo

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

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

rs.FindFirst "[Achternaam] = '" & Replace(Me![Combo39],"'","''") & "'"

A few code tips for you:
* Naming conventions help in code, e.g. call your combo box "cboAchternaam".
* Always close recordsets when your finished with them and set the
object variable to Nothing afterwards. Don't let it to VB/VBA to do it
for you, that doesn't always work. Being explicit does.
* Your use of .FindFirst suggest DAO, if that's the case then check for
rs.NoMatch, your code looks like a mismatch of DAO and ADO
* Declare variables explicitly, As Recordset rather than As Object, this
makes things more clear in code.

Using the above, when you go back to this code a year later you'll
instantly understand what's going on.
--
Error reading sig - A)bort R)etry I)nfluence with large hammer