Calculating Totals, Subroutine or Module?

[DeeJay Johnson]

I need to calculate Invoice Balances for each Payment made. Here's the field names:

INITBALANCE 'Original Invoice Total $$$
PAYMENT1 BALANCE1
PAYMENT2 BALANCE2
PAYMENT3 BALANCE3
PAYMENT4 BALANCE4

I need ALL Balances updated anytime ANY Payment is made OR changed. For example, the code for updating ALL Balances when PAYMENT1 is made OR changed is:

Private Sub PAYMENT1_AfterUpdate()
 
BALANCE1 = INITBALANCE - PAYMENT1
BALANCE2 = BALANCE1 - PAYMENT2
BALANCE3 = BALANCE2 - PAYMENT3
BALANCE4 = BALANCE3 - PAYMENT4
 
End Sub

Now, I can repeat this code for PAYMENT2, PAYMENT3 and PAYMENT4 AfterUpdates, but there's gotta be a better way. I think this is a candidate for a Module, but not sure how to write it or call it. Can you code this example as a module and send it to me, along with whatever parameters, etc. I need to call it? I have not worked with Modules before...

Thanks......dave

[Rabbit]

Don't store the balance. You don't need to, you can just calculate it.

Normalize the payment data.

Payments table

AccountID | Payment
123456789 | -100
123456789 | 10
123456789 | 20
123456789 | 50

Then I can just get the balance using a query

SELECT AccountID, SUM(Payment) AS BALANCE
FROM Payments
GROUP BY AccountID

[ADezii]

In its simplest state, the logic would consist of a Private, Form-Level, Sub-Routine Procedure that Recalculates all Balances after the AfterUpdate() Event of each Payment Text Box, as in:

1. Sub-Routine Definition in Form:
   

   Private Sub UpdateBalances()
     Me![Balance1] = Me![InitBalance] - Me![Payment1]
     Me![Balance2] = Me![Balance1] - Me![Payment2]
     Me![Balance3] = Me![Balance2] - Me![Payment3]
     Me![Balance4] = Me![Balance3] - Me![Payment4]
   End Sub
    

2. Calls to the Sub-Routine from the AfterUpdate() Events of each Payment Text Box:
   

   Private Sub Payment1_AfterUpdate()
     Call UpdateBalances
   End Sub

   Private Sub Payment2_AfterUpdate()
     Call UpdateBalances
   End Sub

   Private Sub Payment3_AfterUpdate()
     Call UpdateBalances
   End Sub

   Private Sub Payment4_AfterUpdate()
     Call UpdateBalances
   End Sub

[ADezii]

@Dave:
After seeing Rabbit's Reply, I'm beginning to think that I was way off base on this one. I assumed that you wanted a visual representation, showing each Balance for the corresponding Payment. Please clarify, Dave.

P.S. - Balance needs not to be stored as indicated by Rabbit, but still can be displayed via an Unbound Text Box.

[NeoPa]

DeeJay Johnson:
, but there's gotta be a better way. I think this is a candidate for a Module,

I think you're right here DJ.

I'm not sure if the number of fields in your table is fixed (at 4) or if maybe it's possible to have an unlimited number of payments for a balance. Whichever way it works, you really are much better advised (unless you have exigent circumstances of which we are unaware) to store only the initial balance and the various payments. A query can very easily determine the other balances on the fly if it's important to display these with the other data (or they can even be determined on the Form/Report itself as ADezii points out), but it shouldn't be necessary ever to store these types of determinable values.

This is what Rabbit was referring to when he talked about 'normalising' your data. Here's a link (Database Normalisation and Table structures) to a relatively short article that explains this concept quite clearly. It's probably the most important concept you need to understand if ever you're to work with databases (RDBMS).