CODEWITHSUNDEEP
ms-accessvba
icouper
icouper

Reputation: 59

Dynamically combine multiple reports using Sub-Reports - MS Access

I'm attempting to create a master report that I can use to combine multiple other reports into one. Ideally with the end result being a single PDF output.

I've been attempting to use a table to store the names of the reports I would like to populate into the master report and use those to define the SourceObject for the sub reports on load.

Ideally would like the option to have a variable number of sub-reports, so I've been attempting to use the Grouping function to accomplish that. So far the only result I'm getting is having the same report in all groups. I've tried putting the following code into OnLoad and OnCurrent - where txtPageReport is a textbox that is storing the report name.

subReportName = Me.txtPageReport
Me.subReport.SourceObject = subReportName

Any suggestions would be appreciated. Maybe I'm going about this the wrong way completely.

Thanks!

Upvotes: 2

Views: 9764

Answers (1)

Parfait
Parfait

Reputation: 107767

Consider the following two options which does not require table storage of subreports to be called dynamically by master report.

Grouped Report

Insert all subreports into the Group Header section (zero-length Detail section). Then, set each subreport Format property to Can Shrink = Yes and have report page break by Employee Group using Force New Page = Before Section. You can now open report in Print Preview and save as PDF using option on Ribbon.

MS Access Report with Grouping

Non-Grouped Report

Insert all subreports into Detail section (no grouping). Then, dynamically filter report using DoCmd.OpenReport method. Use Where Condition argument to filter by EmployeeID.

MS Access Report without Grouping

Below is a VBA loop to iterate through all employees and then output each individual employee report to PDF. Code can be used for either above grouped or non-grouped report.

Dim db as Database
Dim rst as Recordset
Dim strFile As String    

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT EmployeeID FROM Employees", dbOpenDynaset)

Do While Not rst.EOF
    ' OPEN FILTERED REPORT IN PRINT PREVIEW '
    DoCmd.OpenReport "ReportName", acViewPreview, , "EmployeeID = " & rst!EmployeeID

    ' OUTPUT REPORT INTO PDF '
    strFile = "C:\Path\To\Output\Folder\Employee_" & rst!EmployeeID & ".pdf"
    DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, strFile, False

    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing

Upvotes: 1

Related Questions