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!