Solved

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

Posted on 2013-11-07
4
913 Views
Last Modified: 2016-02-11
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)?
0
Comment
Question by:VirtualKansas
  • 2
4 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39630880
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
 

Author Closing Comment

by:VirtualKansas
ID: 39631253
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39631353
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
 

Expert Comment

by:cres1121
ID: 40621877
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query to summarise data Like Pivot Table 3 28
Excess tables to Excel BackUp 3 27
Update Match Entries 4 20
I need to be able to get MAX(date)-1 from table. 4 24
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now