当所有复选框被切换时填写字段 Access 2010。

Question

我在Access 2010中有一个支出子表单，列出了每年与项目相关的预计成本。大多数项目只有一年，但有些项目有多年。每个成本旁边都有一个“完成”复选框，当金额确认时即在每年结束时应该勾选。

基本上看起来像这样：

Year    |   Cost    |   Final
--------+-----------+--------------------
2017    |   $100    |   [checked box]
2018    |   $200    |   [unchecked box]
        |          |   [unchecked box]

我有一个位于表格外的字段FinalCost，它会将Cost字段中的所有内容相加。目前，它会填写任何年份中已选中Final框的金额。只有当所有Final框都被选中时，才应该填写。

例如，现在，即使2017年的Final被选中，它也不应该显示任何内容。当2018年被选中时，它应该显示300美元。但是，实际上，即使仍有一个空复选框，它仍然显示100美元。

这是此表单的代码。

Private Sub Form_AfterUpdate()
Dim rs1, rs2 As Recordset
Dim sql, sql2 As String

sql = "SELECT Sum(Amount) as Final From Expenditures " & _
"Where ProjNo = '" + Me.ProjNo + "' And Final = True Group by ProjNo"

sql2 = "SELECT FinalExpenditure From ActivityCash " & _
"Where ProjNo = '" + Me.ProjNo + "'"

Set rs1 = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dpinconsistent)
Set rs2 = CurrentDb.OpenRecordset(sql2, dbOpenDynaset, dpinconsistent)

If rs1.RecordCount > 0 Then
If rs2.RecordCount > 0 Then
Do While Not rs2.EOF
    rs2.Edit
    rs2!FinalExpenditure = rs1!Final
    rs2.Update
    rs2.MoveNext
Loop
End If
End If

rs2.Close
rs1.Close
Set rs1 = Nothing
Set rs2 = Nothing

End Sub

如何最好地执行此操作？

编辑：当最后一个框被勾选时，将自动添加一行未选中复选框但没有信息。

- AxxieD

---

如果 Count(*) WHERE Final = False > 0，那么不要填充它。 - Mark C.

好的，你说过“只有在所有的最终框都被选中时才应该填写”。所以如果一个或多个记录的Final值为False，那么就不要提供值。 - Mark C.

我应该把它放在哪里？在 sql= 上面吗？ - AxxieD

首先，这听起来是否解决了你的问题？如果是，我会在答案中提供它。 - Mark C.

那看起来可能会做我想要的事情。 - AxxieD

Answer 1

请将以sql = ...开头的语句替换为以下内容：

sql = "SELECT SUM(e1.Amount) AS Final " & _
    " FROM Expenditures AS e1 " & _
    " WHERE NOT EXISTS (SELECT 'x' FROM Expenditures e2 WHERE e2.Final=0 AND e1.ProjNo = e2.ProjNo) " & _
    " AND e1.ProjNo = '" & Me.ProjNo & "'"

只有当项目所有支出都标记为最终状态时，此查询才会返回数据。由于检查了rs1.RecordCount > 0，如果此查询未返回任何记录，则不会进行更新。

- PhilS

---

当我尝试运行这个程序时，它会在 Set rs1 = CurrentDb.OpenRecordset(sql, dbOpenDynaset, dpinconsistent) 处报错："运行时错误 3061：参数过少。期望 1 个。" - AxxieD

这是一个明确的迹象，SQL 中有一个字面值引用了不存在的表字段。请仔细检查您的表 "Expenditures" 中是否存在 "Amount"、"Final" 和 "ProjNo" 字段。 - PhilS

我不知道，我只是将这段代码复制粘贴到原来的代码上，然后它就可以工作了。 - AxxieD

Answer 2

因此，在使用 sql 之前，我建议您验证所有记录的 Final 字段是否为 True。

为了做到这一点，我们可以返回一个具有 Final = False 的（任何）记录的 COUNT()，然后再决定我们想要做什么。

所以，类似于以下内容：

Dim Test as Integer

test = DCount("*", "YourTableName", "Final = False AND ProjNo = " & Me.ProjNo &"")

If test > 0 Then
    'Don't fill the box
Else
    'Fill the box, everything is True
    'Read through your recordsets or whatever else you need to do
End If

为了使用查询，我们需要基本复制Dcount()功能。为此，我们需要另一个Recordset变量，并且需要检查查询中Count()字段的值。

