'Operation must use an updateable query'

[DanB]

I have an issue with a query that I cannot figure out the answer to. I keep
getting the error 'Operation must use an updateable query'. The query I am
running is:

UPDATE DGBTestVariations
SET DGBTestVariations.JURISDICTION =
IIF(DGBTestVariations.JURISDICTION = "NoMoneyMuni",
(SELECT DGBJurisdictionCOO.JurisCodes FROM DGBJurisdictionCOO WHERE
DGBJurisdictionCOO.ID=3),
IIF(DGBTestVariations.JURISDICTION =
"Municipalities", (SELECT DGBJurisdictionCOO.JurisCodes FROM
DGBJurisdictionCOO WHERE DGBJurisdictionCOO.ID=2) ,
IIF(DGBTestVariations.JURISDICTION = "County",
(SELECT DGBJurisdictionCOO.JurisCodes FROM DGBJurisdictionCOO WHERE
DGBJurisdictionCOO.ID=1) ,
IIF(DGBTestVariations.JURISDICTION IS
NULL, "ANY", DGBTestVariations.JURISDICTION
)

)
)
)

The DGBTestVariations table can be updated with a string "this works", but
when I try to update it programatically it gives me the error. The table
DGBJurisdictionCOO is a table I have built specifically adding an Indexed ID
field that does not allow duplicates and is the key for the table. I tried
selecting where the group I wanted matched a string I input, and then
switched to this when it kept giving me the error. I was thinking maybe it
was erroring out because it wasn't unique or something, even though it is.
So when I went to this code using the key field from the table, and it still
doesn't work, I decided to ask the experts.

 

[John Spencer]

Your sub-queries are the problem, since in theory they could return more than
one row. Access does not check if they actually return more than one row, it
just makes the assumption that it is possible for a sub-query to return more
than one row.

You can use DLookup to solve the problem. DLookup is guaranteed to return at
most one value (null is an option)

DLookup("JurisCodes","DGBJurisdcitionCOO","Id=3")

UPDATE DGBTestVariations
SET DGBTestVariations.JURISDICTION =
IIF(DGBTestVariations.JURISDICTION = "NoMoneyMuni",
("JurisCodes", "DGBJurisdictionCOO", "ID=3",
IIF(DGBTestVariations.JURISDICTION = "Municipalities",
("JurisCodes","DGBJurisdictionCOO","ID=2",
IIF(DGBTestVariations.JURISDICTION = "County",
("JurisCodes","DGBJurisdictionCOO","ID=1") ,
IIF(DGBTestVariations.JURISDICTION IS NULL, "ANY",
DGBTestVariations.JURISDICTION ) ) ) )

You might add a where clause to speed things up and skip those records where
you are not actually changing the value of JURISDICTION.

WHERE Jurisdiction is Null
Or Jurisdiction in ("NoMoneyMuni","Municipalities","County")

Of course, with this limited bit, you could hard code the values to be
inserted and not bother to look up the JurisCode at all.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County