从动态交叉表查询和 vba 访问报告以“手动”生成报告

access report from dynamic crosstab query and vba to "manually" generate reports

提问by Marcelo Zabani

I have come across the problem of generating complex access reports (by complex I mean with data processing, variable number of fields, among others).
Let me explain in deeper detail some of the things I need to implement:

我遇到了生成复杂访问报告的问题（复杂是指数据处理、可变数量的字段等）。
让我更详细地解释一些我需要实现的事情：

• Some fields should not show according to some values in a query
• If a certain record does not exist, a nice colored (very noticeable) message should appear instead of the values that would be there (Suppose, for example, that a record with 03/04/2009 in the date field exists, a record with 03/06/2009 in the date field also exists but no record with 03/05/2009 exists. Before showing the data related to the last record, I should print something like "Didn't show up on 03/05/2009")
• A bar chart that takes as data not the values in the records, but instead something else that is calculated over a set of records (like an average of all grades for a certain date). The number of series in this chart also varies according to values in the records, this chart would not be in the detail section, but instead in the page heading or some kind of group heading.

• 某些字段不应根据查询中的某些值显示
• 如果某个记录不存在，则应该出现一条漂亮的彩色（非常明显）消息而不是存在的值（例如，假设日期字段中存在 03/04/2009 的记录，日期字段中的 03/06/2009 也存在但不存在 03/05/2009 的记录。在显示与最后一条记录相关的数据之前，我应该打印类似“2009 年 3 月 5 日没有出现”的内容)
• 条形图不是将记录中的值作为数据，而是根据一组记录计算的其他数据（例如某个日期所有成绩的平均值）。此图表中的系列数也根据记录中的值而变化，此图表不会在详细信息部分中，而是在页面标题或某种组标题中。

It should also be mentioned that the query is a TRANSFORM query (more precisely, an INNER JOIN of many TRANSFORM queries), and thus the number of columns returned by the query varies. While in the past I've been unable to bind this query as the recordsource for the report, somehow Access stopped complaining for now (can someone please clarify this? Is this normal, should I not worry about it and use it as a recordsource or should I avoid it?)

还应该提到的是，该查询是一个 TRANSFORM 查询（更准确地说，是许多 TRANSFORM 查询的 INNER JOIN），因此查询返回的列数是不同的。虽然在过去我一直无法将此查询绑定为报告的记录源，但不知何故 Access 现在停止抱怨（有人可以澄清一下吗？这是正常的，我不应该担心并将其用作记录源或我应该避免它吗？）

There are two options to achieve what I want (that I can see for now):

有两种选择可以实现我想要的（我现在可以看到）：

1. Create a report with no record source and lots of unbound fields, and through several events (Report_Open, Section_Format, etc.) and with the help of DAO, manually set the values of these fields. Changing the Data Series of the chart is also possible through VBA.
2. Set the record source to the query, and create some crazy and confusing VBA code to deal with the data and implement everything I need.

1. 创建一个没有记录源和大量未绑定字段的报表，并通过几个事件（Report_Open、Section_Format 等）并在 DAO 的帮助下手动设置这些字段的值。也可以通过 VBA 更改图表的数据系列。
2. 将记录源设置为查询，并创建一些疯狂且令人困惑的 VBA 代码来处理数据并实现我需要的一切。

It seems to me that option 2 is going to be a huge headache and waste of time, and I recognize option 1 is pretty much like writing to an Excel file (since all the data is obtained with DAO), which would be much easier since I have much more control over almost everything there (but for many other reasons, we want everything in an access report)

在我看来，选项 2 将是一个巨大的头痛和浪费时间，我认识到选项 1 很像写入 Excel 文件（因为所有数据都是通过 DAO 获得的），这会容易得多，因为我对那里的几乎所有内容都有更多的控制权（但由于许多其他原因，我们希望所有内容都在访问报告中）

While I'm biased and intend to go with option 1, I have found several problems with this option, for example:

虽然我有偏见并打算采用选项 1，但我发现此选项存在几个问题，例如：

1. I can't find a way to create new pages in the report with VBA, and thus I'm limited only to the first page.
2. Lack of some kind of free, online, decent and complete documentation on VBA and Access Reports

1. 我找不到使用 VBA 在报告中创建新页面的方法，因此我仅限于第一页。
2. 缺乏关于 VBA 和 Access Reports 的某种免费、在线、体面和完整的文档

Also, if option 2 is more viable, I'm certainly willing to go with it, but I would also need some advice, and perhaps some tips to solving the problems I mentioned in this question.

另外，如果选项 2 更可行，我当然愿意接受它，但我还需要一些建议，也许还需要一些技巧来解决我在这个问题中提到的问题。

So, the questions are:

所以，问题是：

• Where can I find some decent and complete documentation on Access Reports and VBA?
• How can I create pages in an access report, and choose which page I want to write to?
• With the problem I have in my hands, will I reach any bottlenecks I should know about? Should I already be thinking of alternatives to Access Reports (writing to a spreadsheet, for example?)

• 我在哪里可以找到有关 Access Reports 和 VBA 的一些体面且完整的文档？
• 如何在访问报告中创建页面，并选择要写入的页面？
• 有了我手中的问题，我是否会遇到任何我应该知道的瓶颈？我是否应该已经在考虑 Access Reports 的替代方案（例如，写入电子表格？）

