Limit the PDF Report to each Agency per Page 1

[lmcate]

Hi I have a report that will create multiple PDF per Agency and what I want only that Agency information on each page. I am getting everyone's information for every agency.

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

 

[Andrzejek]

What do you get in the Immediate Window when you [blue]do this[/blue]:

...
strWhere = "[Employer] =" & temp
[blue]Debug.Print strWhere[/blue]
DoCmd.OpenReport "timecard_agency_daterange_TZ", acViewReport, strWhere
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[lmcate]

Thank you Andy,

The debug don't display anything.

I get 5 Individual PDF's with the correct PDF filename with every agency's information. (a total of 5 agencies

Agency:
A -- has the data of all 5 agencies
B -- has the data of all 5 agencies
C -- has the data of all 5 agencies
D -- has the data of all 5 agencies
E -- has the data of all 5 agencies

My Goal is tot get:
Agency A with 3 employee records and A.pdf
B with 4 employee records and B.pdf .... etc....

Sam

 

[Andrzejek]

The debug don't display anything."
If your logic goes thru the code with [tt]Debug.Print[/tt], you should see something in the [tt]Immediate Window[/tt]

If you do not know how to use [tt]Immediate Window[/tt], you may replace:
[tt]Debug.Print strWhere[/tt]
with
[tt]MsgBox strWhere[/tt]
You should see a few message boxes with some information in them.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson