Limit the PDF Report to each Agency per Page 1

[lmcate]

Hi I have a report that will create multiple PDF'S per Agency with the employees data. and what I want only each Agency information per page and have a break. So each PDF (5 PDF) with each Agency and with their respect employees. What I am getting is 5 PDF with all 5 employee data in a single pdf.

Here is my code:

Private Sub Label315_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsEmp As DAO.Recordset
Dim MyFileName As String
Dim strWhere As String
Dim mypath As String
Dim temp As String
mypath = "W:\Call Center\Call Center Reports\Agency_Reports\"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT [Employer] FROM [Agency_Hours]", dbOpenDynaset)

'Clear previously built Agency Employer list - 210119 - SF
DoCmd.OpenQuery "qClrAgencyEmployer", acViewNormal, acEdit
'Build Agency Employer list for current run - 210119 - SF

DoCmd.OpenQuery "qAddAgencyEmployer", acViewNormal, acEdit

Set rsEmp = db.OpenRecordset("SELECT [Employer] FROM [AgencyEmployer]", dbOpenDynaset)

Do While Not rsEmp.EOF
temp = rsEmp("Employer")

'MyFileName = rs("Employer") & ".PDF" 'You can explore assigning the other field names
MyFileName = temp & ".PDF" 'You can explore assigning the other field names

strWhere = "[Employer] =" & temp

DoCmd.OpenReport "timecard_agency_daterange_TZ", acViewReport, strWhere
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Agency Report to PDF"
rsEmp.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
End Sub

Thank you

 

[lameid]

Your code reads as though you are looping over employers, you say agency but you say you get a report for each employee?

Are Agency and Employer the same thing?

I'm guessing not with a table name like AgencyEmployer suggesting an cross reference between the two.

Based on that assumption I have to ask why if you are saving a PDF for each Employer do you expect to have one for each agency?

 

[lmcate]

Hi Lameid:

My goal is to have each employer and their employees with stats written to a PDF what the report above makes 5 PDF's with all 5 Employers for each employee. Perhaps my query.

This is my goal
FIRST PDF
Employer A.pdf
Employee xx1 xxxxx
Employee xx2 xxxxx
Employee xx3 xxxxx

SECOND PDF
Employer B.pdf
Employee aa1 xxxxx
Employee aa2 xxxxx

THIRD.PDF
Employer B.pdf
Employee vv1 xxxxx
Employee vv2 xxxxx
Employee vv3 xxxxx
Employee vv4 xxxxx
Employee vv5 xxxxx

 

[lameid]

Does it run run for one employer for each PDF or is it doing something else...

Is your strWhere correct? Is Employer a numeric field or some other datatype? Only Numeric fields do not need delimiters of some sort around the value. Text needs either single or double quotes ( ' or " ) around values in queries and dates need use the hash (#)

strWhere = "[Employer] =" & temp

What is the record source behind the report (table or query, if query what is the SQL - Is Employer an unambiguous field in it)?

Does your report have a sub reports on it? If so are the master and child fields set appropriately?

Can you copy your report and modify it to run for one employer and it work right? What does that query look like?