回答by JeffO

Sounds like you want to dynamically create the reportand avoid all the dummy text boxes.

听起来您想动态创建报告并避免使用所有虚拟文本框。

回答by David-W-Fenton

In regard to:

关于：

I can't find a way to create new pages in the report with VBA, and thus I'm limited only to the first page.

我找不到使用 VBA 在报告中创建新页面的方法，因此我仅限于第一页。

Your solution #1 seems to assume an unbound report.

您的解决方案 #1 似乎假设了一个未绑定的报告。

I think what I'd do is have the form the crosstab as the rowsource, so you'd have records to generate the pages, and then define your report's controls with no ControlSource (except for the controls that are bound to fields that are always present in the CrossTab). Then you could assign the ControlSources at runtime based on the particular columns. Here's the SQL for a crosstab grabbed from an app I'm working on now:

我想我要做的是将交叉表作为行源的形式，因此您将有记录来生成页面，然后定义没有 ControlSource 的报表控件（绑定到始终为的字段的控件除外）存在于交叉表中）。然后，您可以在运行时根据特定列分配 ControlSource。这是从我现在正在开发的应用程序中抓取的交叉表的 SQL：

  TRANSFORM First(impNoMatch.PersonID) AS FirstOfPersonID
  SELECT impNoMatch.LastName, impNoMatch.FirstBame
  FROM impNoMatch
  GROUP BY impNoMatch.LastName, impNoMatch.FirstName
  PIVOT impNoMatch.Status;

Now, you know that the fields in the SELECT clause will always be present, so if you opened a recordset on the SQL string you are using and count the number of fields in the recordset's Fields collection (you can't use the report's Recordset unless it's an ADO recordset, i.e., not bound to the Recordsource):

现在，您知道 SELECT 子句中的字段将始终存在，因此如果您在正在使用的 SQL 字符串上打开记录集并计算记录集的 Fields 集合中的字段数（除非它是一个 ADO 记录集，即没有绑定到 Recordsource）：

  Dim strSQL As String
  Dim rsFields As DAO.Recordset
  Dim lngFieldCount As Long

  strSQL = Me.Recordsource
  Set rsFields = CurrentDB.OpenRecordset(strSQL)
  lngFieldCount = rsFields.Fields.Count

From that, since you know the number of fields in the SELECT statement (i.e., the row headings), you can calculate the number of dynamic controls you want to assign, and you can use this recordset's fields collection to assign the ControlSources and unhide the controls.

由此，由于您知道 SELECT 语句中的字段数（即行标题），您可以计算要分配的动态控件的数量，并且您可以使用此记录集的字段集合来分配 ControlSources 并取消隐藏控件。

You'd start out with all your controls that will display the dynamic fields set so their Visible property is FALSE. You'd also use a naming convention for those controls. In the code below, I've used txtNN, where NN is the numeric index in the Fields collection formatted as 2 digits. Here's the code (which adds lines to what's listed above, and is run in the OnOpen event):

您将从将显示动态字段设置的所有控件开始，以便它们的 Visible 属性为 FALSE。您还可以为这些控件使用命名约定。在下面的代码中，我使用了 txtNN，其中 NN 是 Fields 集合中的数字索引，格式为 2 位数字。这是代码（在上面列出的内容中添加了几行，并在 OnOpen 事件中运行）：

  Dim strSQL As String
  Dim rsFields As DAO.Recordset
  Dim lngFieldCount As Long
  Dim l As Long
  Dim strControlName As String

  strSQL = Me.RecordSource
  Set rsFields = CurrentDb.OpenRecordset(strSQL)
  lngFieldCount = rsFields.Fields.Count
  For l = 2 To lngFieldCount - 1
    strControlName = "txt" & Format(l, "00")
    Me(strControlName).ControlSource = rsFields.Fields(l).Name
    Me(strControlName).Visible = True
  Next l
  rsFields.Close
  Set rsFields = Nothing

Now, if you want to get fancy, you can reformat the controls, changing widths and horizontal/vertical position. If you do that, you have to do it in a different event, and it's a bit tricky to choose that. The only good place to put it is in a report group's header's OnFormat event. If you don't have any grouping, you can add one that doesn't do anything. In the case of my crosstab, a two-level sort on Lastname and Firstname and a header on the Firstname group with nothing in it is a good place to use the OnFormat event to change the appearance/layout of the controls on your report.

现在，如果你想变得更有趣，你可以重新格式化控件，改变宽度和水平/垂直位置。如果你这样做，你必须在不同的事件中进行，选择它有点棘手。放置它的唯一好地方是在报告组的标题的 OnFormat 事件中。如果您没有任何分组，则可以添加一个不执行任何操作的分组。在我的交叉表的情况下，姓氏和名字的两级排序以及名字组上没有任何内容的标题是使用 OnFormat 事件更改报表上控件的外观/布局的好地方。

As to your question about how to learn how to do this, I recommend picking up an intermediate/advance Access programming book. The Access Developers Handbook is the gold standard on this, and includes tons of examples of programmatic control of reports.

至于您关于如何学习如何做到这一点的问题，我建议您选择一本中级/高级 Access 编程书籍。Access Developers Handbook 是这方面的黄金标准，包括大量的报表程序控制示例。