Update Query to update values of a sub form (BOM)

[GAJITCS]

I have a simple BOM.



There are three tables
Assemblies - It contains the fields: AssemblyID, Description and On Hand
Parts - Contains many fields, relevant are: PartID and On Hand
BOM - Assembly ID, PartID and Number Required

Assembly:AssemblyID is a One to Many relationship to BOM:AssemblyID
Parts:PartID is a One to Many relationship to BOM:PartID

I have a form that happily shows an Assembly with a subform showing the component parts.
I have a field on that form where I can enter a value by which I can execute an update query to increase the On Hand value of the Assembly by the number specified.

What I am struggling with is the query that will then decrease the On Hand values of the component parts shown on the query.

The logic I have says along the lines of Table:Parts:OnHand-(Form:Assemblies:Text10*Form:BOM:Number Required)

In essence, if the captured value in Text 10 is 2, and the Number Required for the first entry on the BOM Components list was 3, then the On Hand value for the component would need to be reduced by (2*3)=6.
There after, for each of any other components, the calculation would be the same, On Hand = Text10*Number Required.

Is there a way of completing this task?

Would it be helpful if I posted more detail? and what would you need?

Many thanks.

[CJ_London]

strongly recommend you do not save the on hand value - calculate it as and when required.

Reason is that if an error occurs, it will be impossible to resolve. By error I mean a record is deleted, a quantity changed, the order of events are changed etc.

You should have a table of transactions which records quantities in and quantities out. Plenty of examples out there, just google 'BOM system design considerations' or similar.