recording payment on detail vs. main form

  • Thread starter Thread starter June
  • Start date Start date
J

June

I have a main billing form with a billing detail subform.
On the subform there's a PAID check box for my users to
click when they receive payment for each line item. I
would like to have some way to record when ALL line items
of a particular invoice are paid. Is there a way to cause
a yes/no field in my main billing table to automatically
record "yes" once all the line items of its associated
bill details have been recorded as PAID? Or should I be
approaching this another way?

thanks in advance,
June
 
June said:
I have a main billing form with a billing detail subform.
On the subform there's a PAID check box for my users to
click when they receive payment for each line item. I
would like to have some way to record when ALL line items
of a particular invoice are paid. Is there a way to cause
a yes/no field in my main billing table to automatically
record "yes" once all the line items of its associated
bill details have been recorded as PAID? Or should I be
approaching this another way?
Click to expand...

Since you are recording payments at the detail level, you have no need
nor reason to store a value at the summary level to indicate that all
detail items have been paid. This piece information can always be
retrieved by a query, and you can use a calculated control on the main
form to do that lookup. For example, you could have a checkbox
"chkAllPaid" on the main form with a controlsource along these lines:

=Not IsNull(DLookup("InvoiceItemID", "tblInvoiceDetails",
"InvoiceID=" & [InvoiceID] & " AND IsPaid=False"))

You'd have to force this check box to be recalculated every time you
update a record on the subform, so you'd have code like this in the
subform's AfterUpdate event:

Private Sub Form_AfterUpdate()

Me.Parent!chkAllPaid.Requery

End Sub

If you need to run reports or lists invoices which have or have not been
completely paid, queries can easily identify them. To get all invoice
records that haven't been completely paid, this would suffice:

SELECT * FROM tblInvoices
WHERE InvoiceID IN
(SELECT InvoiceID FROM tblInvoiceDetails
WHERE IsPaid=False);

To get all invoice records that *have* been completely paid, you could
use NOT IN:

SELECT * FROM tblInvoices
WHERE InvoiceID NOT IN
(SELECT InvoiceID FROM tblInvoiceDetails
WHERE IsPaid=False);

That may not be the most efficient method, as I gather Jet SQL doesn't
handle NOT IN very well. If you try it and find it too slow, there are
other SQL formulations to accomplish the same thing.
 
Back
Top