Solved

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

Posted on 2013-11-07
4
974 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 48

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 48

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…

696 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