access 2010 create a report with a filename for each page from a data field

interesting challenge; have a large Access report that creates a unique page for each record.

Would like to create a .pdf of each page in the report with a file name that includes the record ID.

Currently the report is grouped, with ~100 to 200 pages per group (each page break corresponds to a unique record.)

How to create a report that creates >100 unique pages and saves them as individual files, without having to query each record (manually create the single page reports)?
VirtualKansasAsked:
Who is Participating?
 
Dale FyeCommented:
Generally, I do this with a separate recordset and a loop.  In my VBA code, I:

1.  Open the report (full report, all records)

2.  Create a recordset based on the reports recordset.

3.   Loop through each of the records in this recordset.

3a.  Inside the loop, I use whatever unique field is in each record to filter the report to just this record.
3b.  Once the report is filtered, I use the OutputTo method to save that single report
3c.  goto the next record in the recordset

4.  Close the recordset and the report.

I've seen it done where the user opens the filtered report inside the loop, but this means you have to open the report X# of times.  The technique described above is much quicker as it only opens the report once and just sets a filter.
0
 
VirtualKansasAuthor Commented:
Pretty advanced for me (jack of all trades, here.)  If you can throw me a little more detail, would appreciate it.  Still, looks like bang on point, so thank you.
0
 
Dale FyeCommented:
OK, lets assume you have a form, with a command button.

The code behind that command button might look something like:

Private Sub SaveAsIndividualPDFs_Click
    dim rpt as Report
    dim rs as DAO.Recordset
    dim strFileName as string

    'This opens the report in preview mode.
    Docmd.OpenReport "YourReportName", acViewPreview
    set rpt = Reports("YourReportName")

    'This creates a recordset to loop thru
    set rs = currentdb.querydefs("YourReportsQueryName").Openrecordset

    'Loop through the records
    rpt.filteron = true
    While not rs.eof

        rpt.Filter = rs!ID
        strFileName = "C:\yourPath\" & format(date(), "yyyy-mm-dd") & "_ID-" & Format(rs!ID, "000") & ".pdf"
        docmd.outputto acoutputreport, rpt.name, acformatpdf, strFileName, false

        rs.movenext
    Wend
    rs.close
    set rs = nothing
    docmd.Close acReport, rpt.name

End Sub

Open in new window

This would create file names that look like: 2013-10-08_ID-001.pdf

Written from my iPad, so may contain typos. Also, I can not remember whether you need to add the ".pdf" to the end of strFileName or whether Access will automatically add that given the output type.
0
 
cres1121Commented:
Hi Dale... I am running into the same problem.. I stole your code and blindly tried to modify it for my use.  I created a form with one button.   I put your code in vba and I tried to modify it per your instructions.  Two things I want different I want payroll date in the naming of the file which is payrolldate of the field in my query.  But it gives me a error 2501
The outputto action was canceled.  

Private Sub Command0_Click()

'Private Sub SaveAsIndividualPDFs_Click()
    Dim rpt As Report
    Dim rs As DAO.Recordset
    Dim strFileName As String

    'This opens the report in preview mode.
    DoCmd.OpenReport "qrytotalcommissiondetail", acViewPreview
    Set rpt = Reports("qrytotalcommissiondetail")

    'This creates a recordset to loop thru
    Set rs = CurrentDb.QueryDefs("qrytotalcommissiondetail").OpenRecordset

    'Loop through the records
    rpt.FilterOn = True
    While Not rs.EOF

        rpt.Filter = rs!Employeename
        strFileName = "K:\payroll\commissions\" & Format(Date, "mm-dd-yyyy") & "_" & (rs!Employeename) & ".pdf"
        DoCmd.OutputTo acOutputReport, rpt.Name, acFormatPDF, strFileName, False

        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
    DoCmd.Close acReport, rpt.Name

End Sub

I got the error 2501 to go away once I made the path it could not create it.  It did not break on employee it created the same report for all the employee.  It also did not change the name of the report. SO I ended up with one big report.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.