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 35

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 35

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

792 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