automate the creation of a daily set of PDF's

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.
Who is Participating?
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

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;
Jim P.Commented:
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.
jtbold2Author Commented:
not sure I understand either of these solutions
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.