Access 2007 Report to PDF files, looping through report query source

[TomYC]

I have been reading the threads about sending individual reports as emails, but I want something simpler: to loop through all the records in the report's source query, and create individual pdf files (named from two fields in that query).
my report is rptBudgetCombined, its source query is qryBudgetNew, containing among other fields "Department" and "Dept ID" which means I want a file name of stDepartment & stDeptID & ".pdf" or something close to that, output to a path stMyPath.
The reason I need this is that when I attempt to open the report with several dozen records, I get a 'system resources exceeded' error; in any case, for distribution it would be better to have each department record become its own file.
This is Access 2007, and I understand that outputting to PDFs directly is now possible, right?
Many thanks,
T.Y.

 

[MajP]

Public Sub ExportToPDF()
Const strDir = "C:\"
Const qryName = "qryBudgetNew"
Const PK = "departmentID"
Const rptName = "rptBudgetCombined"
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(qryName)
Do While Not rs.EOF
DoCmd.OpenReport rptName, acViewPreview, , PK & " = " & rs.Fields(PK)
DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strDir & rs.Fields(PK) & rs.Fields("departmentName") & ".pdf"
DoCmd.Close acReport, rptName
rs.MoveNext
Loop
End Sub

 

[TomYC]

Thank you very much! Here's what I saw: "Too few parameters. Expected 4
Run time error 3061"
is what I received, when I put in my version of your code, into a command button on a form:
Public Sub cmdCreatePDF_Click()
Const strDir = "C:\"
Const qryName = "qryBudgetNew"
Const PK = "departmentID"
Const rptName = "rptBudgetCombined"
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset(qryName)
MsgBox "Department is " & PK

Do While Not rs.EOF
DoCmd.OpenReport rptName, acViewPreview, , PK & " = " & rs.Fields(PK)
DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, strDir & rs.Fields(PK) & rs.Fields("departmentName") & ".pdf"
DoCmd.Close acReport, rptName
rs.MoveNext
Loop
End Sub

 

[MajP]

too few parameters expected is an error on the SQL.

I assume that error occurs here
Set rs = CurrentDb.OpenRecordset(qryName)

Which means something wrong with the query. Can you post the query? Does it take any parameters by chance?

 

[MajP]

oh yeah I only post examples and not exact solutions. My field names do not exactly match yours. You need to check the field, table, query, and report names.

 

[TomYC]

You're probably correct, given that my query, qryBudgetNew is not so simple, containing a join, etc., and picking up some fields (parameters?) from a form:

SELECT qryBudgetNewJoined.Department, qryBudgetNewJoined.[Dept ID], qryBudgetNewJoined.[Job Code], qryBudgetNewJoined.[Job Description], qryBudgetNewJoined.[Total Regular FTE], qryBudgetNewJoined.[Total Overtime FTE], qryBudgetNewJoined.[Total Holiday FTE], qryBudgetNewJoined.[Total FTE]
FROM (qryDirectorBudget INNER JOIN qryBudgetNewJoined ON qryDirectorBudget.Department = qryBudgetNewJoined.Department) LEFT JOIN DepartmentList ON qryBudgetNewJoined.Department = DepartmentList.Department;

If needed, the other queries are:

qryDirectorBudget =
SELECT DISTINCT Directors.Service, Directors.DepartmentName, qryBudgetNewJoined.Department, qryBudgetNewJoined.[Dept ID], Directors.Segment
FROM Directors RIGHT JOIN qryBudgetNewJoined ON Directors.DeptID = qryBudgetNewJoined.Department
WHERE (((Directors.Service) Like [Forms]![frmReportGenerator].[cboService] & "*") AND ((qryBudgetNewJoined.Department) Like [Forms]![frmReportGenerator].[cboDept] & "*") AND ((qryBudgetNewJoined.[Dept ID]) Like [Forms]![frmReportGenerator].[cboDeptNo] & "*") AND ((Directors.Segment) Like [Forms]![frmReportGenerator].[cboSegment] & "*"));

and qryBudgetNewJoined is:
SELECT Department, [Dept ID], [Job Code], [Job Description],
[Total Regular FTE], [Total Overtime FTE], [Total Holiday FTE],[Total FTE]
FROM BudgetNew
UNION SELECT [Rpt Dept], [Rpt Dept Desc], PositionDetail.[Job code], [Jobcode Title],
Null, Null, Null, Null
FROM PositionDetail PositionDetail LEFT JOIN BudgetNew
ON PositionDetail.[Rpt Dept]=BudgetNew.Department AND PositionDetail.[Job code]=BudgetNew.[Job Code]
WHERE BudgetNew.[Job Code] IS NULL;

If that's all too much to parse, perhaps I could learn how to incorporate parameters into the VBA code you supplied?
Thanks!
T.Y.

 

[MajP]

If they are coming from a form it should be fine it would pull them.

Can you do this as a test to see if the query opens? Ensure the query is closed and see if this opens it. The forms have to be open.

public sub test
Const qryName = "qryBudgetNew"
docmd.openquery (qryName)
end sub

 

[TomYC]

It did pull the query! I was doubtful. And I also note that the query displayed (when I had selected a single Department from the form) a set of records (all with the same Department and Dept ID) representing the different job codes present for that Department. So ultimately I want to create one report PDF for each unique Department, not for each job code in each department, if that is clear. That's down the road from here...
Tnx