asked on

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

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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

"[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

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

@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 ...

GPSPOW

ASKER

Dale,

I am getting an error:

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

Glen

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

Glen,

In Da;es code change

 set rpt = Reports("rpt_Active_Employee_By_Department"

Open in new window

 set rpt = Reports("rpt_Active_Employee_By_Department")

Open in new window

GPSPOW

ASKER

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

SOLUTION

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

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?

GPSPOW

ASKER

Supervisor was a text field. When I added the chr(34) on both sides of the Supervisor it worked fine.

Thanks

Thanks

Glen

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

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.

GPSPOW

ASKER

Thanks to Dale and Boyd.

Glen

Dale Fye

Glad to help.