I am using some code I found online that runs a report and saves the reports to a PDF. The code works with no errors but it is producing only one page per pdf when it should be multiple pages.
My data has 25 records in it. The field DirectReportsTo consist of 5 unique people and each of those has on average 5 people associated with them. If I run the report on it's own it prints in order by the 5 unique people and prints all pages. When I run the code I get a PDF for each of the 5 people as I desire but within those PDF's I want to see multiple pages yet I only see 1 page per pdf.
Example.
DirectReports Psoft_ID John Smith 123442 John Smith 125478 John Smith 125477 Jim Scott 458745 Jim Scott 458999 Jim Scott 788956
I want to see one pdf for John Smith and in it is a report for his 3 people and one pdf for Jim Scott and his 3 people in it. The code below gives me one pdf for John Smith with only one person in it and one pdf for Jim Scott with one person in it.
The report is grouped by DirectReportTo and is sorted in PSoft_ID order. I have tried everything I can to change the code with no luck. I'm a novice with vba so appreciate any help.
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim strPath As String
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select DISTINCT Psoft_ID, DirectReportsTo from [qryRPT_1PGR_distribution_EVPs]", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
DoCmd.OpenReport "rpt1PAGER_Final_BonusOnly_Distribution", acViewPreview, , "Psoft_ID= " & rst!PSoft_ID
strPath = "C:\PDFs\1Pgrs" & rst!DirectReportsTo & ""
Call MakeSureDirectoryPathExists(strPath)
DoCmd.OutputTo acOutputReport, "rpt1PAGER_Final_BonusOnly_Distribution", acFormatPDF, strPath & rst!DirectReportsTo & ".pdf", False
DoCmd.Close acReport, "rpt1PAGER_Final_BonusOnly_Distribution"
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
strSQL = ""
End Sub
Would be a great Mother's Day gift to be able to get this code working so at work tomorrow I will be able to have this task complete.
Thanks in advance for your help.