Filtering OpenRecordset Form reference error

[CaptainKen]

I'm attempting to populate a textbox with a field from a query based on where the ContactID matches the ContactID of a specific open form and where another field value in the query (called Default) is True. I'm using the .Filter of the OpenRecordset.

I'm getting the error: "3061: Too few parameters. Expected 3."

As you can see by all the commented out code, I've tried many ways to get it to work. Based on the MsgBox results the filter looks correct.

So for instance:
This works
rs.Filter = "ContactName = 'Cory Goulden'" & " AND Default=True" 'WORKS

This does not:


rs.Filter = "ContactID = [Forms]![frmCompanyContacts]![txtContactID] AND Default=True"

        Dim db As Database
        Dim rs As Recordset
        Dim rsFiltered As Recordset
        Dim strDefaultAddr As String
        
        strDefaultAddr = "SELECT DISTINCTROW [tblClients/Prospects].CompName, tblCompanyContacts.ContactName, tblContactAddresses.Attn, IIf(IsNull([Addr2]),[Addr1],[Addr1] & ', ' & [Addr2]) AS Street, tblContactAddresses.Zip, tblContactAddresses.City, tblContactAddresses.State, tblContactAddresses.Default " & _
                        "FROM ([tblClients/Prospects] INNER JOIN tblCompanyContacts ON [tblClients/Prospects].CompID = tblCompanyContacts.CompID) INNER JOIN tblContactAddresses ON tblCompanyContacts.ContactID = tblContactAddresses.ContactID " & _
                        "ORDER BY [tblClients/Prospects].CompName, tblCompanyContacts.ContactName;"
        
        Dim strWhere As String
        strWhere = "[Forms]![frmCompanyContacts]![txtContactID]"
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strDefaultAddr, dbOpenDynaset)
            rs.MoveLast
            MsgBox [Forms]![frmCompanyContacts]![txtContactID] & ", " & [Forms]![frmCompanyContacts]![txtContactName] & ", " & Default
            'rs.Filter = "ContactName = 'Cory Goulden'" & " AND Default=True"    'WORKS
            'rs.Filter = "ContactID = '" & strWhere & "'"
            'rs.Filter = "ContactID = " & strWhere
            'rs.Filter = "ContactID = '" & [Forms]![frmCompanyContacts]![txtContactID] & "'"
            rs.Filter = "ContactID = [Forms]![frmCompanyContacts]![txtContactID]"
            'rs.Filter = "ContactID = [Forms]![frmCompanyContacts]![txtContactID]" & " AND Default=True"
            'rs.Filter = "ContactID = '[Forms]![frmCompanyContacts]![txtContactID]'" & " AND Default=True"
            'rs.Filter = "ContactID = [Forms]![frmCompanyContacts]![txtContactID] AND Default=True"
            'MsgBox rs.Filter
        Set rsFiltered = rs.OpenRecordset
            rsFiltered.MoveLast
                MsgBox rsFiltered.RecordCount
                Me.txtAddressInfo = rsFiltered("Street")
                'Me.txtAddressInfo = rs("Street") & ", " & rs("City") & ", " & rs("State") & ", " & rs("Zip")
            rsFiltered.MoveNext
            rs.Close
            rsFiltered.Close
            db.Close
        Set rs = Nothing: Set rsFiltered = Nothing: Set db = Nothing

[orange]

Try (assuming Id is a number)
rs.Filter = "ContactID = " & [Forms]![frmCompanyContacts]![txtContactID] & " AND Default=True"

or if ContactId is text

rs.Filter = "ContactID = '" & [Forms]![frmCompanyContacts]![txtContactID] & "' AND Default=True"

[CaptainKen]

Thanks for replying with a possible solution.

I've tried both your recommendations and now I'm getting:
"3061: Too few parameters. Expected 2."

So it changed from expected 3 to expected 2.

BTW, ContactID referenced in the table is an Auto Number Long Integer.

[CaptainKen]

Well the main problem was that I hadn't included the ContactID field in the main query! Once I did that I was able to figure out the correct syntax to reference the textbox holding the ID. Here's the final code in case it helps someone else.

        Dim db As Database
        Dim rs As Recordset
        Dim rsFiltered As Recordset
        Dim strSQL As String
        Dim strAddr As String
        
        'This one assembles the Addr1 and Addr2 in the query
        'strSQL = "SELECT DISTINCTROW [tblClients/Prospects].CompName, tblCompanyContacts.ContactName, tblCompanyContacts.ContactID, tblContactAddresses.Attn, IIf(IsNull([Addr2]),[Addr1],[Addr1] & ', ' & [Addr2]) AS Street, tblContactAddresses.Zip, tblContactAddresses.City, tblContactAddresses.State, tblContactAddresses.Default " & _
                    "FROM ([tblClients/Prospects] INNER JOIN tblCompanyContacts ON [tblClients/Prospects].CompID = tblCompanyContacts.CompID) INNER JOIN tblContactAddresses ON tblCompanyContacts.ContactID = tblContactAddresses.ContactID " & _
                    "ORDER BY [tblClients/Prospects].CompName, tblCompanyContacts.ContactName;"
        
        ' This one assembles the Addr1 and Addr2 later on
        strSQL = "SELECT DISTINCTROW [tblClients/Prospects].CompName, tblCompanyContacts.ContactName, tblCompanyContacts.ContactID, tblContactAddresses.Attn, tblContactAddresses.Addr1, tblContactAddresses.Addr2, tblContactAddresses.Zip, tblContactAddresses.City, tblContactAddresses.State, tblContactAddresses.Default " & _
                    "FROM ([tblClients/Prospects] INNER JOIN tblCompanyContacts ON [tblClients/Prospects].CompID = tblCompanyContacts.CompID) INNER JOIN tblContactAddresses ON tblCompanyContacts.ContactID = tblContactAddresses.ContactID " & _
                    "ORDER BY [tblClients/Prospects].CompName, tblCompanyContacts.ContactName;"

        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
            rs.MoveLast
            rs.Filter = "ContactID = " & [Forms]![frmCompanyContacts]![txtContactID] & " AND Default=True"
        
        Set rsFiltered = rs.OpenRecordset
            rsFiltered.MoveLast
            
                If rsFiltered("Addr2") > "" Then
                    strAddr = rsFiltered("Addr1") & ", " & rsFiltered("Addr2") & ", " & rsFiltered("City") & ", " & rsFiltered("State") & ", " & rsFiltered("Zip")
                Else
                    strAddr = rsFiltered("Addr1") & ", " & rsFiltered("City") & ", " & rsFiltered("State") & ", " & rsFiltered("Zip")
                End If
        
                Me.txtAddressInfo = strAddr
            
            rsFiltered.MoveNext
            rs.Close
            rsFiltered.Close
            db.Close
        Set rs = Nothing: Set rsFiltered = Nothing: Set db = Nothing