Error 3077 - use of apostrophe

[dotcanada]

Good day.

I have a combo box (txtSearch) but it's not allowing me to search for names which have an apostrophe. I get Error 3077. I looked at several posts on this but none were able to help me.

Here is my code:

Private Sub txtSearch_AfterUpdate()
'Find record based on contents of txtSearch.
Dim strSearch As String
On Error GoTo errHandler
'Delimited for text search.
strSearch = "Name = " & Chr(39) & Me!txtSearch.Value & Chr(39)

'Delimited for numeric values.
'strSearch = "OrderID = " & Me!txtSearch.Value

'Find the record.
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
errHandler:


MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
End Sub

Thanks in advance.

[pbaldy]

Try

strSearch = "Name = " & Chr(34) & Me!txtSearch.Value & Chr(34)

[apr pillai]

strSearch = "Name = " & Chr(39) & Me!txtSearch.Value & Chr(39)

Change to:

strSearch = "Name = " & chr(34) & Chr(39) & Me!txtSearch.Value & Chr(39) & chr(34)

[John_G]

Not surprising. You are using single quotes (= apostrophes = chr(39)) to delimit a string which has an apostrophe in it, which causes an error.

If you know that the text to search for will never contain double-quotes ("), you can use those as delimiters:

strSearch = "Name = " & Chr(34) & Me!txtSearch.Value & Chr(34)

If your search string could contain either single- or double-quotes (but not both), you could do something like this:

Private Sub txtSearch_AfterUpdate()
 'Find record based on contents of txtSearch.
  Dim strSearch As String, Delim as string
  On Error GoTo errHandler
  'Delimited for text search.
  Delim = chr(39) ' start with single quote
  if instr( Me!txtSearch.Value,"'") <> 0 then
    Delim = chr(34)   ' Change delimiter to double-quote
  endif
  strSearch = "Name = " & Delim & Me!txtSearch.Value & Delim

  'Delimited for numeric values.
  'strSearch = "OrderID = " & Me!txtSearch.Value

  'Find the record.
  Me.RecordsetClone.FindFirst strSearch
  Me.Bookmark = Me.RecordsetClone.Bookmark
    Exit Sub
 errHandler:
  MsgBox "Error No: " & Err.Number & "; Description: " & _
   Err.Description
 End Sub

[dotcanada]

Change to:

strSearch = "Name = " & chr(34) & Chr(39) & Me!txtSearch.Value & Chr(39) & chr(34)

This got rid of the error, but it's no longer bringing up the record of who I typed in.

[dotcanada]

Thanks John and pbaldy. I just changed the code to chr(34). Works great now.

[pbaldy]

No problem.