We help IT Professionals succeed at work.
Get Started

Report filter not changing when sub routine runs

GPSPOW asked
Last Modified: 2016-02-13
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")
    With MyRs
            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"
    End With
    rpt.Filter = " "
    rpt.FilterOn = False
End Sub

Open in new window

Watch Question
Principal Analyst
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE