I am trying to create a form with a datasheet subform. The datasheet is based on the tblMaterial:

MaterialID
WorkorderID
PartID
Description
UnitCost
Quantity
Notes

The PartID is a lookup to tblMaterial which contains the following fields:

PartID
PartCode
PartName
UnitCost
Notes

I would like to create a combobox called cboProduct with the following record Source:

SELECT [tblPart].[PartID], [tblPart].[PartCode], [tblPart].[PartName], [tblPart].[UnitPrice] FROM tblPart;

And by clicking cmdAddPrduct it would insert the specific fields into the approriate fields in the DS. The workorderID will be passed by Args from the main form.

Any ideas?

I'm not clear what is happening here. If you want to add a material to the subform, why not just have a combobox on the subform to select the Part?

Its my understanding that a datasheet could not have a footer or header on it.

So? I don't see what you need a header or footer for. But if you need one, use Continuous Form mode rather than a datasheet.

That make sense but the issue still remains on how to update the continous form based on the selection of a combobox, any ideas?

Maybe I'm not understanding the process. But what it sounds like to me that you are entering Material records through the subform. As part of the Material record you are selecting a Part number to populate the PartID field in the Material record. So you create your combo as part of the subform.