Sum problem in datasheet form

[tmac]

I can't figure this out. I've read all the posts on sums in forms but
here is my problem.



I have a list of part shown in data sheet format. There is also a
subdata sheet to each part that you can click on the + to expand into
the tranaction details. On the transaction details, I have Ordered,
Received, Sold and Shrinkage and calculate the InStock value.



On the Parts form, I reference the Instock value for each product line.
IT shows an #Error unless I expand the transaction details by click on
the +. Then the referenced Instock value gets updated. The other product
lines for In-Stock still show #Error. Each on in turn will be updated as
I clcik the +.



What am I doing wrong?



thanks



tim

 

[tmac]

Ok, I did it this way,



For the form source, I made this



SELECT DISTINCTROW [Workorder Parts].*, NZ([UnitsReceived])-NZ([UnitsSold])-
NZ([UnitsShrinkage]) AS InStock, TempProductList.ASICOST,
TempProductList.BASSCOST, TempProductList.ASISTOCK,
TempProductList.BASSSTOCK FROM ([Workorder Parts] LEFT JOIN [Inventory
Transactions] ON [Workorder Parts].PartID=[Inventory
Transactions].ProductID) LEFT JOIN TempProductList ON [Workorder
Parts].PartID=TempProductList.ProductID;



It works great! Thought Id pass it on.