Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-25-2018, 09:33 AM   #1
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 78
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Loop through Reports, Output as PDF

On the surface this seems easy enough, but here is my problem,

I have a report, that when opened from the "Home Form" (A form that is sort of like a switchboard) receives a value and the formulas on the report are updated

So in other words, you run the report, a variable is passed from the Home Screen and all the calculations on the report update. And this works fine.

Going one step further, I now was to loop through a query, open said report, and have it output a PDF for each department within qryDepartmentActive.

I am not quite sure how to do this as I am now not opening the report through the home screen, and therefore no variable is being passes

The report is unbound.
Here is what I have for code:

Code:
Private Sub Command110_Click()

    Dim MyRs As DAO.Recordset
    Dim rpt As Report
   
    Set MyRs = CurrentDb.OpenRecordset("qryDepartmentActive")
    DoCmd.OpenReport "RPT: TEST_042018", acPreview 'Preview just to ensure it is pulling in the [COST_CENTER]
    Set rpt = Reports("SELECT * FROM qryDepartmentActive")

    With MyRs
        .MoveFirst
            Do While Not .EOF
                ' open report hidden
                ' Output report as a PDF
                



                'This is what I need to adapt as the report is unbound, and not filtering. 
                '--------------------------------------------------
                rpt.Filter = "[COST_CENTER] = " & !txtCOST_CENTER
                rpt.FilterOn = True
                '--------------------------------------------------
                


                DoCmd.OpenReport "RPT: TEST_042018", acViewReport
                DoCmd.OutputTo acOutputReport, "RPT: TEST_042018", acFormatPDF
    
               .MoveNext
            Loop
    End With
End Sub
The field [COST_CENTER] is the department ID, and the "txtCOST_CENTER" is the box that is bound to the "Home Screen" that allows me to open the report and have it filtered.

In my mind I need to pass the field value of [COST_CENTER] from qryDepartmentActive to the report

jeran042 is offline   Reply With Quote
Old 04-25-2018, 09:47 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Loop through Reports, Output as PDF

If the field txtCost_Center is text you need to delimi it:

