Solved

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

Posted on 2013-11-07
4
899 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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

15 Experts available now in Live!

Get 1:1 Help Now