vba 如何在 Access 连续表单中模拟组头？

How to simulate a Group Header in an Access Continuous Form?

提问by PowerUser

I have a form (nota report) in Access displayed in Continuous View that has alot of repeat data. i.e. it looks like:

我在 Access 中有一个表单（不是报表）显示在连续视图中，其中包含大量重复数据。即它看起来像：

StateNames
FLAbe
FLBart
FLCharlie
GADeleanor
GAMary

玛丽 州Names
FLAbe
FLBart
FLCharlie
GADeleanor
GA

This needs to be interactive (there's some command buttons on each row as well as in the form header), but there's alot of repetitive data. Is there a way to add a group header by state? i.e. make it look like:

这需要是交互式的（每一行以及表单标题中都有一些命令按钮），但是有很多重复的数据。有没有办法按状态添加组标题？即让它看起来像：

StateNames
FL
Abe
Bart
Charlie
GA
Deleanor
Mary

玛丽 州Names
FL
Abe
Bart
Charlie
GA
Deleanor


采纳答案by HansUp

The closest I can get is to add a field to your form's record source which numbers each row within a group. Then you could add a text box in the detail section of your form with this expression as its control source.

我能得到的最接近的是向表单的记录源添加一个字段，该字段为组中的每一行编号。然后，您可以使用此表达式作为其控件源，在表单的详细信息部分添加一个文本框。

=IIf(rank = 1, [State], ""]

=IIf(rank = 1, [State], ""]

The text box would display the first occurrence of each state, and a zero length string otherwise.

文本框将显示每个状态的第一次出现，否则显示零长度字符串。

I tested with this query for the record source.

我使用此查询对记录源进行了测试。

SELECT
    m1.state,
    m1.names,
    (
        SELECT Count(names)
        FROM MyTable AS m2
        WHERE
            m2.state=m1.state
            AND m2.names<=m1.names
    ) AS rank
FROM MyTable AS m1
ORDER BY m1.state, m1.names;

And it worked, but is not editable. If you need to be able to edit, try a query which uses DCount to generate the rank.

它有效，但不可编辑。如果您需要能够进行编辑，请尝试使用 DCount 生成排名的查询。

SELECT
    m1.state,
    m1.names,
    DCount("names", "MyTable",
    "state ='" & state & "' AND " & "names <= '" & names & "'") AS rank
FROM MyTable AS m1
ORDER BY m1.state, m1.names;

Both those queries give this result set with your sample data:

这两个查询都给出了带有您的示例数据的结果集：

state names     rank
FL    Abe       1
FL    Bart      2
FL    Charlie   3
GA    Deleanor  1
GA    Mary      2

Note, I assumed unique combinations of state and name.

注意，我假设了 state 和 name 的独特组合。

回答by Damon

I found a number of problems with the solutions posted. Most obviously there are issues (as noted) if there are duplicate entries. But I found there was a deeper issue which is that you can't do this and still sort the data on yet another field (suppose you wanted to sort by the date by which each name was first registered in each state, and yet NOT rank them that way).

我发现发布的解决方案存在许多问题。最明显的是，如果有重复的条目，就会出现问题（如上所述）。但是我发现有一个更深层次的问题，那就是你不能这样做并且仍然对另一个字段的数据进行排序（假设你想按照每个名字在每个州首次注册的日期进行排序，而不是排名他们那样）。

A better solution is found in the Using Code section of the Microsoft KB:

在 Microsoft KB 的 Using Code 部分中找到了更好的解决方案：

http://support.microsoft.com/kb/q101081

http://support.microsoft.com/kb/q101081

The code from that section retrieves the actual data from the Previous field, in whatever sort order you define.

该部分的代码以您定义的任何排序顺序从 Previous 字段中检索实际数据。

Computationally this can be a bit intense, so it works only for small datasets, but I achieved good results by first testing the function:

在计算上这可能有点密集，因此它仅适用于小型数据集，但我通过首先测试该函数取得了良好的结果：

=PrevRecVal([Forms]![myForm],"ID",[ID],"Names")=[Names])

=PrevRecVal([Forms]![myForm],"ID",[ID],"Names")=[Names])

then putting it inside the IIF function:

然后将其放入 IIF 函数中：

=IIf(PrevRecVal([Forms]![myForm],"ID",[ID],"Names")=[Names],"",[FullName])

=IIf(PrevRecVal([Forms]![myForm],"ID",[ID],"Names")=[Names],"",[FullName])

回答by David-W-Fenton

Have you looked at subdatasheets? If you have a state table (or just query your existing data for the unique states), you could use that in a non-editable parent datasheet, and then the subdatasheet would display the people data, and you could make it editable.

你看过子数据表吗？如果您有一个状态表（或只是查询现有数据以获得唯一状态），您可以在不可编辑的父数据表中使用它，然后子数据表将显示人员数据，您可以使其可编辑。

I'd implement this with datasheet forms, not with the actual table or query datasheets. This gives you a lot more control over the subdatasheet linking and formatting, as well as making interacting with the datasheet events easier (it can be done with a table or query datasheet, but only through Screen.ActiveDatasheet.

我会用数据表形式来实现这一点，而不是用实际的表或查询数据表。这使您可以更好地控制子数据表链接和格式设置，并使与数据表事件的交互更容易（可以使用表或查询数据表完成，但只能通过 Screen.ActiveDatasheet。