Hi all

I'm trying to write an Update query to update values in a table with the contents of another table. The data to be inserted is in a table which is an identical copy of the table to be updated, and has been transferred from a SQL Server database via an Append query.

There are a number of fields that need to be updated, but here's an abridged version of the query for brevity:


UPDATE tbl_items
SET
tbl_items.it_status = "Checked"
,tbl_items.it_DueDate = (SELECT it_DueDate FROM [tbl_items to update] WHERE [tbl_items to update].it_work_number = tbl_items.it_work_number AND [tbl_items to update].it_number = tbl_items.it_number)

WHERE
it_work_number in (select it_work_number FROM [tbl_items to update]) AND it_number in (SELECT it_number FROM [tbl_items to update]);


I have an append query which pulls only the relevant data to be updated from SQL Server and inserts it into [tbl_items to update] so only records where the works order number (it_work_number) and item number (it_number) appear in items to update need to be updated (hence the WHERE clause).

I then want the Update query to update the above records' it_DueDate with the new Due Date form the items to update table. I have previously written a similar query in SQL to update values with a value selected from another table, but can't seem to make it work in this case.

Access is giving me a message saying "Operation must use an updateable query." I currently have one record in the items to update table, which definitely matches the it_work_number and it_number on a record in tbl_items so the Select statement to get the value to insert should definitely return a value, but if I remove this line and just leave the static update to it_status it works perfectly.

I'm at a bit of a loss on this one so any help would be greatly appreciated!

Many thanks
Nick