I have a main from that is driven by a table and a subform of the main form that is driven by another table. On the subfrom I do a summary of qty * amount and have transferred that the main form as text box. I want to take the value in that text box on the main form and use it to update a field in the table associated with the main form..
If I understand you correctly you want to store derived (calculated) data. This is a very bad idea and goes against the rules of conventional database design. There are several reasons for this but primarily:
- you can always re-calculate the data whenever you need it i.e. no need to store it
- suppose someone changes the data in you "sub" table. Then the data in your main table is wrong. You would have to ensure nobody could change the sub-data without then recalculating the main data.
If you need this calculated data to be available say for a report, then write a query that will provide it. You can always use this query as your data source whenever you need the calculated data withou the worry of knowing whether your tables are in sync.
The query will be an aggregate query with the two tables joined as the source.