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.
Thanks
Loop print report to file with parameters stored in a table?
-
- Lounger
- Posts: 29
- Joined: 31 Jan 2013, 17:43
-
- Administrator
- Posts: 78625
- 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
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:
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.
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
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
Hans
-
- Lounger
- Posts: 29
- Joined: 31 Jan 2013, 17:43
Re: Loop print report to file with parameters stored in a ta
As always your input is greatly appreciated! I'll go ahead an work on setting this up in my DB and test it out.
-
- Lounger
- Posts: 29
- Joined: 31 Jan 2013, 17:43
Re: Loop print report to file with parameters stored in a ta
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
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
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Loop print report to file with parameters stored in a ta
Interesting, why do end users need multiple loops for individual reports to be able to manage what parameters they pass to the report exports?
-
- Lounger
- Posts: 29
- Joined: 31 Jan 2013, 17:43
Re: Loop print report to file with parameters stored in a ta
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.
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Loop print report to file with parameters stored in a ta
Can you show the click event of that button? And if it just runs the export function, can you show that as well?
-
- Lounger
- Posts: 29
- Joined: 31 Jan 2013, 17:43
Re: Loop print report to file with parameters stored in a ta
call the loop function for the onclick function
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
Re: Loop print report to file with parameters stored in a ta
Is it possible to see your adaptation of the loop function?