The quotes are killing me!

[Gina Maylone]

Hello! Happy Thanksgiving everyone!



I keep getting a syntax error with this:

  Private Sub Combo138_AfterUpdate()
On Error GoTo Combo138_AfterUpdate_Err
Debug.Print Combo138.Column(2)
Debug.Print Combo138.Column(3)
    DoCmd.SearchForRecord , "", acFirst, " [memberid] = " & Combo138.Column(2) And "[PrimaryLotNum] = " & "'" & Combo138.Column(3) & "'"
    '"[PrimaryLotNum] = " & "'" & Screen.ActiveControl & "'"
'WHERE ([AccountName]='" & Me.cboAccount & "'"
Combo138_AfterUpdate_Exit:
    Exit Sub
Combo138_AfterUpdate_Err:
    MsgBox Err.Description, vbCritical
    Resume Combo138_AfterUpdate_Exit  ERROR HIGHLIGHTS HERE
End Sub

The memberid is a number and the lot number is text. I'm using a combo box to find the member and their respective lot number (some members have more than one lot and assessment). My debugs print the correct information. I know my issue is with the quotes. TIA!

[ranman256]

use filter:

sWhere = "[memberid] = " & Combo138.Column(2) & " And [PrimaryLotNum] = '" & Combo138.Column(3) & "' and [PrimaryLotNum] ='" & Screen.ActiveControl & "'"
docmd.filter = sWhere
docmd.FilterOn = true

[NoellaG]

Hi, the rule is simple: the where clause is a string and has to be surrounded by quotes:
"MemberID = 1" Result: MemberID = 1

If you have a text argument, you have to send literal quotes in the string. 2 following quotes are translated in 1 literal quote
"MemberID = 1 and PrimaryLotNumber = ""ABC""" Result : MemberID = 1 and PrimaryLotNumber = "ABC"

If you want to use variables for the criteria you have to bring them out of the string and concatenate the partial strings
"MemberID = " & Combo138.Column(2) & " and PrimaryLotNumber = """ & Combo138.Column(3) & """"

[moke123]

try also adding something like

Dim strCriteria As String
strCriteria = "[memberid] = " & Combo138.Column(2) And "[PrimaryLotNum] = " & "'" & Combo138.Column(3) & "'"
Debug.Print strCriteria

This makes it much easier to confirm that your criteria is correct and the delimiters are in the right places.