Save report to file for each in list

[DavidCB]

Greetings,

Needed to be pointed in the right direction.

I have two tables, StoreListTBL (StoreID [Primary], SiteName, Address, State) and EmpListTBL (EmpNumb [Primary], StoreID, Fname, Lname, ).

Trying to automated a process that saves a PDF report to file for each Store listing the assigned employees.

The Reports are generated monthly and saved as StoreID+Date (EmpListTX001-01172022) and Saved to a Folder based on State (Z:\Texas\EmpListTX001-01172022)

I know there are a few ways to skin a cat, I have a form that shows the required data based on a query [StoreID], what is the best way to get this to a report format?

Do I even need to do this from the Form?

A million Thank you's in advance,

DavidCB

[ranman256]

something like:

the form (fMyForm) has a listbox (lstClients) of all clients
the code will cycle thru a listbox , get next name, , creating the pdf file
the query (in the report) uses the listbox to pull only that person selected in the list.
i.e.: select * from table where [ClientID]=forms!fMyForm!lstClients

the folder, vLoc , is also in the listbox in another column. col 3, but in vb code columns start with zero

sub btnGo_click()
Dim i As Integer
dim vItm, vSendTo

vDir = "c:\temp\"

For i = 0 To lstClients.ListCount - 1
   vItm= lstClients.ItemData(i)  'get next item in list data
   lstClients =vItm      'set listbox to that item

   vSendTo = lstClients.column(1)  'get clients email addr. (note in VB, columns begin with zero)
vLoc  = lstClients.column(2)   'col 3 has folder name, but in vb code, columns begin with zero, so col3 =  lstClients.column(2)  

   vFilename = "rInvoice_" & vLoc & ".pdf"
   vFile = vDir & vFileName

 'create invoice data file
   docmd.OutputTo acOutputReport ,"rInvoice",acFormatPDF,vFile
      'or
   docmd.OutputTo acOutputQuery ,"qsMyQuery",acFormatPDF,vFile

    'email invoice directly
   DoCmd.SendObject acSendReport, "rInvoice", acFormatPDF, vSendTo, , , "Subject", "message"
Next
end sub

[DavidCB]

OK, think I got it, will run through this,

Thanks

DavidCB

[Welshgasman]

Main report for store.
Subreport for employees.
Use your query as a recordset for each store and output report to pdf.
Attach pdf file to email.