Cumlative balance in MS Access Form

[Joe1991]

Hi,

I am trying to create a Form which would display the following columns:

Date, Description of transaction, Debit, Credit, Net, Balance.

For example:

Date Description Debit Credit Net Balance
18/08/2010 Cash Withdrawal -£10.00 £0.00 -£10.00 -£10.00
19/08/2010 Deposit £20.00 £0.00 £10.00

I am struggling to create the final "Balance" column.

What I need is a formula or expression along the lines of "Previous Balance Value - Debit + Credit"

I created this sucessfully in a MS Works database but I have struggled to create a similar Form in MS Access.

I need this in a Form as it would allow me to see the balance column when I am inputing anythign in the Date, Description, Debit, Credit columns.

I would appreciate any help

Thanks!

 

[timf79]

My first thought is that you need VBA code that runs after every line entered and posts the value hardcoded into a table.

Menaing you enter row one, then trigger the vba code to calculate the current balance, post it into a field.
you enter the next row and once you are done you triger vba again...

the problem will be when importing records, in this case I would import in excel, calculate the balances and then go from there.

 

[GohDiamond]

One way that worked for me in the past was:

Put your Formula in the Footer of the form using textboxes to do SUM of Debits and Credits and one that does your formula.
SUMofDebits:= Sum([DebitFieldName])
SUMofCredits:= Sum([CreditFieldName])
Balance: = (SumofDebits + SumofCredits)

Cheers!
Goh

 

[timf79]

It really depends if you wan to have a running balance.

If so you could:
- Take the data into a recordset
- Make sure that recordset is sorted by date and time
- go row for row in the recordset to calculate the running balance
- write the running balance in the table

 

[Joe1991]

Thanks for both of your replies.

Goh - I have tried your suggestion but I would like a running cumulative balance after each record.

I would appreciate any further suggestions!

 

[timf79]

Joe,

did you try VBA code using the ADO method as described above?

What is the problem with that?

 

[Joe1991]

Timf79,

Goh's suggestion returns something like this:

Date Description Debit Credit Net Balance
13/03/2011 Cash -10.00 0 -10.00 0
14/03/2011 Deposit 0 10.00 10.00 0

But I need it to look like this:

Date Description Debit Credit Net Balance
13/03/2011 Cash -10.00 0 -10.00 -10.00
14/03/2011 Deposit 0 10.00 10.00 0.00

As in the balance is different for each record.

I'm afraid I don't know how to create code, so I couldn't try your suggestion.

I would happily try your suggestion, if you would be kind enough to provide the code?

Many thanks in advance!

 

[Joe1991]

Timf79,

Goh's suggestion returns something like this:

Date Description Debit Credit Net Balance
13/03/2011 Cash -10.00 0 -10.00 0
14/03/2011 Deposit 0 10.00 10.00 0

But I need it to look like this:

Date Description Debit Credit Net Balance
13/03/2011 Cash -10.00 0 -10.00 -10.00
14/03/2011 Deposit 0 10.00 10.00 0.00

As in the balance is different for each record.

I'm afraid I don't know how to create code, so I couldn't try your suggestion.

I would happily try your suggestion, if you would be kind enough to provide the code?

Many thanks in advance!