Exporting Reports by group based on query to PDF

[brahma228]

Hi all,

First time poster and beginner here.

I'm having an issue with a some VBA I'm trying to piece together to export individual reports to PDF by employee ID. The report is based on a query (qryMnthlyCommSumm) and the issue I'm having is passing the criteria to the query. There are 2 criteria I'm trying to pass to the report query: "Monthly" and a combo box value from a subform but I'm getting Error 3265 "Item not found in this collection."

This is what I have so far thanks to some searching:

Private Sub Command22_Click() 
Dim dbs As DAO.Database 
Dim qdf As DAO.QueryDef 
Dim rst As DAO.Recordset  
Set dbs = CurrentDb 
'Get the parameter query 
Set qdf = dbs.QueryDefs("qryMnthlyCommSumm")  
qdf.Parameters("[Pmt Occ]").Value = "Monthly" 
qdf.Parameters("[End Date]").Value = "[Forms]![fmnuMain]![NavigationSubform].[Form]![RptMonth]"  
Set rst = qdf.OpenRecordset()  
Do While Not rst.EOF
     strRptFilter = "[EE No] = '" & rst![EE No] & "'"
     DoCmd.OutputTo acOutputReport, "rptMnthlyCommStmt", acFormatPDF, "C:\Temp" & "\" & rst![LN] & " - " & rst![EE No] & ".pdf"
     DoEvents
     rst.MoveNext 
Loop

rst.Close 
Set rst = Nothing
End Sub

Thanks in advance!

 

[Uncle Gizmo]

I did a set of videos and a Google presentation explaining the method I use. Might be of interest to you. Here's the link:- https://sites.google.com/site/msacc...pies-of-report-with-different-criteria/videos

 

[brahma228]

I did a set of videos and a Google presentation explaining the method I use. Might be of interest to you. Here's the link:-

Awesome. I'll take a look and come back with any questions.

 

[brahma228]

If it helps, I'm trying to modify the VBA code posted here:

ttps://access-programmers.co.uk/forums/showpost.php?p=1264731&postcount=24
ttps://access-programmers.co.uk/forums/showpost.php?p=1049034&postcount=10

This code worked if I already had a table with all the information I needed on the reports.

I guess a workaround is instead of a select query, I do a make table then use the table as my recordset. I'm trying to skip that step and use the select query as my recordset but using the SQL string per the above doesn't seem to be working.

 

[brahma228]

Just an update for people who are trying to accomplish this in the future. I've decided to create a temp table to store my report data instead. Turns out it runs faster because it doesn't need to rerun the query for each report group.