Loop through Reports, Output as PDF (1 Viewer)

jeran042 · Apr 25, 2018

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

arnelgp · Apr 25, 2018

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

rpt.Filter = "[COST_CENTER] = """ & !txtCOST_CENTER & """"

arnelgp · Apr 25, 2018

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

jeran042 · Apr 25, 2018

arnelgp,

Thank you for the response,
The field [COST_CENTER] is formatted as a number

jeran042 · Apr 25, 2018

Is this something that can be handled with OpenArgs?

MarkK · Apr 25, 2018

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

arnelgp · Apr 26, 2018

You can replace this:

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

With:

strFilter = "[COST_CENTER] = " & !txtCOST_CENTER

jeran042 · Apr 26, 2018

MarkK said:
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?

MarkK · Apr 26, 2018

What is the RecordSource of the report? Does it leverage the TempVar that you set in code?
Mark

jeran042 · Apr 26, 2018

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?

MarkK · Apr 26, 2018

jeran042 said:
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

jeran042 · Apr 26, 2018

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

MarkK · Apr 26, 2018

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

jeran042 · Apr 27, 2018

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"

MarkK · Apr 27, 2018

After the line executes, the TempVar will hold the value of the field.

jeran042 · Apr 27, 2018

It isn't, its holding the "TempVars!COST_CENTER" (800)

And while its outputting, it is giving the name of a third cost_center. The Department name (my only guess as to why I would see this name) is the second department on the list

I only think its that is because in some other loops I use something like "rs.MoveFirst"

MarkK · Apr 27, 2018

jeran042 said:
It isn't, its holding the "TempVars!COST_CENTER" (800)

You are claiming that the assignment to a TempVar is failing in VBA. This is a significant claim, and one that I don't know how to replicate here. Can you please post a database that demonstrates this problem, because I have never seen anything like this happen before. Probably there is something else going on, but from your description I can't guess what it would be.
hth
Mark

jeran042 · Apr 27, 2018

Markk,

I'm not sure how I would post this DB without an enormous amount of sanitation.

I'm almost sure that something else (more than likely my own error) is going on, and I suppose my next move is to find out what that is before I ask you to put forth any more effort.

As it stands now, you have gotten me very close, I need to uncover what the disconnect is between the desired output and what I am actually getting,

Let me see what I can do,

Very much appreciated,

jeran042 · May 1, 2018

MarkK said:
You are claiming that the assignment to a TempVar is failing in VBA. This is a significant claim, and one that I don't know how to replicate here. Can you please post a database that demonstrates this problem, because I have never seen anything like this happen before. Probably there is something else going on, but from your description I can't guess what it would be.
hth
Mark

Markk,

I believe I know why I am not achieving the desired output. All of the formulas in the report all reference an unbound textbox called "txtCost_Center"

Example:

Code:

=Round(Nz(DSum("[DEBIT]","qryLedger_Detail","[COST_CENTER]=txtCOST_CENTER" & " And [CATEGORY]='TEMPORARY PERSONNEL'"),"$0"))-Round(Nz(DSum("[CREDIT]","qryLedger_Detail","[COST_CENTER]=txtCOST_CENTER" & " And [CATEGORY]='TEMPORARY PERSONNEL'"),"$0"))

So, that being said, is there a way to populate this textbox with the value of the TempVars?

EDIT:
I was able to pass the TempVars to the form by simply putting "=[TempVars]![COST_CENTER]" in the textbox "txtCost_Center"

Thank you for your help!!

MarkK · May 1, 2018

Nice job! Good luck with the rest of the project.

Loop through Reports, Output as PDF (1 Viewer)

Registered User.

..forever waiting... waiting for jellybean!

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

bit cruncher

..forever waiting... waiting for jellybean!

Registered User.

bit cruncher

Registered User.

bit cruncher

Registered User.

bit cruncher

Registered User.

bit cruncher

Registered User.

bit cruncher

Registered User.

Registered User.

bit cruncher

Similar threads

Users who are viewing this thread