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
  • 2
LVL 34

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 34

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

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

914 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

24 Experts available now in Live!

Get 1:1 Help Now