Attached is the module I am running to loop through a report to run selective reports based on a query. The report has a group equal to the Sueprvisor in the main query. The controlling query has 10 records with just the Supervisor, a character field.
When I step through the module, the report outputs to the separate pdf files as I expected. However, when I run the module, the data is only for the last Supervisor processed. The file name changes as expected, but not the data.
I would appreciate any help in correcting this.
Public Sub Supervisor()
Dim MyRs As DAO.Recordset
Dim rpt As Report
Set MyRs = CurrentDb.OpenRecordset("qry_Active_Employee_Supv")
DoCmd.OpenReport "rpt_Active_Employees_By_Department", acPreview, , , acHidden
Set rpt = Reports("rpt_Active_Employees_By_Department")
Do While Not .EOF
' open report hideen and filtered
' save the hidden report as a PDF
rpt.Filter = "[Supervisor] = " & Chr(34) & !Supervisor & Chr(34)
rpt.FilterOn = True
DoCmd.OutputTo acOutputReport, "rpt_Active_Employees_By_Department", acFormatPDF, "\\pmcfs\groups\HR\HR Extractions\ActiveEmployees\Active_Empl_List_" & !Supervisor & ".PDF"
rpt.Filter = " "
rpt.FilterOn = False