bastos.sergio@gmail.com wrote:
[color=blue]
> Hello everyone,
> I'm having a bit of a problem seeying the big picture in this and I was
> hoping somebody more experienced in this could help tell me if I'm
> proceding right. I plan to construct a master invoice table with
> client information and a linked details invoice table with line items
> of all items charged.
> I also plan to have a payments table of the invoices. Since the client
> can make more than one payment to an invoice I need to create another
> master-detail table. The master table carries the invoice id, and the
> details table carries all the payments made to that invoice. But how
> can I tell that the invoice has already been paid in full and stop the
> user from adding more payments to it? Is there another way to do this
> and I'm not seeying it? Any help would be great...[/color]
This seems like a decent structure to me. I use it myself :-)
There is no way to technically block more entries to the payments
*table* after the total equals (or exceeds) the invoice sum. But the
spot where the user enters payments should be a form, so we have some
instruments to our disposal.
You didn't tell about your forms. I just guess there is one to enter
payments, and you have to indicate the invoice where the payment is
related to. As soon as the user chooses the invoice, your program can
quickly sum up all payments (if any) using DSum if nothing else, message
the user (did you know that 'to message' is a verb? :-) ) and even set
AllowAdditions to false. Or so.
Do yo store the total amount in the invoice record? I don't. I do store
item prices in the detail records--I don't want to have to retrieve
those from products tables, and moreover these could change over time.
So if you want to have that invoice total, a little calculation is due.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html 
how to link a master detail table 
