Parameter in a sub query

[LonnieJohnson]

I want to use a sub query to determine the value for an update query. My problem is using one of the fields in the main query's table as a parm in the sub query like so...

UPDATE tempDemand SET tempDemand.resourcesAvailable = (SELECT ResourcesAvailable FROM qrySupplyResult WHERE Role =   [tempDemand]![Role]);

Does that make sense?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[PHV]

UPDATE tempDemand INNER JOIN qrySupplyResult ON tempDemand.Role = qrySupplyResult.Role
SET tempDemand.resourcesAvailable = qrySupplyResult.ResourcesAvailable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[LonnieJohnson]

Thanks. I did that at first and got a message saying the I must use an updateble query. That's when I went to this try. I finally did it with a recordset object in vba.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!