automate the creation of a daily set of PDF's

Posted on 2013-11-22
Last Modified: 2013-12-19
I need to create a filtered pdf report for employees with their individual inventories. The report I need to run is "copy of trader holdings," with the table containing the list of traders named "trading groups," Finally I have a form named "select trader" that I use to filter out each trader manually, which uses a query named "copy of trader holdings" as the source for the report, though I don't know if that is needed to do this automatically. Each "trader" that I wish to create a report for has a unique number in the books table, that field is named "trader."

What I am hoping for is that I will be able to push one button, and have a pdf for each trader in the table "trading groups" created and saved in a folder named "trader reports" on my H drive.

assume that my programming ability has been exhausted simply by describing what I want to do and will be of little or no more help.
Question by:jtbold2
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
LVL 36

Expert Comment

ID: 39670268
OutputTo, which is the method we use to export to .pdf doesn't support a where argument so you have to provide the report with selection criteria some other way.  I use a form with controls to hold the criteria.   The controls will be visible if the user enters the data such as a date range but will be hidden if I populate them via a VBA code loop.  This is the case you need.  So your code will need to open a recordset that returns all the traders you want to send a report to.  As you loop through that recordset, you would copy the TraderID to a hidden field on your form and then run the OutputTO method to create the PDf followed by the code to email it.  The report's RecordSource would reference the hidden field on the form for its criteria.

Select ... From ... Where TraderID = Forms!yourform!hiddenTraderID;
LVL 38

Expert Comment

by:Jim P.
ID: 39672190
To create a query on the fly:
Public Function OutputToExcel(OutputFileName As String, Optional ExportMonth As Integer = 0, Optional ExportYear As Integer = 0)

Dim DB As Database
Dim qdf As QueryDef
Dim QryName As String
Dim SQL As String

If ExportMonth = 0 Then                 'If you don't provide a month I default
    If Month(Date) = 1 Then             'it to the prior month and if the month
        ExportMonth = 12                'is January (1) then I default it to 12
        ExportMonth = Month(Date) - 1
    End If
End If
If ExportYear = 0 Then                  'Again if you don't provide a year I default
    If Month(Date) = 1 Then             'it to the current year unless it is
        ExportYear = Year(Date) - 1     'January then I default it to the prior year.
        ExportYear = Year(Date)
    End If
End If

'I'm creating the query name to export it looks like "zz_07_2005"
QryName = "zz_" & Format(ExportMonth, "00") & "_" & Format(ExportYear, "0000")

'This is the sql statement that selects for the month and year. You'll need _
 to edit it to correct for the table names and field names.
SQL = "SELECT * " & _
    "FROM TableName " & _
    "WHERE Month(FieldName) = " & ExportMonth & " " & _
    "AND YEAR(FieldName) = " & ExportYear

'This is where I actually create the query
Set DB = CurrentDb()
With DB
    Set qdf = .CreateQueryDef(QryName, SQL)
End With

'This is the output a spreadsheet.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, QryName, OutputFileName, True

'If you want to delete the query after it is ouput uncomment the line below.
'DoCmd.DeleteObject acQuery, QryName
DoCmd.CopyObject , "tblLogicalChain", acTable, "tblLogicalChain_base"
DoCmd.SetWarnings False

End Function

Open in new window

Ask if you wand a separate tale.

Author Comment

ID: 39675179
not sure I understand either of these solutions
LVL 36

Accepted Solution

PatHartman earned 500 total points
ID: 39675367
You can't do what you want without coding.   But first you need to understand what has to happen because unless you hire someone to do this for you, the best you'll get is examples that sort of does what you need that you will need to modify.  jimpen's solution doesn't really help you because it is showing you how to build an SQL string and that is not the problem.  The report already has an SQL string or a reference to a querydef.  That SQL needs to be modified to have a where clause with an argument.  That argument will be a TraderID that you put into a hidden field on the form.  So the WHERE clause will be something like - Where TraderID = Forms!yourform!txtTraderID;  

You need a recordset that lists all the Traders.

For each Trader record you want to create a PDF of that Trader's data.

You need a loop to read the recordset and for each record:
    1. put the TraderID into the hidden form field so the report can find it.  So each cycle through the loop puts a new value into Forms!yourform!txtTraderID. So:
Forms!yourform!txtTraderID = rs!TraderID
    2. use OutputTo to create the PDF for the trader.

This is air code (no variables declared, untested) but it should give you the idea.  You have a control on a form that will hold the ID field.  Each time through the loop, you put in a new value so when you open the report, the report's RecordSource query can read the value and filter the report.
Set rs = qd.OpenRecordset
Do until rs.EOF
    Forms!yourform!yourID = rs!SomeID
    strFileName = strPath & "yourreport-" & rs!SomeID & "-" & Format(Date(), "yyyymmdd") & ".pdf" 
    DoCmd.OutputTo acOutputReport, "yourreport", acFormatPDF, strFileName
Set rs = Nothing

Open in new window


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 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