Run Same Report for multiple Depts (from Dept table)

[whmusser]

This may so elementary I should be embarrassed but I'm mostly an Excel guy, so...

My primary data table contains a monthly import of charges to be billed back to various Depts (80ish) in my Org. There is an established relationship between the Dept & Charges tables. I currently have a report that prompts for the Dept I'd like to report (the Query data source actually prompts I guess) and presents the resulting report in Print Preview. I then save as a PDF in a specific folder, named as <DeptNum>.pdf. This is then Shared from my Google Drive and sent as a link in a GMail mail merge (but that part is not really germane to this).

I currently run the report manually for each Dept, saving to the correct location and overwriting the previous same name file. A bit tedious and I'd like to create a Form/Button to execute a Macro or Event Proc to step through the Depts table and run/save/overwrite the report to my desired folder.



Any suggestions/examples??

[June7]

VBA code - pseudocode:

Open recordset of departments, loop through recordset, open report filtered by value from record, output report, close report, move to next record, repeat. Can even send email directly from Access.

Common topic. https://www.accessforums.net/import-...ges-25042.html

[whmusser]

Thanks for such a quick reply. Looks as though I'll need to learn my way through some VBA but that gives me some direction!! My mail merging is problematic, I think, as we're on Google not Exchange. I have that handled in Google Docs, just need to create the files to send.

[June7]

Might find this of interest https://www.accessforums.net/modules...vba-49948.html

[whmusser]

Mr Moderator, your pseudocode and links were valuable and have gotten me to where I am, partially successful and left with another question...

I have the following VBA code executing on the Click event of a single Button on a Form:
==========================

Option Compare Database


Private Sub Form_Current()


End Sub


Private Sub StartReports_Click()
Dim dbsCellCharges As DAO.Database
Dim rsetDepts As DAO.Recordset
Dim strWhere As String


Set dbsCellCharges = CurrentDb
Set rsetDepts = dbsCellCharges.OpenRecordset("Dept List")


While rsetDepts.EOF = False
' MsgBox (rsetDepts.Fields(1).Value)

deptnum = rsetDepts.Fields(1).Value

' Test output to Downloads folder
strWhere = "C:\\users\wmusser\Downloads\" & deptnum & ".pdf"


' Live output to Wireless folder
' strWhere = "C:\\users\wmusser\Google Drive\Wireless\" & deptnum & ".pdf"
' debug to show strWhere location
MsgBox (strWhere)


' Prior work to use OpenReport method with while parameter
' strWhere = "Cellular Bill Data.Dept ='" & deptnum & "' And Cellular Bill Data.Billing Cycle Date='" & "#4/1/2015#"
' MsgBox (strWhere)
' DoCmd.OpenReport "By Dept Bill Detail", acViewPreview, , strWhere

' Outputs report to strWhere location.
' Report has a query as it's data source and query is setup with a function() as criteria in Dept


DoCmd.OutputTo acOutputReport, "Auto By Dept Bill Detail", acFormatPDF, strWhere

' Comment out to use entire Dept List table
rsetDepts.MoveLast
rsetDepts.MoveNext

Wend


End Sub

=========================
Sorry for any crudeness to my very old coding skills..

This code executes the named Report and that report has a Query as it's data source. The criteria for the Dept in the Query is [Enter Dept] and that prompts me for the dept number in each iteration through the While loop. The resulting report is saved to the desired folder, named as the DeptNum.pdf, as I need. This does require me to enter 79 dept numbers (for now) each time I need to refresh my .pdf files (monthly).

I've tried to place a function into the query's Criteria, ">getDeptNum()" (w/o quotes) but can't figure out the syntax for that function or where to write it, in the same Event Proc as my existing On Click code? I've searched some examples but can't quite get it...

[June7]

Why would you use a function in dynamic parameter? What is the code? The function would have to be a general code module for the query to find it. I NEVER use dynamic parameter queries.

A VBA procedure can 'refresh' your PDF files. Requires opening and manipulating a recordset of department numbers. The posted procedure shows code to do that but most of it is commented out.

So why have dynamic parameter in query if you have code that can automate these 79 outputs?

[whmusser]

Short answer is because my Googling came across a thread (somewhere, not this site) that using a function in the criteria of query design was the only way to get a VBA argument into the Query. There were snippets of code but I couldn't seem to cut/paste/modify either in the right syntax or in the correct code module for it to work. The entry in the Query criteria row was simply described to be: >function().

Most of what is commented out is either prompts to show me what I was building or failed attempts to use the DoCmd.OpenReport. I've gotten closer with the DoCmd.OutputTo method but currently the Query prompts me to enter the Dept number when I'd just like to use the Dept number from the Dept List table.

The code I posted is all the code I have. It's the Event Proc of the single button on a single Form. The rest of what I'm doing is done using the Access interface. I have 2 tables (Dept List & Charges) and several reports using queries as their dataset (the queries do the filtering of data).

[June7]

Open recordset of department numbers, loop the recordset, reference the deptnum field as criteria for opening filtered report, output report to PDF, close report, move to next record, repeat.

Consider:

Private Sub StartReports_Click()
Dim rsetDepts As DAO.Recordset
Dim strPath As String, strWhere As String
Set rsetDepts = CurrentDb.OpenRecordset("SELECT DeptNum FROM [Dept List];")
While Not rsetDepts.EOF
    strPath = "C:\users\wmusser\Downloads\" & rsetDepts!deptnum & ".pdf"
    strWhere = "Dept='" & rsetDepts!deptnum & "' And [Billing Cycle Date]=#" & Me.tbxCycle & "#"
    DoCmd.OpenReport "By Dept Bill Detail", acViewPreview, , strWhere
    DoCmd.OutputTo , , acFormatPDF, strPath
    DoCmd.Close acReport, "By Dept Bill Detail"
    rsetDepts.MoveNext
Wend
End Sub

If deptnum is a number type field, remove the apostrophe delimiters.

BTW, recommend no spaces or special characters/punctuation (underscore is exception) in naming convention. Note the use of [].