Using MS Access Macro or VBA loop to print individual reports

Question

I'm not at all familiar with VBA scripts, but I've tried a few scripts found here and elsewhere online without a lot of luck.

Currently I have a form where a user can press an "Export Report" command button, which opens a prompt asking for an [AgencyID] (a 5 digit id number). Once the user enters an [AgencyID] a report "rptAgencyReport" is launched which displays information from query "qryAgencyReport" using the criteria "Like [AgencyID]" to query fields from many tables in a print-friendly view. From here the user can either print the report or save it as pdf or rtf.

My problem is if we want to export a copy of all our files to .rtf format we have to manually select the "Export Report" button, enter the [AgencyID], then save the outputted file. For 600-700 individual reports this takes days to complete.

What I would like to do is rather than manually entering the [AgencyID] and running each report, I would like to batch export these reports to .rtf files using the [AgencyID] as the filename. I have a query "exportAgencyID" which contains a lists of all the [AgencyID]s I need to run, but I have not found a way to pass the [AgencyID] as a variable to the report and query.

I've looked at https://support.microsoft.com/en-us/kb/209790 but I don't know how to take each record from query "exportAgencyID" and use it as the input required for the query "qyrAgencyReport".

Query "exportAgencyID"

[AgencyID]
3
36
162
194
1190
1345
. . .

Query "qryAgencyReport"

Field: AgencyID
Table: AgencyMaster
Sort: 
Show: checked
Criteria: Like [AgencyID]

tldr; Report displays results of query in a printable form; query results are based on the user entered [AgencyID]. How can I set the [AgencyID] input automatically from a table or query containing all the [AgencyID]s and export a record named [AgencyID].rtf for each [AgencyID]?

Anyone able to help a non-profit save a few days of repetitive work?

I found this code at: mrexcel.com/forum/microsoft-access/… Does most of what I need. I get hung up with "Me.cbo". Since I am not using a combo box, what do I replace this with? Leaving Me.cbo off causes thus to hang. — user1261889
– user1261889, Commented Sep 18, 2015 at 0:53

Juan Castiblanco · Accepted Answer · 2015-09-18 18:35:41Z

2

The question is a bit ambiguous, but I created a simple function that might help you out

 Public Sub GetAgencyID()
 Dim rst As Recordset
 Dim db As Database
 Dim strSQL As String

 set db = CurrentDb


 strSQL = "SELECT exportAgencyID.AgencyID FROM exportAgencyID ORDER BY exportAgencyID"

  set rst = db.OpenRecordset(strSQL)

 rst.MoveFirst

 Do Until rst.EOF
 DoCmd.OpenReport "rptAgencyReport", acViewPreview, , "AgencyID = " & rst!AgencyID
 DoCmd.OutputTo acOutputReport, "rptAgencyReport", acFormatRTF, "C:\ReportsFromAccess\AgencyID." & rst!AgencyID & ".rtf"
docmd.close acReport, "rptAgencyReport"
rst.MoveNext
loop

rst.close
set rst = nothing
strSQL = ""
End Sub

C:\ReportsFromAccess requires you to have a folder named ReportsFromAccess in your C:\ drive. You can edit that to save it to where it needs to be saved. It should create a file like AGencyID1.rtf when AgencyID =1

edited Sep 18, 2015 at 18:35

answered Sep 17, 2015 at 23:43

Juan Castiblanco

515 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1261889 Over a year ago

In the above code I needed to add Set db = CurrentDb Set rst = db.OpenRecordset("SELECT [AgencyID] FROM [export-agency-list]", dbOpenDynaset)

Juan Castiblanco Over a year ago

Thanks, I must have accidentally missed that part. Note that since strSQL was already declared with the query you asked, the code can simply be db.OpenRecordset(strSQL)

user1261889 · Accepted Answer · 2015-09-18 16:38:14Z

0

Thank you @juan-castiblanco for the code. I needed to make a couple tweaks to get this to work. Below is the working code.

Private Sub GetAgencyID()
 Dim rst As Recordset
 Dim db As Database
 Dim strSQL As String

 Set db = CurrentDb()

 Set rst = db.OpenRecordset("SELECT [AgencyID] FROM [exportAgencyID]")

rst.MoveFirst

Do Until rst.EOF
  DoCmd.OpenReport "rptAgencyReport", acViewPreview, , "AgencyID = " & rst!AgencyID
  DoCmd.OutputTo acOutputReport, "rptAgencyReport", acFormatRTF, "C:\Provider Profiles 2016\" & rst!AgencyID & ".rtf"
  DoCmd.Close acReport, "rptAgencyReport"
  rst.MoveNext
Loop

rst.Close
Set rst = Nothing
strSQL = ""
End Sub

answered Sep 18, 2015 at 16:38

user1261889

31 silver badge4 bronze badges

Collectives™ on Stack Overflow

Using MS Access Macro or VBA loop to print individual reports

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related