Results 1 to 4 of 4
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

    Trying to duplicate a query in a recordset

    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!
    Last edited by klnlsu; 02-11-2016 at 11:48 AM. Reason: Left out another factor

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try this debugging method. I think you'll be surprised:

    BaldyWeb-Immediate window
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    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!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  2. db recordset query help
    By elios115 in forum Programming
    Replies: 3
    Last Post: 08-06-2013, 02:41 AM
  3. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  4. Replies: 1
    Last Post: 11-13-2009, 03:03 AM
  5. Add new Recordset using Recorset by Query
    By gailoni in forum Programming
    Replies: 1
    Last Post: 10-22-2008, 11:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums