Combo Lookup encounters problem for customer with Apostrophe in name 5

[molly]

I have a customer who uses an apostrophe in their name.
Below is my On Event and On GotFocus vba for my lookup combo. It works. however, not for this 1 customer named EG .... Wilson's Battery.

Is there a way to fix this?
Thanks. Molly

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

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

Me.CboAccountNameFilter = Null
End Sub

-------------------------------------------------------
Private Sub CboAccountNameFilter_GotFocus()
Me.Refresh
End Sub
---------------------------------------------------

 

[TheAceMan1]

How are ya molly . . .

You need to construct a substring. Try the following. Try the following ... Note: The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Click OK.

[blue]   Dim rs As [purple][b]DAO[/b][/purple].Recordset, CBx As ComboBox, Cri As String, DQ As String

   DQ = """"
   Set CBx = Me!CboAccountNameFilter
   Set rs = Me.Recordset.Clone
   
   rs.FindFirst "[AccountName] = " & DQ & CBx & DQ
   If Not rs.EOF Then Me.Bookmark = rs.Bookmark
   
   CBx = Null
   Set CBx = Nothing
   Set rs = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[molly]

Aceman - it works great. For some reason, i think the lookup is now zippier.

Thanks for much again. Nice tip.
Molly

 

[LisaWay]

Another way you could do this would be to keep your code the way it is, except change:

rs.FindFirst "[AccountName] = '" & Me![CboAccountNameFilter] & "'"

to:

rs.FindFirst "[AccountName] = """ & Me![CboAccountNameFilter] & """"

Regards,
Lisa

 

[PHV]

A safer way:

rs.FindFirst "[AccountName]='" & Replace(Me![CboAccountNameFilter], "'", "''") & "'"

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

 

[TheAceMan1]

Howdy PHV . . .

[blue]Debug.Print "[AccountName] = '" & Replace("[red][b]Wilson's[/b][/red] Battery", "'", "''") & "'" returns:

[AccountName] = [red][b]'[/b][/red]Wilson[red][b]''[/b][/red]s Battery[red][b]'[/b][/red][/blue]

Am I missing something here?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[LisaWay]

TheAceMan1:

PHV's solution is correct. You need the double single-quotes because they are within a single-quoted string.

Regards,
Lisa

 

[TheAceMan1]

Howdy LisaWay . . .

Yeahhhhh, figured after I posted (been at it a little too long today) . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[molly]

PHV and Aceman - I threw in PHV's last idea. I also changed something, substituting AccountID for AccountName because i needed to concatenate accountname and accountID in the row source for the selection. all works well.

One question. Since i added PHV's one row below where it says rsFind, are there any places that i need to strip out?
EG the CBx or such things? Just want to see if there is any cleanup needed.

I will later take out the Remark note where i say "take out". thanks molly



======================================================
Private Sub CboAccountNameFilter_AfterUpdate()

' Find the record that matches the control. Helped from Aceman and PHV.

Dim rs As DAO.Recordset, CBx As ComboBox, Cri As String, DQ As String

DQ = """"
Set CBx = Me!CboAccountNameFilter
Set rs = Me.Recordset.Clone

' take out - rs.FindFirst "[AccountID] = " & DQ & CBx & DQ

rs.FindFirst "[AccountID]='" & Replace(Me![CboAccountNameFilter], "'", "''") & "'" 'from PHV
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

CBx = Null
Set CBx = Nothing
Set rs = Nothing

End Sub
=========================================================

 

[PHV]

Private Sub CboAccountNameFilter_AfterUpdate()
' Find the record that matches the control.
    Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone
    rs.FindFirst "[AccountID]='" & Replace(Me![CboAccountNameFilter], "'", "''") & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    Me!CboAccountNameFilter = Null
End Sub

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

 

[molly]

To PHV - works great. Thanks so much for something that i can make like a template or boiler plate. Molly

 

[molly]

PHV - I realize now that i have to use a NUMBER field called AUTOCUSID and not the TEXT field AccountID. So now I need to fix some of the quotes. Can you help me?
I copied the text commands to help get it started. Please refer to the section that says PHV. It is the row below it.
thanks alot. I remarked out your text method just in case. Molly

Private Sub CboAccountNameFilter_AfterUpdate()

' Find the record that matches the control.
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

'PHV need to fix next row quotes - Next row is for AUTOCUSID as a NUMBER field.
'rs.FindFirst "[AutoCusID]='" & Replace(Me![CboAccountNameFilter], "'", "''") & "'"


'Next row is for ACCOUNTID as a TEXT field
rs.FindFirst "[AccountID]='" & Replace(Me![CboAccountNameFilter], "'", "''") & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Me!CboAccountNameFilter = Null

End Sub

 

[PHV]

rs.FindFirst "[AutoCusID]=" & Me![CboAccountNameFilter]

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