Output to separate PDF files based on field (1 Viewer)

bren1519

New member
Local time
Today, 07:49
Joined
Sep 10, 2021
Messages
4
Hi there,
A bit of background on this report. It is a payroll report that for security reasons I can't build in Cognos. The data is coming from an Excel sheet which will be updated by payroll staff and they wish the output to be in pdf format on a network drive based on the department. They gave me sample data and I created a linked sheet in Access which they can refresh, I also created a complete list of GL account and associated department which they can also maintain (the idea being that I step out of the picture once a report is developed as I don't have access to the actual payroll data).

From the two excel data sheets, I create a query in Access and a report based on that query. Looks exactly like they have indicated they wish it to look, I've forced a new page after the dept group in the report (not sure about that piece).

I found a thread on this forum with code to output to pdf files. I'll put my code below for you as well as the query sql. The code works, in that it outputs a copy of the report for each department but it outputs the entire report for each department. Any assistance in coding this so that each department output contains only that department would be appreciated. The sub for the command click is attached to a button on the report. Thanks! Brenda



Private Sub Report_Close()
strRptFilter = vbNullString
End Sub

Private Sub Report_Open(Cancel As Integer)
If Len(strRptFilter) <> 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If
End Sub

Private Sub Command283_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT Payroll.[DEPARTMENT] FROM Payroll;", dbOpenSnapshot)
'LOAD CURRENT DATE TO VARIABLE - IN FORMAT dd-mm-yyyy
Date1 = Format(Date, "dd-mm-yyyy")

Do While Not rst.EOF
strRptFilter = "[DEPARTMENT]=" & Chr(61) & rst![DEPARTMENT] & Chr(61)

DoCmd.OutputTo acOutputReport, "Payroll SME Reporting", acFormatPDF, "C:\Users\bbrown\Desktop\PAYROLL\output\" & "\" & rst![DEPARTMENT] & Date1 & ".pdf"
DoEvents
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub

query sql

SELECT [Payroll SME Data].[GL Code], [Payroll SME Data].[Org Pay Class], [Payroll SME Data].[Employee Name], [Payroll SME Data].[Pay Date], [Payroll SME Data].[Annual Salary / Hourly Rate], [Payroll SME Data].[Total Hrs], [Payroll SME Data].[Regular Pay], [Payroll SME Data].Overtime, [Payroll SME Data].[Short Term Sickness], [Payroll SME Data].Bonus, [Payroll SME Data].[Vacation Pay], [Payroll SME Data].Other, [Payroll SME Data].[Gross Pay], [Payroll SME Data].[Total Benefits], DEPARTMENTS.DEPARTMENT, LASTPAYTABLE.LASTPAYREG, LASTPAYTABLE.LASTPAYOT, LASTPAYTABLE.LASTPAYST, LASTPAYTABLE.LASTPAYBONUS, LASTPAYTABLE.LASTPAYVAC, LASTPAYTABLE.LASTPAYOTHER, LASTPAYTABLE.LASTPAYGROSS, LASTPAYTABLE.LASTPAYBENFT
FROM (DEPARTMENTS RIGHT JOIN [Payroll SME Data] ON DEPARTMENTS.[GL ACCOUNT] = [Payroll SME Data].[GL Code]) INNER JOIN LASTPAYTABLE ON ([Payroll SME Data].[GL Code] = LASTPAYTABLE.[GL Code]) AND ([Payroll SME Data].[Pay Date] = LASTPAYTABLE.[Pay Date]) AND ([Payroll SME Data].[Employee Name] = LASTPAYTABLE.[Employee Name]);
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:49
Joined
Sep 21, 2011
Messages
14,051
Well, you are not using that filter you created.?
Open the report hidden using the filter, then OutputTo

User here https://www.accessforums.net/showthread.php?t=84350 was trying to do the same thing. Ignore the title, very misleading.
Also look at Similar Threads at the bottom of the page.

HTH
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:49
Joined
Oct 29, 2018
Messages
21,358
Hi Brenda. Welcome to AWF!

If you are looping through the Excel to filter for each department, it looks like you need an inner loop to go through each payroll within each department and export each record to pdf.
 

bastanu

AWF VIP
Local time
Today, 04:49
Joined
Apr 13, 2010
Messages
1,401
Hi Brenda,
To save you going through the entire thread given as example maybe try this:
Code:
Do While Not rst.EOF
strRptFilter = "[DEPARTMENT]= '" &  rst![DEPARTMENT] & "'"  'assumes Department is a string

Docmd.OpenReport "Payroll SME Reporting",acViewPreview, strRptFilter, acHidden
DoCmd.OutputTo acOutputReport, "Payroll SME Reporting", acFormatPDF, "C:\Users\bbrown\Desktop\PAYROLL\output\" & "\" & rst![DEPARTMENT] & Date1 & ".pdf"
DoEvents
DoCmd.Close acReport, "Payroll SME Reporting", acSaveNo
rst.MoveNext
Loop

Cheers,
 

bren1519

New member
Local time
Today, 07:49
Joined
Sep 10, 2021
Messages
4
Hi Brenda,
To save you going through the entire thread given as example maybe try this:
Code:
Do While Not rst.EOF
strRptFilter = "[DEPARTMENT]= '" &  rst![DEPARTMENT] & "'"  'assumes Department is a string

Docmd.OpenReport "Payroll SME Reporting",acViewPreview, strRptFilter, acHidden
DoCmd.OutputTo acOutputReport, "Payroll SME Reporting", acFormatPDF, "C:\Users\bbrown\Desktop\PAYROLL\output\" & "\" & rst![DEPARTMENT] & Date1 & ".pdf"
DoEvents
DoCmd.Close acReport, "Payroll SME Reporting", acSaveNo
rst.MoveNext
Loop

Cheers,
Oh seriously, bless you lol! That worked perfectly. So glad I finally just asked as I've been trying for the last couple of days to get this to work! Thanks again!
cheers!
Brenda
 

bastanu

AWF VIP
Local time
Today, 04:49
Joined
Apr 13, 2010
Messages
1,401
You're very welcome Brenda, good luck with your project.
Welcome to AWF!
Cheers,
 

Users who are viewing this thread

Top Bottom