Loop print report to file with parameters stored in a table?

User avatar
yellowfin7
Lounger
Posts: 29
Joined: 31 Jan 2013, 17:43

Loop print report to file with parameters stored in a table?

Post by yellowfin7 »

Good Day,

Im running on Win 7 access 2007 and was hoping to get some help putting the code together if at all possible to create a VBA loop that will grab parameters from a single column in a table and push the value through to the report when printing it to pdf.

I know there are lots of variables in the process, some of which im not sure I can accomplish. Though, I thought why not ask an see what others might have to say. :scratch:

Thanks

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Loop print report to file with parameters stored in a ta

Post by HansV »

Step 1:

If the record source of your report is a table, create a new query based on that table. Add all the fields that you need for the report, including the field on which you want to filter.
If the record source is a query, open that query in design view. If necessary, add the field on which you want to filter.
Enter the following expression in the Criteria row of the field on which you want to filter:

=GetParam()

Save and close the query.
Set the Record Source of the report to the name of the query.

Step 2:

Activate the Visual Basic Editor (Alt+F11).
Insert a new module (select Insert > Module).
Copy the following code into the module:

Code: Select all

Public Const strTable = "tblData"    ' table name
Public Const strField = "MyField"    ' field name
Public Const strReport = "MyReport"  ' report name
Public Const strPath = "C:\Export\"  ' path to store PDFs in
Public gParam                        ' variable to hold field value

Function GetParam()
    ' Return the value of the global variable
    GetParam = gParam
End Function

Sub LoopRecords()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFileName As String
    ' Reference to the current database
    Set dbs = CurrentDb
    ' Open a recordset on the table
    Set rst = dbs.OpenRecordset(strTable, dbOpenForwardOnly)
    ' Loop through the records
    Do While Not rst.EOF
        ' Set global variable to value of field
        gParam = rst.Fields(strField).Value
        ' Assemble the filename
        strFileName = strPath & gParam & ".pdf"
        ' Export the report to PDF
        DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:=strReport, _
            OutputFormat:=acFormatPDF, OutputFile:=strFileName
        ' Move to the next record
        rst.MoveNext
    Loop
    ' Close the table
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Sub
Change the values of the four constants at the beginning of the code to match your situation.

You can run the procedure LoopRecords directly from the Visual Basic Editor, or you can call it - for example - from the On Click event of a command button on a form.
Best wishes,
Hans

User avatar
yellowfin7
Lounger
Posts: 29
Joined: 31 Jan 2013, 17:43

Re: Loop print report to file with parameters stored in a ta

Post by yellowfin7 »

As always your input is greatly appreciated! I'll go ahead an work on setting this up in my DB and test it out. :cheers:

User avatar
yellowfin7
Lounger
Posts: 29
Joined: 31 Jan 2013, 17:43

Re: Loop print report to file with parameters stored in a ta

Post by yellowfin7 »

Just a follow up, I have succesfully implemented the code listed above. I adapted it to allow the use of one parameter table to be use in multiple loops for individual reports. This way the end users can manage what parameters they pass to the report exports.

By moving some of the prublic constants to the sub level I was able to customize the loops by report and still maintain the same code structure for easy reading.

Thank you again Hans :cheers: :thankyou:

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Loop print report to file with parameters stored in a ta

Post by grovelli »

Interesting, why do end users need multiple loops for individual reports to be able to manage what parameters they pass to the report exports?

User avatar
yellowfin7
Lounger
Posts: 29
Joined: 31 Jan 2013, 17:43

Re: Loop print report to file with parameters stored in a ta

Post by yellowfin7 »

grovelli wrote:Interesting, why do end users need multiple loops for individual reports to be able to manage what parameters they pass to the report exports?

One button to RULE THEM ALL... erhm.. One button to run them all. It is an export function allowing the end user the ability to export the needed reports as they need them.

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Loop print report to file with parameters stored in a ta

Post by grovelli »

Can you show the click event of that button? And if it just runs the export function, can you show that as well?

User avatar
yellowfin7
Lounger
Posts: 29
Joined: 31 Jan 2013, 17:43

Re: Loop print report to file with parameters stored in a ta

Post by yellowfin7 »

call the loop function for the onclick function

grovelli
4StarLounger
Posts: 528
Joined: 26 Jan 2010, 15:14

Re: Loop print report to file with parameters stored in a ta

Post by grovelli »

Is it possible to see your adaptation of the loop function?