Update table from form!

[Xhevati]

Hi all in this forum,
I have create a access database to refer a invoices in my company. I have a tables called "RECEIVE_INVOICE" and "PAID_INVOICE", which are in "one-many relation ship. I have a form "frmPAID_INVOCE" (based on the "PAID_INVOICE" table) and I like after fill a last field in this form (AMOUNT_OF_PAID) to update a field PD (which is Yes/No type) in "RECEIVE_INVOICE" table. How I can do it?
Thanks forward!
P.S. Sorry for my mistakes in english!

 

[neileg]

Couple of ideas
1) In the After Update event of AMOUNT_OF_PAID put some code to run an update query on your RECEIVE_INVOICE table.

2) Instead of basing your form on the table, base it on the query and include your PD field from the RECEIVE_INVOICE table. Add a control on your form (make it invisible if you like) for PD and use some code to update this control in the After Update event of your AMOUNT_OF_PAID control.

Question:
Why do you have two tables? Why not just have one table for invoices and a flag to show if they are paid or not. I can't think of a good reason for two tables.

 

[Xhevati]

Hi all in this forum,
I have create a access database to refer a invoices in my company. I have a tables called "RECEIVE_INVOICE" and "PAID_INVOICE", which are in "one-many relation ship. I have a form "frmPAID_INVOCE" (based on the "PAID_INVOICE" table) and I like after fill a last field in this form (AMOUNT_OF_PAID) to update a field PD (which is Yes/No type) in "RECEIVE_INVOICE" table. How I can do it?
Thanks forward!
P.S. Sorry for my mistakes in english!

Thanks Neil,
I like to describe for you that I need two tables, because with one PAYMENT ORDER, we paid more "RECEIVE_INVOICE" and in this table I save these orders. My question was that can I directly update in Access this field (without query)? I mean after update last field, with code open table , find this invoice in "RECEIVE_INVOICE" and update there field PD to YES. (Default Value is No).

 

[Rich]

Wether the invoice has been paid should be calculated, relying on code to update a check box indicating the balance status is not recommended

 

[neileg]

Xhevati, you have responded to my question, but you seem to have ignored my suggestions. I made these ignoring the doubts I had about your design.

My question is very much like Rich's response. We both think you are doing this the wrong way.

The way I would do it if I were writing an accounting application would be to have one table for invoices, one table for payments and a third table for allocations. In the allocations table I would hold amounts that related the payments to the invoices. You may expect to only ever pay the invoices in full, but sometimes you will need to allocate a part payment to the invoice. Therefore,as you say, you can have a payment that relates to many invoices, but you can also have an invoice that is paid by many payments.

You would calculate the total amount paid on an invoice from the allocations table. Subtract this from the invoice amount and you will find out if the invoice is fully paid, part paid or not paid.