vba 在来自、报告之间传递记录集

Passing recordset between froms, reports

提问by darkjh

I have a form which will generate a report for mailing. The form opens a recordset with all the fields needed. I'm wandering how can i pass this recordset to the report, so that i don't need to open the same recordset once again.

我有一个表格，可以生成邮寄报告。该表单将打开一个包含所有所需字段的记录集。我在徘徊如何将这个记录集传递给报告，这样我就不需要再次打开同一个记录集。

Similarly, sometimes i also want to pass recordset between forms (no main/sub form relation), how can i do this?

同样，有时我也想在表单之间传递记录集（没有主/子表单关系），我该怎么做？

Another little question, when i open a form in datasheet view, it's always very big. How can i limit its size when opening?

另一个小问题，当我在数据表视图中打开一个表单时，它总是很大。打开时如何限制其大小？

Thanks!

谢谢！

EDIT:

编辑：

To be clearer, say i have "FORM", when the user hit a button on it, "Report" will be open. I want "Report" to use the recordset that is already created(opened) in "FORM".

更清楚地说，假设我有“表格”，当用户点击它时，“报告”将被打开。我希望“报告”使用已在“FORM”中创建（打开）的记录集。

Also on "FORM", there is a textbox, filled by users, i want also to show it on the "Report".

同样在“FORM”上，有一个由用户填写的文本框，我也想在“报告”上显示它。

EDIT2:

编辑2：

I tired but cannot passe the recordset, nor populating a field in my report from a textbox on my form, very annoying ..

我累了但无法通过记录集，也无法从表单上的文本框中填充报告中的字段，非常烦人..

采纳答案by HK1

You should be able to accomplish this by simply passing the correct SQL statement to your report. This can be done using the Opening Arguments.

您应该能够通过简单地将正确的 SQL 语句传递给您的报告来完成此操作。这可以使用开始参数来完成。

Assuming that you really do want the identical set of records on your report as you see on your form, what you need to do depends on how you have opened your form, or how you are filtering your form. You could indeed use an identical DAO Recordset object and set your reports Recordset object to a copy or clone of the Form's Recordset object. However, this might not be necessary to get the results your look for.

假设您确实希望在您的报表中看到与您在表单上看到的相同的记录集，那么您需要做什么取决于您打开表单的方式或过滤表单的方式。您确实可以使用相同的 DAO Recordset 对象并将您的报告 Recordset 对象设置为 Form 的 Recordset 对象的副本或克隆。但是，这可能不是获得您想要的结果所必需的。

Solution #1
If your form uses a query or SQL statement you can use this solution.

解决方案 #1
如果您的表单使用查询或 SQL 语句，您可以使用此解决方案。

Code on your form:

表单上的代码：

DoCmd.OpenReport "rptReportName", acViewPreview, , , acWindowNormal, Me.RecordSource

Code on your report:

报告中的代码：

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = Nz(Me.OpenArgs, "")
End Sub

Solution #2
Use this solution if your form is using the form's filter property to filter down to the correct set of records. I'm assuming you then want to pass that filter condition on to the report. You'll need to configure the report so that it uses the same RecordSource as your Form (or it must at least contain the table/fields that will be included in your filter statement). The problem is that passing the recordsource of your report to your form doesn't pass any filter that you might have set on the form.

解决方案#2
如果您的表单使用表单的过滤器属性来过滤到正确的记录集，请使用此解决方案。我假设您然后想将该过滤条件传递给报告。您需要配置报表，使其使用与表单相同的记录源（或者它必须至少包含将包含在过滤器语句中的表/字段）。问题是将报表的记录源传递给表单不会传递您可能在表单上设置的任何过滤器。

DoCmd.OpenReport "rptReportName", acViewPreview, , Nz(Me.Filter, ""), acWindowNormal

As a final note, it is not possible to set a Report's recordset property. You can assign a Recordsource as I've already shown (a recordsource is a tablename, a queryname, or an SQL statement) but you cannot use the Recordset property unless the database is an Access Data Project, which I don't recommend using at all.

