How to program update table...

[Kaloyanides]

I have a sales order database.

tblSalesInvoiceHeaders


tblSalesInvoiceItems
tblPayments

When user enters a sales order, they have to click "Issue Invoice." When the invoice is issued, the invoice number is assigned and a record is added to the tblPayments table for collection purposes. This works fine. However, if the invoice is later modified, the user has to go to the Payments tab on the sales order entry screen and manually update or change the invoice amount to reflect the new total. It's not updating automatically. I had it updating but it was buggy. I'm wondering if a professional could shed light on the correct way to go about doing this?

When invoice modified, automatically change the invoice total in the tblPayments table to reflect the new total.

Thanks!

[ranman256]

you need an update query that joins tPayment.InvoiceID to tblSalesInvoiceHeaders.InvoiceID
sum the new total from tblSalesInvoiceItems, like in a textbox on the edit form, txtSum.
the update will change the tPayment.Total to the txtSum.

(it has to be done using the form, because you cannot SUM AND UPDATE in a query)

[aytee111]

The Payments table is a record of payments, not a place to store the invoice amount. These should be two totally separate entities - the amount that is due and the amount that is paid. Learn about table design, and especially normalization techniques - one thing being never to repeat data, which you are doing here. You can see the problems that is causes!