Trying to duplicate a query in a recordset

[klnlsu]

I'm trying to duplicate a query in a recordset open by mimicking the SQL statement from the query (as follows):

SELECT tblSalesCustomerItemNumbers.CustNumber, tblSalesCustomerItemNumbers.CustAltAddrCode, tblSalesCustomerItemNumbers.BBItemNumber, tblSalesCustomerItemNumbers.CustItemNumber, tblSalesCustomerItemNumbers.ConversionDivisor, tblSalesCustomerItemNumbers.Notes, tblSalesCustomerItemNumbers.CustItemID
FROM tblSalesCustomerItemNumbers
WHERE (((tblSalesCustomerItemNumbers.CustNumber) Like "*" & [Forms]![frmCustomerItemFilter]![CustNumberCombo] & "*") AND ((tblSalesCustomerItemNumbers.CustAltAddrCode) Is Null) AND ((tblSalesCustomerItemNumbers.BBItemNumber) Like "*" & [Forms]![frmCustomerItemFilter]![BBItemCombo] & "*") AND ((tblSalesCustomerItemNumbers.CustItemNumber) Like "*" & [Forms]![frmCustomerItemFilter]![CustItemNumberTxt] & "*") AND (([Forms]![frmCustomerItemFilter]![CustAltAddrCombo]) Is Null)) OR (((tblSalesCustomerItemNumbers.CustNumber) Like "*" & [Forms]![frmCustomerItemFilter]![CustNumberCombo] & "*") AND ((tblSalesCustomerItemNumbers.CustAltAddrCode) Like "*" & [Forms]![frmCustomerItemFilter]![CustAltAddrCombo] & "*") AND ((tblSalesCustomerItemNumbers.BBItemNumber) Like "*" & [Forms]![frmCustomerItemFilter]![BBItemCombo] & "*") AND ((tblSalesCustomerItemNumbers.CustItemNumber) Like "*" & [Forms]![frmCustomerItemFilter]![CustItemNumberTxt] & "*"));


in the Recordset Open statement in my VBA code (as follows):

Dim Con1 As New ADODB.Connection, Con2 As New ADODB.Connection

Set Con1 = CurrentProject.Connection
Set Con2 = CurrentProject.Connection

Dim rs1 As New ADODB.Recordset, rs2 As New ADODB.Recordset
Dim MyStr, AstStr As String

AstStr = "'*'"
MyStr = "SELECT * FROM tblSalesCustomerItemNumbers " _
& "WHERE ((CustNumber Like ' & AstStr & [Forms]![frmCustomerItemFilter]![CustNumberCombo] & AstStr & ') " _
& "AND (CustAltAddrCode Is Null) " _
& "AND (BBItemNumber Like ' & AstStr & [Forms]![frmCustomerItemFilter]![BBItemCombo] & AstStr & ') " _
& "AND (CustItemNumber Like ' & AstStr & [Forms]![frmCustomerItemFilter]![CustItemNumberTxt] & AstStr & ') " _
& "AND (' & [Forms]![frmCustomerItemFilter]![CustAltAddrCombo] & ' Is Null)) " _
& "OR ((CustNumber Like ' & AstStr & [Forms]![frmCustomerItemFilter]![CustNumberCombo] & AstStr & ') " _
& "AND (CustAltAddrCode Like ' & AstStr & [Forms]![frmCustomerItemFilter]![CustAltAddrCombo] & AstStr & ') " _
& "AND (BBItemNumber Like ' & AstStr & [Forms]![frmCustomerItemFilter]![BBItemCombo] & AstStr & ') " _
& "AND (CustItemNumber Like ' & AstStr & [Forms]![frmCustomerItemFilter]![CustItemNumberTxt] & AstStr & '))"

rs1.Open MyStr, Con1, adOpenDynamic, adLockPessimistic
rs2.Open "tblSalesCustomerItemNumbers", Con2, adOpenDynamic, adLockPessimistic


I am using ADO recordsets instead of DAO because I use a lot of SQL files and I want my code to be consistent. The query uses fields from an open form for the criteria and I must use "LIKE" so that it picks up all records if the criteria is left empty. I am able to open the recordset, but I get a recordcount of -1.

Another thing -- I am using the same table in both of my recordsets. I don't know if this is a problem.

My first question: Is there an easier way to do this?
My second question: If this is the best way to do this, why am I getting a recordcount of -1?



Thanks in advance!

[pbaldy]

Try this debugging method. I think you'll be surprised:

BaldyWeb-Immediate window

[klnlsu]

Thank you for the debugging tip. I will use that in the future. I was able to complete my task with a simple append query, so I do not need to duplicate the query in a recordset. Whew!

[pbaldy]

No problem.