我有一张相当小的桌子tblFunding
有20条记录。我想更新它的字段Payment
使用不可更新查询的结果,即:
"SELECT UserName, Sum([Payment]) As Payment FROM tblFundingMain WHERE (((DateDiff('m',[PaymentDate],DateSerial(Year(Date()),1,1))) Between -7 And 4)) GROUP BY UserName")
我知道存储此类数据是不好的做法,但用户希望时不时地看一下数据,因为为了方便起见,该表已绑定到表单。
我们想出了一个方法,使用DAO
它有效,但它为不存在的记录留下空值UserName
更新表中的字段,即tblFunding
。我们希望该值是0
如果字段不匹配。该代码在表单打开之前运行,这意味着表在表单启动之前更新。有没有办法礼貌地完成这项任务?请参阅下面的代码并尽可能提供建议。谢谢你!
Private Sub btnGlance_Click()
Dim rs1 As DAO.Recordset
Dim rs 2 As DAO.Recordset
Set rs1 = CurrentDb.OpenRecordset("SELECT UserName, Sum([Payment]) As Payment FROM tblFundingMain WHERE (((DateDiff('m',[PaymentDate],DateSerial(Year(Date()),1,1))) Between -7 And 4)) GROUP BY UserName")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM tblFunding")
rs1.MoveFirst
Do Until rs1.EOF
rs2.MoveFirst
Do Until rs2.EOF
If rs1.Fields("UserName") = rs2.Fields("UserName") Then
rs2.Edit
rs2.Fields("Payment").Value = rs1.Fields("Payment").Value
rs2.Update
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Docmd.OpenForm "frmUserGlance"
End Sub
可以运行 UPDATE 操作将所有 Null 更改为 0。
CurrentDb.Execute "UPDATE tblFunding SET Payment = 0 WHERE Payment Is Null"
或者考虑替代代码:
rs2.MoveFirst
Do Until rs2.EOF
rs1.MoveFirst
rs1.FindFirst "UserName = '" & rs2!UserName & "'"
rs2.Edit
If Not rs1.NoMatch Then
rs2!Payment = rs1!Payment
Else
rs2!Payment = 0
End If
rs2.Update
rs2.MoveNext
Loop
在表单上显示此摘要数据的替代方法可以使用域聚合函数。构建一个进行求和的查询对象,然后使用 DLookup 提取特定值。或者直接在源表上使用 DSum()。
如果只是执行一个将过滤求和查询连接到所有用户名数据集的查询,则可以避免所有这些代码。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)