jeran042
Registered User.
- Local time
- Yesterday, 18:45
- Joined
- Jun 26, 2017
- Messages
- 127
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:
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
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