Update Query Returning (Operation must use an updatable query) 1

[puforee]

I have a table that contains class information (Classes). One of the fields is a yes/No field default 0 (No) named "Complete" The Key field in this table is (ClassID).

Before considering a Class complete I have a query (Completed Classes) that checks 2 items (must be done for Completion) and then returns the Keys (ClassID) of classes that meet the completed criteria. Using the Completed Classes query and the Classes table I am attempting to use an update query to change the "Complete" field in the Classes table to -1, or Yes. This is where the error occurs. I get the same error if I try to set the Complete field to Yes.

Here is the code for the Update query that is not running correctly:

UPDATE [Classes] INNER JOIN [Completed Classes] ON [Classes].ClassID = [Completed Classes].ClassID SET [Classes].Complete = -1;

I always seem to have issues with update queries when they depend on a Table to be updated and a query containing the key field.

Thanks,

 

[dhookom]

I think the error message is accurately stating the query can't be updated because of the [Completed Classes] query.

You might try this query after backing up your data:

UPDATE Classes SET Complete = 1 WHERE ClassID IN (SELECT ClassID FROM [Completed Classes]);

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

Thanks Duane, and another star for you. Although, can you explain why mine did not work and yours did. After coping in the code and looking at Design View I noticed the criteria for ClassID contained the "In Select" statement? And, I will keep this code in my notes for possible further use.

Thanks again,
John

 

[dhookom]

Can you provide the sql of [completed classes]?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016