'Must be an updatable query' problem

[Guest]

Can someone explain why this query wont run? I get 'Must be updatable query'.
I clicked on Help but nothing made sense.

UPDATE tblActivityQuestions SET QuestionID = (SELECT Q.ID FROM tblQuestions
As Q WHERE tblActivityQuestions.ChecklistID = Q.ChecklistID AND
tblActivityQuestions.Sequence = Q.Sequence);

All I'm trying to do is to update a column based on values in another table.
There are no relationships involved.

 

[Amy Blankenship]

Is this on a web server? Make sure the directory priveleges are set
correctly.

 

[Duane Hookom]

Try this kludgy, slow work around:

UPDATE tblActivityQuestions
SET QuestionID = DLookup("ID","tblQuestions", "ChecklistID = " &ChecklistID
& " AND Sequence = " & Sequence);

This assumes ChecklistID and Sequence are both numeric fields.

 

[John Spencer]

Perhaps you can use this syntax

UPDATE tblActivityQuestions Inner Join TblQuestions as Q
ON tblActivityQuestions.ChecklistID = Q.ChecklistID AND
tblActivityQuestions.Sequence = Q.Sequence
SET QuestionID = Q.ID

Since your subquery could in theory return more than one value, Access won't
allow updating. You may know from your data that you will get back at most
one value, but Access doesn't and therefore ...