Can't get a value from a querry into a forms text box

[Bob H]

I have a main Shippin Control table, and a main Shipping Control form
with description of cargo and quantity text boxes. I have a querry which
is a join of tblValues and tblShipping_Control. The SQL is below

SELECT Shipping_Control.DescriptionofCargo, Shipping_Control.CargoQty,
tblValues.Value, ([Shipping_Control].[CargoQty]*[tblValues].[Value]) AS
CommercialValue, Shipping_Control.ShippingDate, Shipping_Control.ShipTo,
Shipping_Control.ShipFrom, Shipping_Control.CollectionNoteNo,
Shipping_Control.PackingListNo, Shipping_Control.ShipmentID,
Shipping_Control.Customer
FROM Shipping_Control INNER JOIN tblValues ON
Shipping_Control.[DescriptionofCargo] = tblValues.[HGGPPart]
WHERE (((Shipping_Control.Customer)<>"GDF SUEZ"))
ORDER BY Shipping_Control.DescriptionofCargo;

I want to get the CommercialValue from the above into the
CommercialValue box in the main form.

I have tried using D Lookup, =DLookUp("[CommercialValue]",
"[qryPartValue]", <criteria to select the

desired part>)

Adding this to the ControlSource in form design view for the commercial
value text box puts the same value in every record in the main form,
which is the first value from the qryPartValue

I have also tried adding the relevant qurries in querry design, but I am
not getting the results I want. Maybe I amnot adding the corrct tables
or qurries??

I have tblShipping_Control, the main table
tblValues, Values of the parts to ship from 3 sites
tblValueTees, Values of parts from another site
tblPart, listing every part which needs a value
qryPartValue, from tblValues
qryValueTees, from tblValueTees
qryShipping_Control, main qry
frmShipping_Control, main form with all required fields including
Commercial Value, CargoQty, and CargoDescription.


Is there anyway this can be done.

Thanks