Solved

automate the creation of a daily set of PDF's

Posted on 2013-11-22
4
206 Views
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.
0
Comment
Question by:jtbold2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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;
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
    Else
        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.
    Else
        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.
0
 

Author Comment

by:jtbold2
Comment Utility
not sure I understand either of these solutions
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
    rs.MoveNext
Loop
Set rs = Nothing

Open in new window

0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

762 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

7 Experts available now in Live!

Get 1:1 Help Now