最后要注意的是，无法设置报表的记录集属性。您可以像我已经展示的那样分配一个记录源（记录源是一个表名、一个查询名或一个 SQL 语句），但是除非数据库是一个访问数据项目，否则您不能使用 Recordset 属性，我不建议在全部。

Edit1
It wasn't clear from the original post what problem the OP was trying to solve. I incorrectly assumed he was having trouble getting the same records to show on his report as what he has on his form. It appears that rather the OP is concerned about making two trips to the server to retrieve records.

Edit1
从原始帖子中不清楚 OP 试图解决什么问题。我错误地认为他很难在报告中显示与他在表格上显示的记录相同的记录。OP 似乎更关心的是两次访问服务器以检索记录。

Because you cannot set the Recordset value on an Access report, your best option might be to create a local Access table and simply use it as a temp table. I don't know what size your recordset typically is. If it's quite large (5000+ records) this solution may not be a good idea. One problem I can think of is that it will cause your front-end database application file to bloat over time unless you have the file setup to run Compact and Repair on close.

因为您无法在 Access 报表上设置 Recordset 值，所以最好的选择可能是创建一个本地 Access 表并将其用作临时表。我不知道您的记录集通常有多大。如果它非常大（5000 多条记录），这个解决方案可能不是一个好主意。我能想到的一个问题是，它会导致您的前端数据库应用程序文件随着时间的推移而膨胀，除非您将文件设置为在关闭时运行压缩和修复。

回答by David-W-Fenton

I think the worry about two trips to the server is unwarranted.

我认为担心两次访问服务器是没有根据的。

If you were using a Jet/ACE back end, Jet would cache the data locally, and there wouldn't be a re-retrieval unless the data had changed (in which case I think you'd probably want the up-to-date data, no?).

如果您使用的是 Jet/ACE 后端，Jet 会在本地缓存数据，除非数据发生更改，否则不会重新检索（在这种情况下，我认为您可能需要最新的数据，不是吗？）。

With a server database, the server itself is likely to cache the results, particularly if the SQL statement used is identical in both cases.

对于服务器数据库，服务器本身可能会缓存结果，尤其是在两种情况下使用的 SQL 语句相同的情况下。

This looks to me like a case of premature optimization.

在我看来，这像是一个过早优化的案例。

回答by Lefteris Gkinis

Well If I have understand from your question you need to manipulate the data in a table by knowing/unknowing the record set number.
If that is then you need to reform the mentality of how to access data in a table, because to promote the record set number in your list is not the quite right way, usually we promote the data and the record numbers are hidden.
So when you read your table try to pass your fields into variables for later use or pass them directly to your list view.
The way of accessing the table to get each data it comes from another process which always varied.
But even when you want to keep the record numbers for later use try to declare a name as public variable as ArrayList()then when you read from table you may use the 'Variable'.add(RecordNumber).
So when you need to access a particular number take the reading line number from your list view by calling VariableName(ListViewLineNumber)Please inform me if this solution comes closely to you issue solution.

好吧，如果我从您的问题中了解到您需要通过知道/不知道记录集编号来操作表中的数据。
如果是这样，那么你需要改变如何访问表中数据的心态，因为在你的列表中提升记录集编号不是很正确的方法，通常我们提升数据并且记录编号是隐藏的。
因此，当您阅读表格时，请尝试将您的字段传递给变量以供以后使用或将它们直接传递给您的列表视图。
访问表以获取它来自另一个始终变化的进程的每个数据的方式。
但是，即使您想保留记录编号以备后用，也请尝试将名称声明为公共变量，因为ArrayList()当您从表中读取时，您可以使用'Variable'.add(RecordNumber).
因此，当您需要访问特定号码时，VariableName(ListViewLineNumber)请通过调用从列表视图中获取阅读行号请通知我此解决方案是否与您的问题解决方案非常接近。