'Lo All,

I've been working on a little bill of materials project and am running into a problem with one of my forms.

The main form is currently used to select the desired item and then the subform spits out a list of the needed components, quantities, and cost. Since some of the components cannot be manufactured in house I used a rather ugly IIF/DLookup function to determine what category (purchased/manufactured) it fell in then looked at the appropriate table.

This works great but then I decided to add a "Total Cost:" field in the subform's footer. This of course didn't work out since I'm trying to SUM a calculated field. Is there someway to convert the IIF/Dlookup process into a query so that I can use that to supply the data to my Cost textbox and then be able to SUM?

On a related note, is there a way to add checkboxes for each item listed on the subform that don't act as a group? I'd like to have one so if a component can be manufactured, but we decide to purchase anyways, I can check that and have it use the correct cost...

Handful of problems so if anyone could just point me in the right direction I'd appreciate it.

Ugly IIF/Dlookup:
IIf( nz(DLookUp('[ProductionCost]![ProductionCost]','ProductionCost','[ParentID]=[ComponentID]'),0)=0,
Table.Fields Used: