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.