Operation must use an updateable query

[Arpan]

Consider the following Query in a MS-Access database:

UPDATE FirstGlobal SET Quantity = (SELECT Quantity FROM FirstGlobal
WHERE ClientName = [ClientName1] AND Scrip = [Scrip1] AND ContNoteNo =
[ContNoteNo1]) + [AddQty1]
WHERE ClientName=[ClientName1] AND Scrip=[Scrip1] AND
ContNoteNo=[ContNoteNo1];

When I run the above Query by clicking the 'Run' button (with the
exclamation mark) while I am in SQL View in MS-Access, then I am first
prompted to enter a value for the input parameter 'ClientName1', then
I am prompted to enter a value for the input parameter 'Scrip1', then
I am prompted to enter a value for the input parameter 'ContNoteNo1' &
finally I am prompted to enter a value for the input parameter
'AddQty1'.

After this, Access generates the following error:

Operation must use an updateable query.

After some tests, I concluded that the source of the error is the sub-
query 'SELECT Quantity FROM FirstGlobal.......'. If I replace the sub-
query with any number, say, 100 & then add it to the parameter
'AddQty1', then the above Query runs perfectly.

But what is wrong with the sub-query?

Please note that the sub-query will ALWAYS RETURN only ONE VALUE &
will NEVER BE NULL.....that is for sure.

 

[Arpan]

Well, it seems the JET DB Engine treats any query with a sub-query as
a non-updateable query which was why Access was generating the
'Operation must use an updateable query ' error. I changed the query
to overcome the error:

UPDATE FirstGlobal AS FG1 INNER JOIN FirstGlobal AS FG2 ON
(FG1.ClientName = FG2.ClientName) AND (FG1.Scrip = FG2.Scrip) AND
(FG1.ContNoteNo = FG2.ContNoteNo) SET FG1.Quantity = FG1.Quantity +
[AddQty1]
WHERE FG1.ClientName=[ClientName1] AND FG1.Scrip=[Scrip1] AND
FG1.ContNoteNo=[ContNoteNo1];

So please don't bother to answer this post any longer.