如何使用 DAO.Recordset 使用不可更新的查询来更新表 [英] How to use DAO.Recordset to update a table using a non-Updateable Query

问题描述

我有一个相当小的表 tblFunding 有 20 条记录.我想使用不可更新的查询结果更新它的字段 Payment，即:

I have a fairly small table tblFunding with 20 records. I want to update its field Payment using results of a non-updateable query ie:

"SELECT UserName, Sum([Payment]) As Payment FROM tblFundingMain WHERE (((DateDiff('m',[PaymentDate],DateSerial(Year(Date()),1,1)))介于 -7 和 4)) GROUP BY UserName")我知道存储这种类型的数据是不好的做法，但用户希望不时浏览一下数据，因为为了方便起见，表格绑定到表单.我们提出了一种使用 DAO 的方法，该方法有效，但它为更新表中的 UserName 字段不存在的记录留下空值，即 tblFunding.如果字段不匹配，我们希望值为 0.代码在表单打开之前运行，这意味着在表单启动之前更新表.有没有办法礼貌地完成这项任务?请查看下面的代码，并尽可能提供建议.谢谢！

"SELECT UserName, Sum([Payment]) As Payment FROM tblFundingMain WHERE (((DateDiff('m',[PaymentDate],DateSerial(Year(Date()),1,1))) Between -7 And 4)) GROUP BY UserName") I know it is bad practice to store this type of data but the user wants to have a glance at the data from time to time since the table is bound to a form for his convenience. We came out with a method that uses DAO which works but it leaves nulls for records that do not exist for UserName field in the updated table i.e tblFunding. We would prefer the value to be 0 in case the fields do not match. The code runs before the form opens which means the table is updated before the form is launched. Is there a way to politely accomplish this task? Please see the code below and advise wherever you can. Thank you!

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.

Could run an UPDATE action to change all Null to 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().

Alternative to display this summary data on form could use domain aggregate function. Build a query object that does the summation then use DLookup to pull a particular value. Or use DSum() directly on source table.

如果只是执行一个 JOINED 过滤求和查询到所有用户名数据集的查询，就可以避免所有这些代码.

Could avoid all this code if just did a query that JOINED filtered summation query to dataset of all user names.

这篇关于如何使用 DAO.Recordset 使用不可更新的查询来更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！