Lookup has problem with lastname's with an apostrophe 2

[cimoli]

I have a customer name lookup field on my form.
It was created by the MS wizard. It works.
Except for any person with an apostrophe in their name like
O'Rorke or O'Malley. I get a debug error when I pick such a
lastname. Below is the present AfterUpdate code in the combo box.

Is there a way to fix this to allow an apostrophe last name??
thanks much. Cimoli.


Private Sub CboCusName_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastName] = '" & Me![cboCusName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[dhookom]

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

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

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya cimoli . . .

Try this:

[blue]   Dim rs As DAO.Recordset, DQ As String, Cri As String
   
   DQ = """"
   Cri = "rs.FindFirst [LastName] = " & DQ & Me![cboCusName] & DQ
   
   Set rs = Me.Recordset.Clone
   rs.FindFirst Cri
   
   
   If Not rs.NoMatch Then
      Me.Bookmark = rs.Bookmark
   Else
      MsgBox "'" & [Lastname] & "' Not Found! ... or Doesn't Exist!"
   End If[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[cimoli]

Duane - I just tried the last one. I get a debug error yellowed at:

rs.FindFirst Cri

Can you check? thanks as usual. Cimoli

 

[cimoli]

Boy, I need new glasses. I got mixed up on the responses. It was Aceman's code that had the debug. Sorry. so I just tried Duane's. It works well. So if no objections, I will use Duane's. Thanks to both of you. Cimoli

 

[TheAceMan1]

cimoli . . .

So sorry. It should've been:

[blue]Cri = "[LastName] = " & DQ & Me![cboCusName] & DQ[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

Another (safer) way:

rs.FindFirst "LastName='" & Replace(Me!cboCusName, "'", "''") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886