help with "BalanceDue" on Invoicepayment form?

[Brook]

Good Day all,

I have frminvoices that I use to create invoices and apply payments. The
frminvoicepaymentssubform pulls the invoicenumber, invoicetotal from the
frminvoices, and as payments are applied, they are deducted from the
invoicetotal.

The problem that I am having is that my code is applying payments as a
running sum instead of for each invoicenumber.

Below is my code, if anyone can help me set this up so that the
"BalanceDue" is calculated for each invoicenumber and invoicepayment subform?

I have a field on my form labeled: BalanceDue with a control source
"=MyNewBalance()"

The reason that I am trying to set this up is so that once the balance due
is "0" then a checkbox is updated to Paid in Full and taken off my open
invoice report.

Thanks in advance..

Brook

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

If Nz([paymentdate], 0) = 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:

 

[UpRider]

For your Dsum, your criteria is for a specific payment date. It should be
for a specific invoice number. Using the date will just sum up all the
payments made on that date, regardless of the invoice number or even the
customer!

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[InvoiceNO] = " & me.[txtInvoiceNO)


HTH, UpRider

 

[Brook]

Thank you...

That worked perfectly!

Brook

For your Dsum, your criteria is for a specific payment date. It should be
for a specific invoice number. Using the date will just sum up all the
payments made on that date, regardless of the invoice number or even the
customer!

MyNewBalance = curInvoiceTotal - DSum("[PaymentAmount]",
"tblinvoicePayments", _
"[InvoiceNO] = " & me.[txtInvoiceNO)


HTH, UpRider

Good Day all,

I have frminvoices that I use to create invoices and apply payments. The
frminvoicepaymentssubform pulls the invoicenumber, invoicetotal from the
frminvoices, and as payments are applied, they are deducted from the
invoicetotal.

The problem that I am having is that my code is applying payments as a
running sum instead of for each invoicenumber.

Below is my code, if anyone can help me set this up so that the
"BalanceDue" is calculated for each invoicenumber and invoicepayment
subform?

I have a field on my form labeled: BalanceDue with a control source
"=MyNewBalance()"

The reason that I am trying to set this up is so that once the balance
due
is "0" then a checkbox is updated to Paid in Full and taken off my open
invoice report.

Thanks in advance..

Brook

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

If Nz([paymentdate], 0) = 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: