Syntax Error in Where clause

Question

I'm new to Access and have a couple basic questions. First, I have an unbound combo box that looks up a company by a subsidiary name and bookmarks the company record. The code is straight from the Access wizard:

Me.RecordsetClone.FindFirst "[txtSubsidiaryName] = '" & Me![cboCompanyNameSearch] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

This works great until I have a subsidiary with an apostrophe in its name. I then get a syntax error:

Run time error 3077
Syntax error (missing operator) in expression.

I should be able to figure this out but have not. My preference is to allow the apostrophe. My second choice would be to trap it and not allow it.

Answer

Tom,

The functions in P.S. should solve your problem with where expression.

HTH,
Shamil

P.S. The code:

Function smsSuperFixUp(ByVal varValue As Variant, _
                    Optional ByVal strQuote As String = "'") As Variant

        'Add the appropriate delimiters, depending on the data type.
        'Put quotes around text, "#" around dates, and nothing
        'around numeric values.

        Select Case VarType(varValue)
                Case vbInteger, vbSingle, vbDouble, vbLong, vbCurrency
                        smsSuperFixUp = smsNoCommasAsDecDelimiter(varValue)
                Case vbString
                        smsSuperFixUp = strQuote & smsQuoteDuplicate(varValue, strQuote) & strQuote
                Case vbDate
                        smsSuperFixUp = "#" & Format(varValue, "mm") & "/" _
                                    & Format(varValue, "dd") & "/" _
                                    & Format(varValue, "yyyy") & "#"
                Case Else
                        smsSuperFixUp = Null
        End Select

End Function

'*+
'
'  Function smsNoCommasAsDecDelimiter (pvar As Variant)
'
'  Returns a string representing <pvar> number with a point as a decimal
separator.
'  (MS Access SQL scripts should have point symbol (".") as a decimal
separator !!!
'   If you build SQL scripts in Access Basic you have to supply them
'      with the numbers in this format. )
'
'*-
Function smsNoCommasAsDecDelimiter(pvar As Variant)
   On Error GoTo smsNoCommasAsDecDelimiter_err
   Dim strActualDecSeparator As String * 1
   Dim intDecSeparatorPos As Integer
   Dim strRet As String

   strActualDecSeparator = Mid(CStr(9.9), 2, 1)

   strRet = CStr(pvar)
   intDecSeparatorPos = InStr(1, strRet, strActualDecSeparator)
   If intDecSeparatorPos > 0 Then
      strRet = Left(strRet, intDecSeparatorPos - 1) & _
          "." & _
          Mid(strRet, intDecSeparatorPos + 1)
   End If
   smsNoCommasAsDecDelimiter = strRet
smsNoCommasAsDecDelimiter_Done:
   Exit Function
smsNoCommasAsDecDelimiter_err:
   Resume smsNoCommasAsDecDelimiter_Done
End Function

Public Function smsQuoteDuplicate(pstr, Optional ByVal strQuote = "'")
  On Error Resume Next

  Dim strDup As String, strRem As String, intPos As Integer
  strDup = ""
  strRem = pstr
  intPos = InStr(1, strRem, strQuote)
  If intPos <> 0 Then

   While intPos <> 0
    strDup = strDup & Mid(strRem, 1, intPos) & strQuote
    strRem = Mid(strRem, intPos + 1)
    intPos = InStr(1, strRem, strQuote)
   Wend
  End If
  strDup = strDup & strRem
  smsQuoteDuplicate = strDup

End Function