在Access 2007中使用VBA模块填充报表 [英] Populating a report with a VBA module in Access 2007

问题描述

 

我想在表上运行SQL查询，遍历记录集并计算某些结果.

I want to run an SQL query on a table, loop through the recordset and calculate certain results.

然后我想将结果显示在Access报告中.

I then want to present the results in an Access report.

我很难找到优质的在线VBA资源，所以这一直是一个挑战.

I've had a hard time finding good online VBA resources so this has been a challenge.

 

到目前为止，我已经创建了一个宏，该宏在用VB编辑器编写的模块中调用函数.

So far I've created a Macro which calls a Function within a module that I've written in the VB editor.

此函数执行查询并遍历记录集，从而计算出多个值.

This function performs the query and loops through the recordset, calculating a number of values.

然后我要做的就是将值放在带有标签的文本框中，然后打开报告.

All I want then is to place the values in text boxes with labels in a report and to open the report.

我已经在设计视图中创建了一个带有必要文本框的报告，但是我不确定在代码运行后如何填充它们.

I've created a report in the design view with the necessary textboxes but I'm not sure how to populate them after the code has ran.

记录集非常大，该函数需要大量时间才能运行，因此我不想每次填充一个字段时都运行该函数.

The recordset is quite large and the function takes a substantial amount of time to run so I don't want to run the function each time I populate a field.

我的代码的简化版本如下:

A stripped down version of my code is as follows:

Option Compare Database
Option Explicit

Public Function MyFunction()
  
  ' Connect to database and obtain recordset
  Dim database As DAO.database
  Dim recordset As DAO.recordset
  Set database = CurrentDb
  Set recordset = database.OpenRecordset("SELECT [My Table].ID, [My Table].Production FROM [My Table];")

  ' Loop to determine results goes here
  
  ' Set up the report
  Dim report As Document
  Set report = database.Containers("Reports").Documents("Results Report")
  
  ' Need code to assign values to text boxes here
  
  DoCmd.OpenReport report.Name, acViewPreview
End Function

任何帮助，不胜感激！

 

Aonghus

推荐答案

VBA论坛:

VBA forum : http://social.msdn.microsoft.com/forums/en-US/isvvba/threads/

kaymaf

这篇关于在Access 2007中使用VBA模块填充报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！