Recordset not Updatable

[Crispy]

krispi (MIS) 11 Nov 05 9:36
Hi all

I have a database of mortgage applications.

I have a query 'QryCasesToWork' which identifies applications to work based on a complex set of criteria.

Once the query has run, I need a field called 'ToDo' to update to 'yes'. Unfortunately, one of the preceding queries which goes to make up QryCasesToWork is a Union query, meaning that all the subsequent results are non-updatable.

Is there an easy way to take the application numbers returned in QryCasesToWork, and update the appropriate ToDo fields in the table?

I had a vague thought of using a Make Table query form the results of QryCasesToWork then querying that table against my main table to update the field, but this seems to me to be a cumbersome and unwieldy solution.

Does anyone have the 'correct' solution? Any help greatly appreciated.

Thanks
Chris

 

[neileg]

Union queries can be an indication that your structure is wrong. Do you have too many tables where one would do?

Failing this, can you create new queries based on the results of your final query that extract the date from individual tables? You may have to amend your existing queries so they are tagged with a value that identifies which table they came from.

 

[ScottGem]

UPDATE tablename SET Todo = True
WHERE IDfield IN(SELECT IDfield FROM QryCasesToWork)

This assumes that the table that TODO is in has a field that matches a corresponding field in the query

But I agree with Neil that you could have design issues

 

[Crispy]

Thanks both for your help.

I'll give Scott's code a try. I agree with your comments on design, unfortunately I am working with linked tables from a number of different databases, which is why I'm having to use the union query to enable me to query the data on a like for like basis.

Cheers again

Chris