Cumulative totals: Cannot edit a field in recordset

[Persist]

I want to calculate the cumulative totals for each account in my database

I created a query qCumTot with four fields
Account: text field sort ascending from another table
Date:
Income: Currency from another table
CumTot: Created in this query and set to set = 0

I intended to overwrite CumTot using visual basic
so I could see the results in the output of the query

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("qCumTot")



Looping thorough record set I can calculate CumTotal
But I cannot overwrite rs![CumTot}

rs.Edit
rs![CumTot] = CumTotal
‘ Gives Error 3164:
‘ Field cannot be updated, another process has locked record or table
rs.Update

Any suggestions

[June7]

Saving calculated aggregate data to table is usually poor design and problemmatic as you found out. The aggregate total should be calculated when needed. This would be a Totals (GROUP BY) query.

Can build a report with grouping on account and textboxes with Sum function. This will allow aggregate calcs and display of detail records.

[Persist]

[Saving calculated aggregate data to table is usually poor design
*** Yes I tried to avod this by saving to a query. Is that poor design?

and problemmatic as you found out.
***** Yes. It did not work. Can I get it to work.
I would like to calculate running totals so I can more easily check against my bank statements

The aggregate total should be calculated when needed. This would be a Totals (GROUP BY) query.
*** I have done GROUP BY overall totals for each account. This gives me the current balance for each account but not how the balance has moved with each transaction

Can build a report with grouping on account and textboxes with Sum function. This will allow aggregate calcs and display of detail records.
** Could you say more about this

Thanks

[June7]

Review Access Help on building reports that use grouping & sorting and aggregate calcs. No sense in me trying to repeat the help manual.

Here is a web tutorial: http://office.microsoft.com/en-us/ac...aspx?section=5

Textboxes in report offer a RunningSum property.

You don't 'save' to a query. Queries manipulate data, an UPDATE query can even modify values in table. Data can even be entered into an editable SELECT query and the values will pass through to the source tables.

[Persist]

You wrote "Textboxes in report offer a RunningSum property."
***** This was GOLD
I googled “access report runningsum property”
I found detailed instruction
This worked – an easy way to get cumulative totals

You wrote
You don't 'save' to a query. Queries manipulate data, an UPDATE query can even modify values in table. Data can even be entered into an editable SELECT query and the values will pass through to the source tables.
*** I meant that I defined a new field in my query and set it to zero in this query so access would know it was a numeric field. Then I attempted to overwrite this field with my VBA calculated cumulative total. This overwriting was not allowed.
It does not matter now. The report running sum does the trick.

Thanks