使用 MS Access 宏或 VBA 循环打印单个报告 [英] Using MS Access Macro or VBA loop to print individual reports

问题描述

我对 VBA 脚本一点也不熟悉，但我尝试了一些在这里和网上其他地方找到的脚本，但运气不佳.

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.

目前我有一个表单，用户可以在其中按下导出报告"命令按钮，这会打开一个提示，要求输入 [AgencyID](一个 5 位数的 ID 号).用户输入 [AgencyID] 后，将启动报告rptAgencyReport"，该报告使用标准Like [AgencyID]"显示来自查询qryAgencyReport"的信息，以在打印友好的视图中查询许多表中的字段.用户可以从这里打印报告或将其保存为 pdf 或 rtf.

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.

我的问题是，如果我们要将所有文件的副本导出为 .rtf 格式，我们必须手动选择导出报告"按钮，输入 [AgencyID]，然后保存输出的文件.对于 600-700 个单独的报告，这需要几天时间才能完成.

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.

我想要做的不是手动输入 [AgencyID] 并运行每个报告，我想使用 [AgencyID] 作为文件名将这些报告批量导出到 .rtf 文件.我有一个查询exportAgencyID"，其中包含我需要运行的所有 [AgencyID] 的列表，但我还没有找到将 [AgencyID] 作为变量传递给报告和查询的方法.

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.

我看过 https://support.microsoft.com/en-us/kb/209790 但我不知道如何从查询exportAgencyID"中获取每条记录并将其用作查询qyrAgencyReport"所需的输入.

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".

查询exportAgencyID"

Query "exportAgencyID"

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

查询qryAgencyReport"

Query "qryAgencyReport"

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

tldr;报告以可打印的形式显示查询结果；查询结果基于用户输入的[AgencyID].如何从包含所有 [AgencyID] 的表或查询中自动设置 [AgencyID] 输入，并为每个 [AgencyID] 导出名为 [AgencyID].rtf 的记录?

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?

推荐答案

这个问题有点含糊，但我创建了一个简单的函数，可以帮助你

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 要求您在 C:\ 驱动器中有一个名为 ReportsFromAccess 的文件夹.您可以编辑它以将其保存到需要保存的位置.当 AgencyID =1

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

这篇关于使用 MS Access 宏或 VBA 循环打印单个报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！