Run Error 3077 - The Apostrophe problem

[jclover]

I'm using multiple criteria to pull up records in a form. However, whenever the "Retailer" field has a text string with an apostophe in it (example: Fred's Electronics), it errors out "3077". I'm assuming it reads the ' and it cuts the field short. Is there any way to define the value, or write the code to handle this?

rs.FindFirst "[SKU] = '" & Me![SKU] & "' AND [CHANNEL] = '" & Forms!frmSpecialPricing![Retailer] & "'"

[Rabbit]

Try:

rs.FindFirst "[SKU] = """ & Me![SKU] & """ AND [CHANNEL] = """ & Forms!frmSpecialPricing![Retailer] & """"
 

[jclover]

Now it's runtime error '2186'. Any thoughts?

[Rabbit]

What about:

rs.FindFirst "[SKU] = ""Forms!FormName![SKU] & "" AND [CHANNEL] = ""Forms!frmSpecialPricing![Retailer]"""
 

[jclover]

Seems to work. Thank you.

[jclover]

I take that back. It executes the code...no errors, but now the find function no longer works. It won't filter for any criteria.

[jclover]

I think I got it with this:

Dim rs As Object
Set rs = Me.Recordset.Clone
Dim ChannelAdj As String
Dim Channel As String
 
Channel = Forms!frmSpecialPricing![Retailer]
 
If InStr(1, Channel, "'", vbTextCompare) > 0 Then
    ChannelAdj = Replace(Channel, "'", "''", , , vbTextCompare)
Else:
    ChannelAdj = Channel
End If
 
rs.FindFirst "[SKU] = '" & Me![SKU] & "' AND [CHANNEL] = '" & ChannelAdj & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 

[Rabbit]

I think I got it with this:

Dim rs As Object
Set rs = Me.Recordset.Clone
Dim ChannelAdj As String
Dim Channel As String
 
Channel = Forms!frmSpecialPricing![Retailer]
 
If InStr(1, Channel, "'", vbTextCompare) > 0 Then
    ChannelAdj = Replace(Channel, "'", "''", , , vbTextCompare)
Else:
    ChannelAdj = Channel
End If
 
rs.FindFirst "[SKU] = '" & Me![SKU] & "' AND [CHANNEL] = '" & ChannelAdj & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 

Excellent, thanks for posting the solution.