VBA to loop through records in a table as parameter inputs for a few queries, save to

[manalyn85]

I am looking for the vba to have a query cycle through using records from another query as the parameter run and export for each of the records on the second query.

Table 1 has information with all clients
query 1 has x, y, z being pulled > client used as parameter
query 2 has the list of clients

Currently I have the coding to run the query and export however I am not sure how to get it to repeat for each client.

Code:

Private Sub CmdInternalReports_Click()
Dim xlApp As Excel.Application
    Dim rstDetails As DAO.Recordset
    Dim strTab As String
    Dim strDir As String
 
    Set xlApp = Nothing
    Set rstDetails = Nothing
 
    'Open and display the Network EMC Template
    Set xlApp = New Excel.Application
    'xlApp.Visible = True
    xlApp.Workbooks.Open (SavePath & "Templates\Network EMC Report XCOL Template.xlsx")
 
    'Copy the Order has been scheduled delivered mmddyyyy
    strTab = "EMC"
    Set rstDetails = CurrentDb.OpenRecordset("qryXCLONetworkEMCSchedDelv")
    xlApp.Worksheets(strTab).Select
    xlApp.Worksheets(strTab).Cells(3, 1).CopyFromRecordset rstDetails
    xlApp.Worksheets(strTab).Cells.Select
    xlApp.Worksheets(strTab).Cells.EntireColumn.AutoFit
    xlApp.Worksheets(strTab).Cells(1, 1).Select
 
 
'    xlApp.ActiveWorkbook.SaveAs strDir & "\Order has been scheduled delivered mmddyyyy.xls"
'    SetAttr strDir & "\Order has been scheduled delivered mmddyyyy.xls", vbReadOnly
 
    xlApp.Worksheets(1).Select
 
    xlApp.ActiveWorkbook.SaveAs "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy")
    SetAttr "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy") & ".xlsx", vbReadOnly
    'Close Excel
    xlApp.ActiveWorkbook.Close
    xlApp.Quit
 
    MsgBox "Done!"
End Sub

The coding above runs two different queries and saves them to two seperate tabs on the pre-made template for this report.



Below is the SQL coding I currently have for the first query:

Code:

SELECT 
tblImportNetworkEMC.DeliveryHub, 
tblImportNetworkEMC.HubType, 
tblImportNetworkEMC.Origin, 
tblImportNetworkEMC.Client, 
tblImportNetworkEMC.CurrentLoc, 
tblImportNetworkEMC.Consignee, 
tblImportNetworkEMC.Shipment, 
tblImportNetworkEMC.Order, 
tblImportNetworkEMC.WaitingFor, 
tblImportNetworkEMC.TotalDaysAged, 
tblImportNetworkEMC.IntLastDate, 
tblImportNetworkEMC.IntEnterBy, 
tblImportNetworkEMC.IntLastType, 
tblImportNetworkEMC.InternalAge, 
tblImportNetworkEMC.IntCommentText, 
qryClientNetworkEMC_FilterCHAD.Shipment
 
FROM 
  ((tblImportNetworkEMC 
 LEFT JOIN 
   qryClientNetworkEMC_Filter ON tblImportNetworkEMC.Shipment = qryClientNetworkEMC_Filter.Shipment) 
 LEFT JOIN 
   qryClientNetworkEMC_FilterCHAD ON tblImportNetworkEMC.Shipment = qryClientNetworkEMC_FilterCHAD.Shipment) 
 INNER JOIN 
   tblClientReport ON tblImportNetworkEMC.Client = tblClientReport.Client
 
WHERE (((tblImportNetworkEMC.WaitingFor)="Client") 
  AND ((tblImportNetworkEMC.IntLastType)<>"XCLO") 
  AND ((qryClientNetworkEMC_FilterCHAD.Shipment) Is Null) 
  AND ((tblImportNetworkEMC.Status)="Active Delivery" 
        Or (tblImportNetworkEMC.Status)="Active Closed Delivery") 
  AND ((qryClientNetworkEMC_Filter.Shipment) Is Null));
 

I would like to figure out how cycle through the records in a separate query

qryClient-NetworkEMCQuery.Client

as the parameter for

tblImportNetworkEMC.Client

There can be upto 70 different clients or as little as 2 depending on the day. So the Query I have takes a list of the clients that receive the report and runs it against the imported table to only give the ones with data day.

tblClientReport.Distro
Is where I have the report via client name linked the the distro list that should be pulled.

Any advise for how to look this up would be much appreciated.