How to update a query content in subform?

[rickyfong]

One master file and one transaction file. Then I create a query file mainly based on transaction file plus one field from the master file, the transaction total field. I also create a form with a sub form linked to this query. In the subform, a logic created in after action event procedure as follow:

T2 = Amount
Amount = unit price x Quantity

Amount being updated after user changed the unit price

Also I tried to update the transaction total by:

Transaction total = transaction total - T2 + Amount

The transaction total in the query or subform did updat but not in the maste file.

1) Is that the query file did create a new file to whole the selected fields of the 2 files, so that transaction totel in the query file was updated but not in the master file. As I suppose the query file only create logic to select fields from the 2original files

2) Is there any idea to solve my problem, as I need to update the master file transaction total after changing the unit price in the query file!!??

Thanks a lot!!

 

[Beetle]

You should not be attempting to store the Transaction Total in the table in the first place! Values like this should be calculated on the fly as needed, not stored in a table. To paraphrase Access MVP John Vinson;

"Storing calculated values like this accomplishes three things. It wastes disk space, it wastes time (almost any calculation will be faster than a disk fetch), and it invites bad data. If one of the underlying values gets changed, you could end up with invalid totals in your table and no automatic way to detect that fact."

Just calculate the total in a query (as it appears you're already doing) or in a calculated control on a form or report. There should be no need to store the value in a table.