如何在表中的所有记录上循环更新查询

Question

我已经设置了一个数据库，其中有一个表中有两个“Account”和“Total Accounts”字段。我想要在每个记录的“总帐户”字段中拥有帐户总额的总和，这是以前所有记录中的“帐户”值总和到当前记录的总和的结果。为了达到这个目的，我首先将每条记录的“金额”字段的值复制到同一记录的“总帐户”字段中。然后，我尝试在每个记录的“总帐户”字段中添加前一个记录的“总帐户”金额，以获得该记录的实际总金额。我发现我需要一个VBA循环来做所有记录的查询（除了第一条记录），所以我把它编码为如下，但它有“运行时错误'424'：Object required”，似乎我我在分配strSQL变量时出错了。

Private Sub doDataSegm_Click() 


Dim dbs As DAO.Database 
Dim rs As DAO.Recordset 
Dim strSQL As String 

Set dbs = CurrentDb() 
Set rs = dbs.OpenRecordset("Table1", dbOpenTable) 

If (rs.RecordCount <= 1) Then Exit Sub 

rs.MoveFirst 
rs.MoveNext 

For i = 2 To rs.RecordCount 
    strSQL = "UPDATE Table1, Table1 as copyTable Set Table1.[Total Accounts] = Table1.[Total Accounts] + [copyTable].[Total Accounts] Where Table1.ID = i and copyTable.ID = i-1" 

db.Execute strSQL, dbFailOnError 
rs.MoveNext 
Next i 


End Sub 

Answer 1

你已经包括你的迭代i在SQL的文本字符串，即

"UPDATE Table1, Table1 as copyTable Set Table1.[Total Accounts] = Table1.[Total Accounts] + [copyTable].[Total Accounts] Where Table1.ID = i and copyTable.ID = i-1"

而不是

"UPDATE Table1, Table1 as copyTable Set Table1.[Total Accounts] = Table1.[Total Accounts] + [copyTable].[Total Accounts] Where Table1.ID = 2 and copyTable.ID = 2-1"

你需要建立包含字符串你i变量，即

"UPDATE Table1, Table1 as copyTable Set Table1.[Total Accounts] = Table1.[Total Accounts] + [copyTable].[Total Accounts] Where Table1.ID = " & i & " and copyTable.ID = " & i-1