How to loop an update query on all records of a table (1 Viewer)

[macut]

hi everybody...

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 definition 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 = 0 Or 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

 

[pr2-eugin]

Cross posted @StackOverflow : http://stackoverflow.com/questions/25621949/how-to-loop-an-update-query-on-all-records-of-a-table

Please read : http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

 

[jdraw]

macut,

Most articles you'll find on table design will advise you to
Not Store Totals in Tables.

If/When you do the Totalling or other calculation use a query.

 

[macut]

hi every body,

finally I did and correct the code myself and this is my final code which has been tested:

Dim rs As DAO.Recordset
Dim strSQL As String

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

strSQL = "UPDATE Table1 SET Table1.[Total Accounts] = [Account];"
dbs.Execute strSQL, dbFailOnError

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

    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&
      
    dbs.Execute strSQL, dbFailOnError
    Next i
    
End Sub