Update Query not working
Update Query not working
(OP)
I have a table with a field named "Complete". When I update data in this table I also want to check to see if work has been completed. If it is completed I don't want the user to see the completed items. The users work on items that are not complete due to errors. Not all items have errors so many are not used. Once those items are completed I want to remove them from user visibility to reduce a selection list that the users use.
I use a query to find the Items that are complete from an input table. I then created an update query on the (IPSS Classes) table linked to the query Update Complete Field. In View mode this shows the correct field to modify based on the item number. View runs fine but Run returns:
A Warning indicating changes cannot be undone. "expected"
Operation must use an updateable query. "not expected"
Here is my query code:
IPSS Classes.Complete is a check box in the table
I need the first query to find the items that are completed in the input data. These are the only items that should be updated in the IPSS Classes table complete field.
The completed value is from a different table than the IPSS Classes table. A little confusing but .... Suggestions?
Thanks, John
I use a query to find the Items that are complete from an input table. I then created an update query on the (IPSS Classes) table linked to the query Update Complete Field. In View mode this shows the correct field to modify based on the item number. View runs fine but Run returns:
A Warning indicating changes cannot be undone. "expected"
Operation must use an updateable query. "not expected"
Here is my query code:
CODE -->
UPDATE Breakout_IPSS_End INNER JOIN [IPSS Classes] ON Breakout_IPSS_End.[Related Entity ID] = [IPSS Classes].IPSSClassID SET [IPSS Classes].Complete = Yes;
IPSS Classes.Complete is a check box in the table
I need the first query to find the items that are completed in the input data. These are the only items that should be updated in the IPSS Classes table complete field.
The completed value is from a different table than the IPSS Classes table. A little confusing but .... Suggestions?
Thanks, John
RE: Update Query not working
UPDATE [IPSS Classes]
SET Complete = 'Yes'
WHERE IPSSClassID IN (
SELECT [Related Entity ID] FROM Breakout_IPSS_End)
---- Andy
There is a great need for a sarcasm font.
RE: Update Query not working
RE: Update Query not working
I will now run it.
It ran but I have to do some more work...I have a type conversion error.
I changed "Yes" to Yes and it worked. The field being updated is a check box not a text field.
Great and quick answer. I will log this on in my brain trust book for future use.
A Star to you Andy.
RE: Update Query not working
RE: Update Query not working
UPDATE Breakout_IPSS_End
INNER JOIN [IPSS Classes]
ON Breakout_IPSS_End.[Related Entity ID] = [IPSS Classes].IPSSClassID
SET [IPSS Classes].Complete = Yes;
I saw simple something like, let's say:
UPDATE Breakout_IPSS_End
SET [IPSS Classes].Complete = Yes;
So you were trying to UPDATE something in a Breakout_IPSS_End table, but the field Complete is in [IPSS Classes] table
I'm glad it worked...
Oh, and I believe the checkbox in Access takes the values of 0 for False and -1 for True, but you would have to try it to make sure.
---- Andy
There is a great need for a sarcasm font.
RE: Update Query not working