CODEWITHSUNDEEP
ms-accessvbams-access-2007ms-access-2010
Masoud
Masoud

Reputation: 1

How to loop an update query on all records of a table

I've set a database which has a table in which there are 2 fields "Account" and "Total Accounts". I want to have the amount of total summation of accounts in "Total Accounts" field of each record, which is the result of summation of "Account" values in all previous records till the current one. In order to do this purpose, I copied the value of "Amount" field of each record into "Total Accounts" field of the same record, at first. Then, I tried to add the amount of "Total Accounts" field of every record with just the amount of "Total Accounts" of previous one to earn the actual total amount of that record. I found that I need a VBA loop to do this query for all records (except first record) and so I code it as below, but it has the "Run-time error '424' : Object required", and it seems that I am in a mistake in assignment of strSQL variable.

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

Upvotes: 0

Views: 2910

Answers (1)

citizenkong
citizenkong

Reputation: 679

You've included your iterator i in the literal string of SQL, i.e.

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

rather than

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

You need to build a string containing your i variable, i.e.

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

Upvotes: 0

Related Questions