I have a form linked to a query that uses 3 tables. One of the tables contains a price field (static). It shows a corresponding price in a form field after a product is chosen. Another form field contains another price field (the price actually paid because of discounts). The user can change this final price manually.

I would like the static price minus the discounts to be copied over to the dynamic price field (and thus also to the corresponding table field). However, if the form field control source is a table field it can't also be a form field or a calculation. Is there any way to link this field to the final price field but to also have it get the price from the static field minus discounts ?

I know I shouldn't keep calculated fields in a table but that's the way the assignment requests it be handled.