Add multiple records to a subform from a query - not its data source

[BrainCavity]

Hi all,

I want to populate a subform with data from another query based on specific criteria from that parent form. I'll give you the example.

I have an asset (a car for example). That car is made up of many parts (wheels, doors, etc.) I have the user build this asset and give it a number (serial number if you like). I have the user assign all the different parts to the asset that would be needed to make it. I do this through a form subform (asset and asset detail, linked by asset ID).

Now that they have their asset built, I want to use it in a contract, so I have them create a new contract and assign an asset to that contract (1:1). In the contract details (subform), I want to list all the parts that were needed to build the asset I have selected (basically fill this subform with the information from the asset details). I also want them to be able to add and subtract any other items from inventory that they want for this contract. Then when I save the contract, it would have all the parts (the ones from the asset and any others they choose), saved as the contract details.

I know that I cannot use the asset details directly because it would change the asset details if I let them add or subtract so I know that I need to have a copy of that data added to the contract details. That's where I'm stuck. Both the asset details and contract details pulls their part's list from inventory.

I hope this is clear and makes sense. I have attached a sample database.

Thank you in advance!

 

[Galaxiom]

Make a link table. This has two fields. The PK of the Contract and the PK of the Inventory.

For the subform Record Source make a query joining the inventory table to the link table. Set the Child field as the Contract foreign key field of the link table. Set the Master field as the Contract primary key.

When you open a contract in the main form the subform will display the associated parts via the link table.

You should also consider that the cost of parts will change. Add another field to the link table to hold the cost of the part at a build time and use this instead of the current inventory cost or it will really mess with the value of the contract.