VBA/Access RecordSet每表单记录问题

发布于 2024-08-05 08:37:13 字数 726 浏览 14 评论 0原文

我对 VBA 和 Access 不太熟悉，在尝试使用我提出的另一个问题（Access 中的 DLookup 直到在表单中单击 textBox 后才运行）

下面的代码运行，问题是 Me.Key 是表单中显示的每条记录都不同，在表单打开事件中运行它意味着它仅获取第一条记录中分配给 Me.Key 的第一个值。我如何才能运行此操作，以便 Me.Key 对于显示的每条记录/行都不同？

Dim rs As DAO.Recordset
Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("[MF INCOME - STREAM MONTHLY]")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
rs.FindFirst "[MyMonth]=10 AND [Org_Type]='" & Me.Key & "'"
Me.Oct = rs!SumVal
'...other month assignments

原文

I'm new to VBA and Access in general and ran into this problem whilst trying to use the proposed alternate implementation from another question I'd asked (DLookup in Access not running until textBox clicked on in Form)

The code below runs, the issue is that Me.Key is different for each record being displayed in the form and running this in the form open event means it grabs only the first value assigned to Me.Key from the first record. How can i make this run so that Me.Key is different for each record/line being displayed?

Dim rs As DAO.Recordset
Dim db As Database
Dim qdf As QueryDef
Dim prm As Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("[MF INCOME - STREAM MONTHLY]")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
rs.FindFirst "[MyMonth]=10 AND [Org_Type]='" & Me.Key & "'"
Me.Oct = rs!SumVal
'...other month assignments

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

和影子一齐双人舞 2024-08-12 08:37:13

我猜 Me.Key 是指位于表单详细信息部分的控件。在这种情况下，为了列出控件采用的所有值，您将需要浏览所有记录。这样做的方法之一是：

Dim m_position as Long
for m_position = 1 to Me.recordset.recordcount
   me.seltop = m_position
   debug.print me.key
next m_position

不幸的是，您在浏览所有行时会看到屏幕闪烁。当然，您可以在网上找到一些用于 VBA 的“screenFreezer”实用程序（据我所知，有一个名为 LockWindowUpdate）。

另一种解决方案是浏览基础记录集的克隆（浏览记录集将引发与以前相同的屏幕行为）。假设 Me.Key 控件绑定到记录集的“Key”列，代码可以是：

Dim rsClone as DAO.recordset
set rsClone = Me.recordsetclone
if rsClone.EOF and rsClone.BOF then
Else
    rsClone.moveFirst
    Do while not rsClone.EOF
        debug.print rsCLone.fields("Key")
        rsClone.moveNext
    Loop
Endif
set rsClone = nothing

我最喜欢的是第一个，添加了“freeze”选项。您的代码可以管理表单的 seltop 和 selheight 值。这意味着您可以专门浏览用户选择的记录和/或在浏览完所有记录后返回到原始记录选择。

编辑：

根据@Ben的评论，我要补充一点，如果您的“myControl”控件位于详细信息部分并且未绑定，那么您将无法管理每行一个值。当表单显示为“连续”时，该控件的所有行都将具有相同的值。

如果“myControl”控件绑定到记录集的“myField”字段，则以下任何代码都将同时递增“myControl”控件值和“myField”字段值。您将能够在每一行上拥有不同的值：

解决方案 1：

Dim m_position as Long
for m_position = 1 to Me.recordset.recordcount
   me.seltop = m_position
   me.controls("myControl") = m_position
next m_position

解决方案 2：

Dim rsClone as DAO.recordset, _
    i as long

set rsClone = Me.recordsetclone
if rsClone.EOF and rsClone.BOF then
Else
    rsClone.moveFirst
    i = 1
    Do while not rsClone.EOF
        rsClone.fields("myField") = i
        rsClone.update
        rsClone.moveNext
        i = i+1
    Loop
Endif
set rsClone = nothing

I guess the Me.Key refers to a control located in the details section of your form. In this case, and in order to list all values taken by the control, you will need to browse all the records. One of the ways to do so can be:

Dim m_position as Long
for m_position = 1 to Me.recordset.recordcount
   me.seltop = m_position
   debug.print me.key
next m_position

Unfortunately your will see your screen blincker while browsing all the lines. You can off course find some 'screenFreezer' utilities for VBA on the net (there is one called LockWindowUpdate, as long as I can remember).

Another solution is to browse the clone of the underlying recordset (browsing the recordset will provoke the same screen behaviour as before). Supposing that the Me.Key control is bound to the "Key" column of the recordset, code could be:

Dim rsClone as DAO.recordset
set rsClone = Me.recordsetclone
if rsClone.EOF and rsClone.BOF then
Else
    rsClone.moveFirst
    Do while not rsClone.EOF
        debug.print rsCLone.fields("Key")
        rsClone.moveNext
    Loop
Endif
set rsClone = nothing

My favorite is the first one, with the "freeze"option added. Your code can manage the seltop and selheight values of the form. This means you can browse specifically records selected by users and/or, once all records browsed, go back to the original record selection.

EDIT:

Following @Ben's comment, I shall add that if your "myControl" control is in the details section and is unbound, you then will not be able to manage one value per row. The control will have the same value for all lines when the form is displayed as "continuous".

If your "myControl" control is bound to the "myField" field of a recordset, any of the following codes will increment "myControl" control value and "myField" field value at the same time. You will be than able to have a different value on each row:

Solution 1:

Dim m_position as Long
for m_position = 1 to Me.recordset.recordcount
   me.seltop = m_position
   me.controls("myControl") = m_position
next m_position

Solution 2:

Dim rsClone as DAO.recordset, _
    i as long

set rsClone = Me.recordsetclone
if rsClone.EOF and rsClone.BOF then
Else
    rsClone.moveFirst
    i = 1
    Do while not rsClone.EOF
        rsClone.fields("myField") = i
        rsClone.update
        rsClone.moveNext
        i = i+1
    Loop
Endif
set rsClone = nothing

回复收藏 0 原文

泪痕残 2024-08-12 08:37:13

您可以按照之前的建议尝试表单的当前事件:)

回复收藏 0 原文

黄昏下泛黄的笔记 2024-08-12 08:37:13

根本不清楚您在该查询中需要什么参数。我建议您简单地构建一个不带任何参数的查询，其中包含您需要的所有列。

然后根据这个查询构建小表单。然后，您可以将此小表单放入现有表单中，并根据键值设置显示多个数据字段。（只需确保您设置了子表单的链接主设置和子设置）。表单如下所示：

_{（来源：shaw.ca）}

因此，上面的账单根据客户 ID 显示客户信息，并且是发票中的相关表。

换句话说，要根据另一个表中的键值显示多个数据字段，您不需要编写一行代码。因此，您的整个过程和目标可以通过鼠标拖放来完成。我经常有发票或采购订单之类的东西，但我只有客户 ID。通过使用子表单，我可以显示整个地址和多个数据字段，而无需编写任何代码。当您从一条记录移动到另一条记录时，这整组字段将更新并始终显示正确的相关数据。