Show Running Balance on Data Entry Form

[hawzmolly]

I have a form that updates to my table and I would like to have the form show my new balance after data entry. The table contains all of the accounts that are credits and/or debits.

My table was created from an Excel file that has totals for all credits and debits as well as my previous balance.
Example:
Balance from last entry = $1000
New Credits (+ #) = $ 200
New Debits (- #) = $ 300 shown as ($300)
New Balance = $ 900

I was successful in creating the Access Table. However, as you know the formulas are not created in Access.

I used "Got Focus Event" on my data entry form to add all of the credit fields and Debit fields with no problem. The codes are -



Private Sub Credit_GotFocus()
Credit = [BS] + [BFD] + [Dues] + [DON] + [CSMS] + [TAB]
End Sub

and

Private Sub Debit_GotFocus()
Debit = [WAT] + [Rent] + [WPS] + [EHS] + [OPOX]
End Sub

Now I want to show my running balance which would be - [Old Balance] + [New Credits] + [New Debits] = [New Balance].

Then, next entry becomes [Old Balance] + [New Cred...], well, I think you get the picture.

Private Sub NewBalance_GotFocus()
NewBalance = [Credit] + [Debit] + [????]
End Sub

Where [????] equals the previous balance.

I can get the Credits and Debits added ok but how do I get the previous balance into the VBA. In my example I have reduced the balance by $100 (Credits + Debits = -$100)

I am not a complete noob but I am pretty close.

Any help please.

[alansidman]

I do this a little differently. I ignore the last balance and have a starting balance record in the table as either your debit or credit.

In the footer of your form, create an unbound text box and place a formula similar to this:

=DSum("Debits","tblName")-DSum("Credits","tblName")

This will sum all debits and and credits and subtract the credits from the debits. I use this in a checkbook I have developed. If you need to see the checkbook, I can attach a template.

Alan

[hawzmolly]

I think I'll try this. Please attach. Thanx

[alansidman]

See attached. You will need to download both files and place them both in the same folder.

Good luck and post back with any questions you have.

[hawzmolly]

Exactly what I needed. Problem solved Thanx again!

[Nadeem148]

great work sir
but how can we show every customer balance individually or separately?