Update one field from one table to another field in another table.

[Fox]

I'm not sure I'm in the right section...or if I'm asking the right question
or if it is even possible but
Here it goes...

I have a form called products that has a subform that is called
suppliesUsed. The main form called products gets it's data from a table
called products and the subform gets it's data from the table suppliesUsed.

In the form a user enters the product description, unit price etc and in the
suppliesUsed subform they enter all the supplies that were used for this
product so this could be more than one record. So one product can have many
supplies in the suppliesUsed table. The fields are related/joined by
Products.ProductNameand SuppliesUsed.EndProduct. When the user enters the
product name in the top of the form (products part) the End Product field
gets populated with that information automatically.

In the suppliesUsed subform I created a couple label form controls, one to
total all the suppliesused records for the product in focus and then one to
add the tax to this total. The one that adds the tax is called Supply Total
and of course it is not a field in the suppliesUsed table.

There is a field in the Products table called product cost.

Is there a way to copy the label form control data from the subform to the
product form field ProductCost?

When I create a query that groups the supplies used together so that I can
get correct totals and try to use this with the product table then I am not
able to add records to the form anymore. The little add a record selector
disappears..


If there is anyone that is understanding any of this and has time to help me
out I'd sure apprieciate it.

 

[John W. Vinson]

Is there a way to copy the label form control data from the subform to the
product form field ProductCost?

When I create a query that groups the supplies used together so that I can
get correct totals and try to use this with the product table then I am not
able to add records to the form anymore. The little add a record selector
disappears..

Generally such totals should not be stored ANYWHERE, in any table. The problem
is that if you change a single record in the underlying data, your stored
total will now be WRONG with no easy way to detect that fact.

Unless you have a *demonstrated* (not assumed!) need to store the total, it's
usually best just to redo the calculation when it's needed. There are cases
where you do need to use VBA code to "push" the calculated field into a bound
textbox, but again... don't do it unless you have a very good reason to do so!

 

[Fox]

Thanks John, I've reworked it so I do not need to do this....Not sure what I
was thinking anyway.....