Update statement error

[Alain]

Hi to all,

I am gettin the folowing error "operation must use an updatable query"
with the following :
UPDATE BudgetHST SET BudgetHST.Avril = (SELECT Avril FROM GrossMois WHERE
Idbranch=79)
WHERE BudgetHST.Idbranch=79;
my goal is to update a specific field on a table from a specific field from
another table, both tables are not related,
what am I doing wrong here

Thanks

 

[Jerry Whittle]

Test this on a backup copy of the table or database:

UPDATE BudgetHST AS BT
SET BT.Avril = (SELECT Avril
FROM GrossMois AS GM
WHERE GM.Idbranch=79
AND GM.Idbranch = BT.Idbranch)
WHERE BT.Idbranch = 79 ;

 

[Alain]

Thanks Jerry, but no luck, still gettingthe same msg.
I have found another way to do this kind of update, I use Dlookup to find
the value I need and then use a basic update statement .
It does the job well

 

[John Spencer]

You might be able to use

UPDATE BudgetHST, GrossMois
SET BudgetHST.Avril = [GrossMois].[Avril]
WHERE BudgetHST.Idbranch=79 AND
GrossMois.IdBranch = 79

Or even this

UPDATE BudgetHST INNER JOIN GrossMois
ON BudgetHST.Idbranch=GrossMois.IdBranch
SET BudgetHST.Avril = [GrossMois].[Avril]
WHERE BudgetHST.Idbranch=79

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