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
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