CODEWITHSUNDEEP
ms-accessvbams-access-2010
user1261889
user1261889

Reputation: 3

Using MS Access Macro or VBA loop to print individual reports

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?

Upvotes: 0

Views: 5626

Answers (2)

Juan Castiblanco
Juan Castiblanco

Reputation: 51

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

Upvotes: 2

user1261889
user1261889

Reputation: 3

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

Upvotes: 0

Related Questions