Calculate remaining balance on continious subform

[AusDBGuy]

Hi,
I've been struggling to figure this out.
I have a main form "frmClient" with a continuous subform ("sfrmMembPayments") which deals with membership payments a client makes on their annual membership.
One year a member might pay in full $50.00 then the next year the client might pay in installments.
If they pay in installments I need a remaining balance each time they make a payment.
For each payment(record) I have a field PaidInFull (yes/no)
I created a nested query in which I can calculate the remaining balance, basically it gets all payments for a specific ClientID where PaidInFull = No and sums the payments field to get a total paid. However for each payment it shows the same balance EG
Line 1 is a payment of $20 balance = $30
Line 2 is a payment of $15 balance = $15

However the balance field for both line 1 and 2 shows $15. I have been doing this using Dlookup. See image

Code Im using is as follows:

Dim totalPaid As Long

totalPaid = DLookup("[Balance]", "qryMembPayBalance", "[mpClientID]=" & [mpClientID])


Me.txtBalance = 50 - totalPaid

Any extra help would be greatly appreciated. Even suggestions on another approach would be helpful.
Thanks in Advance

 

[AusDBGuy]

In the end instead of having a balance for each row I just created a textbox on the subform header and used a query to calculate balance and dlookup to display it in the textbox.

 

[Ari]

Hi

Better you create the calculated field in the query, source subform ..

Balance: 50 - DLookup("[Balance]";"qryMembPayBalance";"[mpClientID]=" & [mpClientID])

 

[AusDBGuy]

Hi

Better you create the calculated field in the query, source subform ..

Balance: 50 - DLookup("[Balance]";"qryMembPayBalance";"[mpClientID]=" & [mpClientID])

Hi Ari,
I had tried that previously I did get it to work however it still doesn't do what I want. See the example below for what balance is displayed and what I need:
Fee is $50

Paid|Balance | WhatINeedTheBalanceToDisplay
20 |50-20=30| 30
10 |50-10=40| 20
10 |50-10=40| 10
10 |50-10=40| 0

So I guess I needed a running balance in which the amount paid is deducted from the previous balance not $50, which is why in the end I just went the way I did.
I don't know of an easier way to do this.

 

[mahenkj2]

I think running sum facility is available in reports but I do not know if you like to use it.

 

[mahenkj2]

http://access-programmers.co.uk/forums/showthread.php?t=234607

in above post, you see a good example of running difference by RainLover. see, if this can help u in some way.