Append Problem

[Mart]

Trying run an Update query to update table values with value(s) from a joined
Query. I've tried the usual JOIN options and the following is my latest
attempt from scanning suggested solutions:

UPDATE tblRenaultPriorities AS TRP SET TRP.Description = (select PDescript
from qryMC_RenaultPriorities AS QRP
WHERE QRP.[Req #] = TRP.[Req No] AND QRP.[Sub #] = TRP.[Sub No]);

.....but i still keep getting the same error msg "Operation Must Use an
Updatable Query"

 

[Stefan Hoffmann]

hi Mart,

I've tried the usual JOIN options and the following is my latest
attempt from scanning suggested solutions:

This is not a join.

UPDATE tblRenaultPriorities AS TRP SET TRP.Description = (select PDescript
from qryMC_RenaultPriorities AS QRP
WHERE QRP.[Req #] = TRP.[Req No] AND QRP.[Sub #] = TRP.[Sub No]);

....but i still keep getting the same error msg "Operation Must Use an
Updatable Query"

You cannot use a subquery for an update. You must use a DLookup() to
another select query or use a join:

UPDATE qryMC_RenaultPriorities AS A
INNER JOIN tblRenaultPriorities AS B
ON A.[Req #] = B.[Req No] AND A.[Sub #] = B.[Sub No]
SET B.Description = A.PDescript;


mfG
--> stefan <--

 

[John Spencer]

Access won't let you do that since it believes that your subquery could return
more than one row.

A method that might work would be
UPDATE tblRenaultPriorities AS TRP INNER JOIN qryMC_RenaultPriorities AS QRP
ON QRP.[Req #] = TRP.[Req No] AND QRP.[Sub #] = TRP.[Sub No]
SET TRP.Description = [QRP].[PDescript]

That may not work if qryMC_RenaultPriorities is not itself an updateable query
- union query, group by query, distinct query

If that fails, you will have to use one of the VBA domain functions - DLookup
may work well for you. It will be slow with a large number of records

UPDATE tblRenaultPriorities AS TRP
SET TRP.Description = DLookup ""PDescript","qryMC_RenaultPriorities",
"[Req #]=""" & TRP.[Req No] & """ AND [Sub #] = """ & TRP.[Sub No] & """" )

IF Req No and Sub No are numeric fields and not text fields then replace the
triple quotes with one quote and remove the quadruple quotes

If that seems to work but is too slow. You may need to use the QRP query to
build a temporary table and then join the temporary table to the TRP table to
do the update.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Mart]

thanks Stefan

I was probably unclear but i had already tried a join such as the 1 you
suggested with no luck. It seems creating a temporary Table and joining to
that gets around the "Updatable Query" error

hi Mart,

I've tried the usual JOIN options and the following is my latest
attempt from scanning suggested solutions:

This is not a join.

UPDATE tblRenaultPriorities AS TRP SET TRP.Description = (select PDescript
from qryMC_RenaultPriorities AS QRP
WHERE QRP.[Req #] = TRP.[Req No] AND QRP.[Sub #] = TRP.[Sub No]);

....but i still keep getting the same error msg "Operation Must Use an
Updatable Query"

You cannot use a subquery for an update. You must use a DLookup() to
another select query or use a join:

UPDATE qryMC_RenaultPriorities AS A
INNER JOIN tblRenaultPriorities AS B
ON A.[Req #] = B.[Req No] AND A.[Sub #] = B.[Sub No]
SET B.Description = A.PDescript;


mfG
--> stefan <--

 

[Mart]

thanks John

I didn't get in to the DLookup option and the INNER JOIN example gave the
same "Updatable Query" error.

Instead I created a Temp table and joined to that and sure enough it was
happy with that join and performed the Update successfully. Not 100% ideal
but a practical enough solution !

thanks again

Mart

Access won't let you do that since it believes that your subquery could return
more than one row.

A method that might work would be
UPDATE tblRenaultPriorities AS TRP INNER JOIN qryMC_RenaultPriorities AS QRP
ON QRP.[Req #] = TRP.[Req No] AND QRP.[Sub #] = TRP.[Sub No]
SET TRP.Description = [QRP].[PDescript]

That may not work if qryMC_RenaultPriorities is not itself an updateable query
- union query, group by query, distinct query

If that fails, you will have to use one of the VBA domain functions - DLookup
may work well for you. It will be slow with a large number of records

UPDATE tblRenaultPriorities AS TRP
SET TRP.Description = DLookup ""PDescript","qryMC_RenaultPriorities",
"[Req #]=""" & TRP.[Req No] & """ AND [Sub #] = """ & TRP.[Sub No] & """" )

IF Req No and Sub No are numeric fields and not text fields then replace the
triple quotes with one quote and remove the quadruple quotes

If that seems to work but is too slow. You may need to use the QRP query to
build a temporary table and then join the temporary table to the TRP table to
do the update.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Trying run an Update query to update table values with value(s) from a joined
Query. I've tried the usual JOIN options and the following is my latest
attempt from scanning suggested solutions:

UPDATE tblRenaultPriorities AS TRP SET TRP.Description = (select PDescript
from qryMC_RenaultPriorities AS QRP
WHERE QRP.[Req #] = TRP.[Req No] AND QRP.[Sub #] = TRP.[Sub No]);

....but i still keep getting the same error msg "Operation Must Use an
Updatable Query"