Looping through a recordset to output Ms-Access report to a pdf file

I creating a VBA sub routine to output a report to PDF to each of the main supervisors.  There are 10 supervisors in a query "qrySupv" which I want to use as a recordset.  The query has one field "Supervisor" with 10 records.

The report is using the following query to create the data:

SELECT dbo_vw_ActiveEmployeeList.Supervisor, dbo_vw_ActiveEmployeeList.DeptID, dbo_vw_ActiveEmployeeList.DeptName, dbo_vw_ActiveEmployeeList.JobCodeID, dbo_vw_ActiveEmployeeList.PositName, dbo_vw_ActiveEmployeeList.LastName, dbo_vw_ActiveEmployeeList.FirstName, dbo_vw_ActiveEmployeeList.EmployeeID, dbo_vw_ActiveEmployeeList.EmpType, dbo_vw_ActiveEmployeeList.BaseRate, dbo_vw_ActiveEmployeeList.DOB
FROM dbo_vw_ActiveEmployeeList
ORDER BY dbo_vw_ActiveEmployeeList.Supervisor, dbo_vw_ActiveEmployeeList.DeptID, dbo_vw_ActiveEmployeeList.JobCodeID, dbo_vw_ActiveEmployeeList.LastName;

Open in new window


Here is what I have so far for the sub Routine:

Public Sub Supervisor()
    Dim MyDb As Database
    Dim MyRs As DAO.Recordset
   
    Set MyDb = Database
    Set MyRs = MyRs.OpenRecordset("qry_Active_Employee_Supv")
   
    With MyRs
        .MoveFirst
            Do While Not MyRs.EOF
                DoCmd.OutputTo , acOutputReport, "rpt_Active_Employee_By_Department", acFormatPDF, , "Supervisor = " & MyRs.Supervisor.Value
                .MoveNext
            Loop
    End With
End Sub

If remember I need to use a QueryDef but I am not sure how to do this.


Thanks for any help with this.

Glen
GPSPOWAsked:
Who is Participating?
 
Dale FyeCommented:
Personally, I prefer to open the report once, then filter it inside the loop.

Public Sub Supervisor()
    Dim MyRs As DAO.Recordset
    Dim rpt as Report
   
    Set MyRs = CurrentDB.OpenRecordset("qry_Active_Employee_Supv")
    DoCmd.OpenReport "rpt_Active_Employee_By_Department", acPreview, , , acHidden
    set rpt = Reports("rpt_Active_Employee_By_Department"
   
    With MyRs
        .MoveFirst
            Do While Not .EOF
                ' open report hideen and filtered
                ' save the hidden report as a PDF
                 rpt.Filter = "[Supervisor] = " & !Supervisor
                 rpt.FilterOn = true
                DoCmd.OutputTo acOutputReport, "rpt_Active_Employee_By_Department", acFormatPDF
    
               .MoveNext
            Loop
    End With
End Sub

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You should not need a QueryDef .

The issue I see is how you are referencing a field in the recordset


Try changing

"Supervisor = " & MyRs.Supervisor.Value

Open in new window


to

"[Supervisor] = " & !Supervisor

Open in new window


if Supervisor is nor a numeric data type  then use:

"[Supervisor] = " & Chr(34)  & !Supervisor & Chr(34)

Open in new window

0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
TIP: Use the code tags

The Docmd.Output to does not support  filtering in the way your are trying.

You are not using the With MyRs consistently.

Here is the code assuming Supervisor  is a numeric data type:
Public Sub Supervisor()
    Dim MyRs As DAO.Recordset
   
    Set MyRs = CurrentDB.OpenRecordset("qry_Active_Employee_Supv")
   
    With MyRs
        .MoveFirst
            Do While Not .EOF
                ' open report hideen and filtered
                DoCmd.OpenReport "rpt_Active_Employee_By_Department", acPreview, , "[Supervisor] = " & !Supervisor, acHidden
                ' save the hidden report as a PDF
                DoCmd.OutputTo acOutputReport, "rpt_Active_Employee_By_Department", acFormatPDF
    
               .MoveNext
            Loop
    End With
End Sub

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
@Dale,

My code also only opens the report once and reapplies the filter because i never close the report.

Calling DoCmd.OpenReport with the same report name of a report that is already opened also applies the new filter to the opened report.

I am curios to see which method executes faster .and uses less resources. Goggles on and off to the testing lab ...
0
 
GPSPOWAuthor Commented:
Dale,

I am getting an error:

Extra ) in query expression '([Supervisor] = )'

Glen
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Glen,

In Da;es code change

 set rpt = Reports("rpt_Active_Employee_By_Department"

Open in new window


to

 set rpt = Reports("rpt_Active_Employee_By_Department")

Open in new window

0
 
GPSPOWAuthor Commented:
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
        .MoveFirst
            Do While Not .EOF
                ' open report hideen and filtered
                ' save the hidden report as a PDF
                 rpt.Filter = "[Supervisor] = " & !Supervisor
                 rpt.FilterOn = True
                DoCmd.OutputTo acOutputReport, "rpt_Active_Employees_By_Department", acFormatPDF
    
               .MoveNext
            Loop
    End With
End Sub

Open in new window


I figured out the missing ")" earlier.

Here's the code I have that gets the error at the DoCmd.OutputTo line.

Thanks

Glen
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Glen,

Is the DoCmd.Output to getting the error Extra ) in query expression '([Supervisor] = )'

Where do you want to save the PDF?

Here is the generic code I use a lot
stDocName =  "rpt_Active_Employees_By_Department" 
strPDFPath = "name.pdf"

DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strPDFPath

Open in new window




Try this:

DoCmd.OutputTo acOutputReport, "rpt_Active_Employees_By_Department", acFormatPDF, "Supervisor" & !Supervisor & ".pdf"

Open in new window

0
 
Dale FyeCommented:
Boyd,

I missed that you never closed the report.  But doesn't calling the OpenReport method again cause the report RecordSource to get re-queried, as well as the WHERE clause being executed?  I don't think my technique will re-query the report.

GPSPOW,

Have you resolved the error?  Based on the error message, I'm thinking that you have a NULL record in your Recordset.  I'd run your query ("qry_Active_Employee_Supv") and make sure that the criteria excludes NULLs from the result set.

Is the [Supervisor] field numeric or text?
0
 
GPSPOWAuthor Commented:
Supervisor was a text field.  When I added the chr(34) on both sides of the Supervisor it worked fine.

Thanks

Thanks

Glen
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Glen,

My suspicion was that the Supervisor was a text field, That is why suggest the Chr(34) in the original reply.


Glad to hear you have it working.
0
 
GPSPOWAuthor Commented:
Thanks to Dale and Boyd.

Glen
0
 
Dale FyeCommented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.