Run and Append Query for each record in a recordset using fields in record set as parameters for each query

Question

Private Sub btnMakeECR_Click()
 Me.OrderDate = Date

 Dim rs As DAO.Recordset
 Dim db As DAO.Database
 Set db = CurrentDb
 Set rs = db.OpenRecordset("SELECT tblOrderDetails.Quantity, 
          tblOrderDetails.WeaponID " & vbCrLf & _
          "FROM tblOrderDetails")
 Dim qdf As DAO.QueryDef
 Dim strSQL As String
 Dim qrtAMT As Integer


qryAMT = DCount("[tblOrderDetails.OrderID]", "tblOrderDetails", "
         [tblOrderDetails.OrderID] = [Forms]![frmOrderEntry]![OrderID]")


On Error Resume Next
DoCmd.DeleteObject acQuery, "testQry"
On Error GoTo 0

strSQL = "SELECT TOP " & qryAMT & " tblOrder.OrderID, 
          tblOrderDetails.OrderID, tblOrder.ArmorerID, tblOrder.RecieverID, 
          tblOrder.OrderDate, tblOrder.RecieverNumber, tblOrder.UnitName, 
          tblOrder.PickUpDate, tblOrder.Returned, tblOrder.Cleaned, 
          tblOrder.Notes, tblOrder.DateReturned, tblOrder.DateCleaned, 
          tblOrderDetails.OrderDetailID, tblWeaponGroups.WeaponID, 
          tblWeapons.InventoryID, tblWeapons.SerialNumber, 
          tblOrderDetails.WeaponID, tblWeapons.StockNumber, 
          tblWeapons.IssueCount, tblOrderDetails.Quantity, tblWeapons.Status 
          " & vbCrLf & _
          "FROM (tblWeaponGroups INNER JOIN tblWeapons ON 
          tblWeaponGroups.WeaponID = tblWeapons.WeaponID) INNER JOIN 
          (tblOrder INNER JOIN tblOrderDetails ON tblOrder.OrderID = 
          tblOrderDetails.OrderID) ON tblWeaponGroups.WeaponID = 
          tblOrderDetails.WeaponID " & vbCrLf & _
          "WHERE (((tblWeapons.Status)=""AVAILABLE"")) " & vbCrLf & _
          "ORDER BY tblOrderDetails.WeaponID, tblWeapons.StockNumber, 
          tblWeapons.IssueCount, tblOrderDetails.Quantity;"

Set qdf = db.CreateQueryDef("testQry", strSQL)

DoCmd.OpenQuery ("testQry")

End Sub

This is my recordset and the query I want to run. I want to run the query for each record in the recordset using fields in the recordset as parameters in the query. I want to append all queries. The desired result would be to return a table or query with a number of sets of records equal to the number of records in the recordset an each set of records would contain an amount of records equal to its corresponding value in the record set

like this

RECORDSET         QUERY                      END RESULT OF QUERY
A 2           A AFGHANISTAN                   2 A AFGHANISTAN 
B 1           A ALBANIA                       2 A ALBANIA
C 4           A ALGERIA                       1 B BAHRAIN 
D 1           B BAHRAIN                       4 C CAMBODIA 
              B BARBADOS                      4 C CANADA
              B CAMBODIA                      4 C CHILE
              C CANADA                        4 C CHINA
              C CHILE                         1 D DENMARK
              C CHINA
              C CUBA
              D DENMARK
              D DOMINICA

I am using this for orders all my products are in the same table and are identified by Type, serial number and location.

ignore qryAMT, i was just testing the Dcount function and varying the results of my query with a defined integer. I am going to substitute the value of qryAMT with another expression something like (quantity field for table when dcount-dcount+loop step=loopstep)

Oba Mandley · Accepted Answer · 2018-07-23 03:43:10Z

 Private Sub Command373_Click()
 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 Dim strQNT As String
 Dim srtQNTV As Integer
 Dim qdf As DAO.QueryDef
 Dim strSQL As String
 Dim strWID As Integer

 strQNT = "Quantity"


 Set db = CurrentDb
 Set rs = db.OpenRecordset("tblOrderDetails", dbOpenForwardOnly)

 DoCmd.OpenTable "tblECR", acViewNormal

On Error Resume Next
DoCmd.DeleteObject acTable, "tblECR"
On Error GoTo 0

On Error Resume Next
DoCmd.DeleteObject acQuery, "testQryA"
On Error GoTo 0
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblECR"

Do While Not rs.EOF
Debug.Print rs![Quantity]
Debug.Print rs![WeaponID]
strQNTV = rs!Quantity.Value
strWID = rs!WeaponID.Value

strSQL = "INSERT INTO tblECR ( OrderDetailID, OrderID, Quantity, ArmorerID, 
         RecieverID, RecieverNumber, UnitName, PickUpDate, SerialNumber, 
         Nomenclature, IssueCount, WeaponID, StockNumber, Status ) " & vbCrLf & _
        "SELECT TOP " & strQNTV & " tblOrderDetails.OrderDetailID, tblOrderDetails.OrderID, tblOrderDetails.Quantity, tblOrder.ArmorerID, tblOrder.RecieverID, tblOrder.RecieverNumber, tblOrder.UnitName, tblOrder.PickUpDate, tblWeapons.SerialNumber, tblWeapons.Nomenclature, tblWeapons.IssueCount, tblWeapons.WeaponID, tblWeapons.StockNumber, tblWeapons.Status " & vbCrLf & _
        "FROM tblOrder INNER JOIN (tblOrderDetails INNER JOIN tblWeapons ON tblOrderDetails.WeaponID = tblWeapons.WeaponID) ON tblOrder.OrderID = tblOrderDetails.OrderID " & vbCrLf & _
        "WHERE (((tblOrderDetails.OrderID)=[Forms]![frmOrderEntry]![OrderID]) AND ((tblWeapons.WeaponID)= " & strWID & ") AND ((tblWeapons.Status)=""AVAILABLE"")) " & vbCrLf & _
        "ORDER BY tblWeapons.IssueCount, tblWeapons.WeaponID, tblWeapons.StockNumber;"


 On Error Resume Next
 DoCmd.DeleteObject acQuery, "testQryA"
 On Error GoTo 0

 Set qdf = db.CreateQueryDef("testQryA", strSQL)
 DoCmd.OpenQuery ("testQryA")
 rs.MoveNext

 Loop
 rs.Close
  DoCmd.SetWarnings True



 End Sub

So there it is. It works. There is some garbage code in there but it works. Thank you all for you help.

Collectives™ on Stack Overflow

Run and Append Query for each record in a recordset using fields in record set as parameters for each query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related