每个表单记录的VBA/Access RecordSet问题 [英] VBA/Access RecordSet per form record problem

问题描述

我是VBA和Access的新手，在尝试使用我问过的另一个问题的建议替代实现时遇到了这个问题(

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)

下面的代码运行，问题在于Me.Key对于表单中显示的每个记录都是不同的，并且在表单打开事件中运行它意味着它仅从第一条记录中获取分配给Me.Key的第一个值.我该如何运行，以使Me.Key对于正在显示的每个记录/行都是不同的?

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

推荐答案

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

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

不幸的是，在浏览所有行时，您会看到屏幕闪烁.当然，您可以在网上找到一些用于VBA的"screenFreezer"实用程序(只要我记得，就有一个叫做LockWindowUpdate的实用程序).

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

另一种解决方案是浏览基础记录集的克隆(浏览记录集将引起与以前相同的屏幕行为).假设Me.Key控件绑定到记录集的"Key"列，则代码可能是:

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

我的最爱是第一个，其中添加了冻结"选项.您的代码可以管理表单的seltop和selheight值.这意味着您可以专门浏览用户选择的记录，并且/或者在浏览完所有记录后，返回到原始记录选择.

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.

在@Ben的评论之后，我要补充一点，如果您的"myControl"控件在详细信息部分中并且未绑定，则您将无法管理每行一个值.当表单显示为连续"时，控件的所有行都将具有相同的值.

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

如果"myControl"控件绑定到记录集的"myField"字段，则以下任何代码将同时增加"myControl"控件值和"myField"字段值.您将能够在每一行上使用不同的值:

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:

解决方案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

这篇关于每个表单记录的VBA/Access RecordSet问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！