I am using Access 2007
I have a database that I am working on that will build a unit with a serial number and keep track of what parts were used to build the unit. Each unit has a Bill of Material list with parts that are mandatory or discretionary.
tblBOM
UnitNum_FK
PartNum_FK
BOMQty
ReplacementType_FK (if =1 mandatory, if=2 discretionary)
ReplacementNote
tblUnitAssy
UnitAssyNum_PK
UnitNum_FK
SerialNum
UnitAssyDate
UnitAssyQty
tblPartAssy
PartAssyNum_PK
PartNum_FK
PartAssyQty
I want to setup a form with a subform. The form will update tblUnitAssy. I have a query to use as the record source for the form:
SELECT tblUnitAssy.UnitAssyNum_PK, tblUnitAssy.UnitNum_FK, tblUnitAssy.SerialNum, tblUnitAssy.UnitAssyDate, tblUnitAssy.UnitAssyQty
FROM tblUnitAssy;
I have a query to use for the subform:
SELECT tblBOM.UnitNum_FK, tblBOM.PartNum_FK, tblBOM.BOMQty, tblBOM.ReplacementType_FK, tblBOM.ReplacementNote
FROM tblBOM
ORDER BY tblBOM.ReplacementType_FK;
The link between the forms will be the tblUnitAssy.UnitNum_FK=tblBOM.UnitNum_FK
The user will either leave the tblBOM.BOMQty as is or change it depending on the parts used. If the part was not replaced they will enter 0. The problem I am having is that I do not want the tblBOM updated I want the tblPartAssy updated. I was thinking of putting a button on the form. When that button if clicked the tblPartAssy is updated. I think I would need to do an Append Query to insert rows. Just not sure exactly how to set this up.
Any thoughts on how I can get started?