创建一个模拟此功能的查询：

SELECT COUNT(*) As CountTest
FROM YourTable 
HAVING Final = False 
   AND ProjNo = whateverprojectnumberyou'reusing

保存它，并记住查询的名称。

与 DCount() 类似，我们需要使这个 "check" 确定代码的路线。

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourQuery'sNameHere")

If rst!CountTest > 0 Then
    'They are not all Checked (aka True)
    Else
    'Supply the value to the FinalCost
End If
Set rst = Nothing

- Mark C.

---

好的，你说你只想在 test < 1 的情况下填充它，也就是说没有记录为 Final = False。所以我会把所有代码放在 Else 和 End If 之间（我在我的评论中已经说明了）。否则，我会将你的字段值设置为 "" 或者任何你想要的值。 - Mark C.

它仍然给我相同的结果。我将第一部分写成 If Test > 0 Then FinalExpenditure = ""。也许这是错误的？我不是一个很好的 VBA 程序员。 - AxxieD

是的。我想我应该提到这个。有一个 Expenditures 表格，其中包含所有项目的信息。每个单独项目的表格都有一个支出子表格，列出了该项目的支出信息。如果这样说有意义的话。也许我需要指定我正在引用子表格中的表格？ - AxxieD

没错。我们的 DCount 能够工作，但是我们需要进一步缩小范围，以确保我们不会检查那些与我们无关的内容，所以如果是 ProjNo 或其他字段，我们也需要将其添加进来。你认为这样能解决你的问题吗？ - Mark C.

好的，请创建一个类似于 DCount 的查询并运行它。例如，SELECT COUNT(*) FROM YourTable HAVING Final = False AND ProjNo = whateverprojectnumberyou'reusing。 - Mark C.

Answer 3

将此更改：

sql = "SELECT Sum(Amount) as Final From Expenditures " & _
"Where ProjNo = '" + Me.ProjNo + "' And Final = True Group by ProjNo"

对于这个：

"SELECT SUM(Amount) - SUM(IIF(Final,1,0)*Amount) as YetToConfirm, SUM(Amount) as Confirmed From Expenditures " & _
"Where ProjNo = '" + Me.ProjNo + "' Group by ProjNo"

rs1将返回两个值，如果所有成本都在rs1!Confirmed中得到确认，则为总值，如果有待确认的值则为rs1!YetToConfirm。

然后，在这里： Do While Not rs2.EOF rs2.Edit rs2!FinalExpenditure = rs1!Final rs2.Update rs2.MoveNext Loop 将其更改为： Do While Not rs2.EOF rs2.Edit rs2!FinalExpenditure = Iif(rs1!YetToConfirm = 0, rs1!Confirmed, 0) rs2.Update rs2.MoveNext Loop

- Manuel Castro

---

当我尝试运行这个程序时，它告诉我在Set rs1=处出现了“运行时错误2085：表达式中未定义函数'IFF'”。 - AxxieD

是的...我的错，函数应该是IIF而不是IFF...我已经更新了答案以对应。 - Manuel Castro

嗯...即使所有框都被切换，这仍然会使“FinalExpenditures”字段为空。 - AxxieD

rs1!YetToConfirm 和 rs1!Confirmed 分别返回什么？你可以通过单步调试（F8）到这一步 rs2!FinalExpenditure = Iif(rs1!YetToConfirm = 0, rs1!Confirmed, 0) 吗？ - Manuel Castro

它不让我这样做。我设置了断点，但当我运行它时它不停止。 - AxxieD

Answer 4

一种处理方法是使用子查询检查每个项目中去年（使用dmax函数进行验证）是否已在最终列中检查，如果为真，则获取已检查金额的总和，否则不计算总和。
我已修改您的SQL字符串以包括此内容，并根据您提供的示例进行了测试，以确认它显示总和为$300或无内容。

SQL = ""
SQL = SQL & " SELECT Sum(Amount) as Final From Expenditures "
SQL = SQL & " Where ProjNo = '" & Me.ProjNo & "' And Final = True "
SQL = SQL & " And (SELECT Expenditures.Final FROM Expenditures where year = ( "
SQL = SQL & " DMax('Year','Expenditures','ProjNo= " & Chr(34) & Me.ProjNo & Chr(34) & "'))) = true "
SQL = SQL & " Group by ProjNo "

- Avagut