Invoice Payments subForm - help with running balance

[Brook]

good day,

I have created an Invoice Form form to enter new orders. This Invoice form
has an order entry subform as well as a payments subform.

I have created some code to keep a running balance on my payments subform,
below is my code:

The problem is this, when I enter a date, lets say 1/1/2005, then
4/1/2005, all my balances are updated accordingly. However if I enter a date
1/1/2004 for a payment that I missed, the "Mynewbalance" is recalculated
based on my original invoice amount.

Any ideas/suggestions that could help me with my coding?

thanks,

brook


Begin Code:

Private Function MyNewBalance() As Currency
Dim curInvoiceTotal As Currency
curInvoiceTotal = Me.Parent.Form![invoicetotal]

If Nz([paymentdate]) = 0 Then
'Set to zero to avoid triggering an error in the Dsum call

MyNewBalance = 0
Else

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[PaymentDate] <= #" & [paymentdate] & "#")
End If
End Function

End Code: