I am trying to create a datasheet of multiple items form with three separate running balance columns.
In the past (with only one balance column), I have provided a field in the table. In VBA, I used a SQL to open a recordset then moved to the first record and computed the beginning balance based on the data in the first record and updated the recordset (and table) with the balance as of the first record. Then I moved to the next record and computed the balance based on the balance in the previous record plus and minus the amounts in the second record and updated the recordset (and table) with the balance, and on and on through the last record. This works well but it means that I must store a computed field. I don’t like to do this even though this is a personal database and the size of the database is small.
Could I have some suggestions for a better way to do this? I don’t know how to update each row of a form separately using a function or SQL. I’ve considered using a temporary table but that seems to be a workaround not a solution.