rpt.Filter = "[COST_CENTER] = """ & !txtCOST_CENTER & """"
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-25-2018, 10:04 AM   #3
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Loop through Reports, Output as PDF

Code:
Option Compare Database

Private Sub Command110_Click()

    Dim MyRs As DAO.Recordset
    Dim rpt As Report
    Dim strFilter As String
    
    Const REPORT_NAME As String = "RPT: TEST_042018"
    
    Set MyRs = CurrentDb.OpenRecordset("qryDepartmentActive")
    'DoCmd.OpenReport "RPT: TEST_042018", acPreview 'Preview just to ensure it is pulling in the [COST_CENTER]
    'Set rpt = Reports("RPT: TEST_042018")

    With MyRs
        .MoveFirst
            Do While Not .EOF
                ' open report hidden
                ' Output report as a PDF
                
                ' Build the Criteria
                strFilter = "[COST_CENTER] = """ & !txtCOST_CENTER & """"
                
                ' Open the report hidden
                DoCmd.OpenReport REPORT_NAME, acPreview, , strFilter, acHidden
                
                ' Create a pdf out of it
                DoCmd.OutputTo acOutputReport, REPORT_NAME, acFormatPDF

                ' close the report and re-open it with the new Criteria
                DoCmd.Close acReport, REPORT_NAME
                'This is what I need to adapt as the report is unbound, and not filtering.
                '--------------------------------------------------
                'rpt.Filter = "[COST_CENTER] = " & !txtCOST_CENTER
                'rpt.FilterOn = True
                '--------------------------------------------------
                


                'DoCmd.OpenReport "RPT: TEST_042018", acViewReport
    
               .MoveNext
            Loop
            .Close
    End With
    Set MyRs = Nothing
End Sub

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-25-2018, 10:57 AM   #4
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 78
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Loop through Reports, Output as PDF

arnelgp,

Thank you for the response,
The field [COST_CENTER] is formatted as a number
jeran042 is offline   Reply With Quote
Old 04-25-2018, 12:12 PM   #5
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 78
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Loop through Reports, Output as PDF

Is this something that can be handled with OpenArgs?
jeran042 is offline   Reply With Quote
Old 04-25-2018, 02:24 PM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
What I would do is base the report on a RecordSource that is filtered by a TempVar, something like...
Code:
SELECT * FROM YourTable WHERE CostCenter = TempVars!CostCenter
...and then all you need to do in the loop is set the TempVar, and output the report, which simplifies your code to something like...
Code:
Private Sub Command110_Click()
    Const RN As String = "RPT: TEST_042018"
    Const SQL As String = "SELECT CostCenter FROM qryDepartmentActive"
    
    With CurrentDb.OpenRecordset(SQL)
        Do While Not .EOF
            TempVars!CostCenter = .Fields(0)
            DoCmd.OutputTo acOutputReport, RN, acFormatPDF
            .MoveNext
        Loop
        .Close
    End With
End Sub
hth
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 04-25-2018, 05:48 PM   #7
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,732
Thanks: 55
Thanked 2,143 Times in 2,054 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Loop through Reports, Output as PDF

You can replace this:

strFilter = "[COST_CENTER] = """ & !txtCOST_CENTER & """"

With:

strFilter = "[COST_CENTER] = " & !txtCOST_CENTER

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-26-2018, 05:01 AM   #8
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 78
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Loop through Reports, Output as PDF

Quote:
Originally Posted by MarkK View Post
What I would do is base the report on a RecordSource that is filtered by a TempVar, something like...
Code:
SELECT * FROM YourTable WHERE CostCenter = TempVars!CostCenter
...and then all you need to do in the loop is set the TempVar, and output the report, which simplifies your code to something like...
Code:
Private Sub Command110_Click()
    Const RN As String = "RPT: TEST_042018"
    Const SQL As String = "SELECT CostCenter FROM qryDepartmentActive"
    
    With CurrentDb.OpenRecordset(SQL)
        Do While Not .EOF
            TempVars!CostCenter = .Fields(0)
            DoCmd.OutputTo acOutputReport, RN, acFormatPDF
            .MoveNext
        Loop
        .Close
    End With
End Sub
hth
Mark

Mark,

First off, thank you for your reply,

This is close
I had to add the ".value" to the line:
Code:
TempVars!COST_CENTER = .Fields(0)
as I was getting a Runtime error 32538

So, the code runs, and I do see the [COST_CENTER] being stored in the TempVars within the code, but I don't believe it is being passed to the report, as when I look at the PDF, the formulas show all "#Error" because there is no [COST_CENTER] to calculate off of

Any suggestions?
jeran042 is offline   Reply With Quote
Old 04-26-2018, 05:14 AM   #9
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
What is the RecordSource of the report? Does it leverage the TempVar that you set in code?
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 04-26-2018, 05:18 AM   #10
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 78
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Loop through Reports, Output as PDF

My record source is:

Code:
SELECT TBL_DEPARTMENT.COST_CENTER FROM TBL_DEPARTMENT WHERE COST_CENTER = TempVars!COST_CENTER;
In addition I notices that in the line:
Code:
TempVars!COST_CENTER = .Fields(0).Value
I see that the TempVars side has one [COST_CENTER] and the .Fields has another?
jeran042 is offline   Reply With Quote
Old 04-26-2018, 05:38 AM   #11
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Quote:
Originally Posted by jeran042 View Post
My record source is:

Code:
SELECT TBL_DEPARTMENT.COST_CENTER FROM TBL_DEPARTMENT WHERE COST_CENTER = TempVars!COST_CENTER;
That is the RecordSource of the report? It only has one field. I don't understand. In the recordset that is opened from that SQL, the data returned will be the same as the data in the TempVar, so I don't see the point of it.
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 04-26-2018, 06:01 AM   #12
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 78
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Loop through Reports, Output as PDF

I changed the SQL to match:
Code:
SELECT qryDepartmentActive.COST_CENTER FROM qryDepartmentActive
I only need the [COST_CENTER] as everything on the report is based on calculations
jeran042 is offline   Reply With Quote
Old 04-26-2018, 07:11 AM   #13
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
So just calculate it from the TempVar. That info is available, and updated in the loop for each execution of the report. To restate, if all you need in the report is the value of Cost_Center, and you set that value in the loop, here...
Code:
TempVars!CostCenter = .Fields(0).Value
...then when you execute the report, you don't even need the RecordSource. Just use the value in the TempVar.
Mark
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 04-27-2018, 06:17 AM   #14
jeran042
Newly Registered User
 
Join Date: Jun 2017
Posts: 78
Thanks: 20
Thanked 1 Time in 1 Post
jeran042 is on a distinguished road
Re: Loop through Reports, Output as PDF

The loop is running, however I am still having trouble with the line:
Code:
With CurrentDb.OpenRecordset(SQL)
        Do While Not .EOF
            TempVars!COST_CENTER = .Fields(0).Value
As "TempVars!COST_CENTER" is returning 1 cost_center and ".Fields(0).Value[/CODE]" is returning another.


So what I see on the "TempVars!COST_CENTER" is "800" and on the ".Fields(0).Value" side in "900"
jeran042 is offline   Reply With Quote
Old 04-27-2018, 06:27 AM   #15
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
After the line executes, the TempVar will hold the value of the field.

__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Reply

Tags
outputto pdf , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Duplicate Reports as an output ramez75 Reports 18 02-02-2016 04:37 AM
Loop through query results to output emails knarlyd@hotmail.com Modules & VBA 4 02-02-2015 01:24 PM
Output Reports Rich1968 Macros 1 07-01-2011 05:34 AM
Loop not giving expected output branston Modules & VBA 3 08-27-2009 06:20 AM
VBA Loop One Report - output and save many pdf's wstandis Modules & VBA 17 05-29-2009 07:07 AM




All times are GMT -8. The time now is 03:26 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World