Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-11-07
4
Medium Priority
?
1,036 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 earned 2000 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
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

618 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