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

I have an expenditures subform in Access 2010 that lists the predicted costs associated with the project for each year. Most projects only have one year, but some have more than one. Each cost has a Final checkbox next to it that should be checked when the amount is confirmed, ie. at the end of each year.

我在Access 2010中有一个支出子表单,其中列出了与每年项目相关的预测成本。大多数项目只有一年,但有些项目不止一个。每个成本旁边都有一个Final复选框,应该在确认金额时进行检查,即。在每年年底。

It basically looks something like this:

它基本上看起来像这样:

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

I have another field outside the table, FinalCost, that adds up everything in the Cost field. Right now, it fills in the amount from any year which has a checked Final box. That should only be filled when all the Final boxes are checked.

我在桌子外面有另一个字段FinalCost,它在Cost字段中添加了所有内容。现在,它填写了任何一年有一个已检查的最终框的金额。只有在选中所有最终框后才能填写。

Ex. Right now, it should show nothing even though Final for 2017 is checked. When 2018 is checked, it should show $300. Instead, it shows $100 even though there's still an empty checkbox.

防爆。现在,即使选中2017年的Final,它也应该没有显示任何内容。选中2018后,它应显示300美元。相反,它显示100美元,即使仍然有一个空的复选框。

This is the code for this form.

这是此表单的代码。

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

What would be the best way to go about doing this?

这样做最好的方法是什么?

EDIT: When the last box is checked, a new row is automatically added with an untoggled checkbox but no information.

编辑:选中最后一个框时,会自动添加一个新行,其中包含一个未锁定的复选框但没有信息。

4 个解决方案

#1

Replace the statement beginning with sql = ... with this:

用以下代码替换以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 & "'"

This query will return data only if there are all expeditures for the project marked as final. As you check for rs1.RecordCount > 0 there will be no update if this query returns no records.

仅当项目的所有项目都标记为最终时,此查询才会返回数据。当您检查rs1.RecordCount> 0时,如果此查询未返回任何记录,则不会更新。

#2

So, before sql, I would verify that all records have True in your Final field.

所以,在sql之前,我会验证所有记录在Final字段中是否为True。

To do that, let's just return a COUNT() of (any) records that have Final = False, and we can then decide to do what we want.

要做到这一点,让我们只返回一个COUNT()的(任何)具有Final = False的记录,然后我们就可以决定做我们想要的了。

So, something like,

所以,像,

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

To use a query, we essentially need to replicate the Dcount() functionality. To do this, we need another Recordset variable, and we need to check the value of the Count() field from our query.

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

Create a query that mimicks this:

创建一个模仿这个的查询:

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

Save it, and remember that query's name.

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

Much like the DCount(), we need to make this "check" determine the route of your code.

与DCount()非常相似,我们需要通过“检查”来确定代码的路径。

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

#3

Change this:

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

For this:

"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 will return two values, the total value if all costs were confirmed in the rs1!Confirmed, and the value yet to confirm in rs1!YetToConfirm

rs1将返回两个值,如果所有成本都在rs1中确认,则返回总值!确认,并且值尚未在rs1中确认!YetToConfirm

Then here:

Do While Not rs2.EOF rs2.Edit rs2!FinalExpenditure = rs1!Final rs2.Update rs2.MoveNext Loop

做的不是rs2.EOF rs2.Edit rs2!FinalExpenditure = rs1!Final rs2.Update rs2.MoveNext Loop

change it to:

将其更改为:

Do While Not rs2.EOF rs2.Edit rs2!FinalExpenditure = Iif(rs1!YetToConfirm = 0, rs1!Confirmed, 0) rs2.Update rs2.MoveNext Loop

做的不是rs2.EOF rs2.Edit rs2!FinalExpenditure = Iif(rs1!YetToConfirm = 0,rs1!确认,0)rs2.Update rs2.MoveNext Loop

#4

One way to process this would be check using a subquery whether last year(verified using a dmax function) in each project has been checked in the final column, if this is true, get your sum of checked amounts, else dont calculate the sum.
I have modified your sql string to include this and I tested it against your given example to confirm its showing a sum of $300 or nothing.

处理此问题的一种方法是使用子查询检查是否在最后一列中检查了每个项目中的去年(使用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 "

#1