[Erm]

I have an Access 2010 database as attached and a query in it called qryDueforArchive. The query is displaying the necessary results...all with the most recent treatment date greater than a year, but it won't allow me to tick the archived box...checked unique values and dynaset settings and all are as resources suggest they should be...please can anyone help?

PS. OBP...if you see this,,,check out tabbed forms...is this what you was thinking?

[OBP]

Sorry, I can't open the 2010 database, it would have to be 2007 for me to open it.
Are you actually archiving the data or just marking as "archived"?
Is there more than one table in the query?
What joins have you used, you can't use left or right joins and still have the dataset updatable.

[Erm]

Oh yeah I forgot, sorry...what about this one?

Just marking it as archived...2 tables, its a 1 to many

[OBP]

No it is still the same, I have had this problem with converted databases. I will attach an Access 2000 database, can you import the tables and query in to it, that should ensure that I can open it.

[Erm]

here we go...thanks

[OBP]

You are using qryMaxTreatmentDate which uses aggregate functions which make the query recordset "Not Updatable".
So you either need to use a VBA Recordset or use that query to make or append to a table.

[Erm]

oh...will a VBA recordset allow me to present the user with the list of records from that query for them to tick archive as required? is it difficult...no idea where to start...is that what you meant by tabbed mainforms too?

[OBP]

No it would not make it available to the user to tick them. I can't open your forms, it gives loads of errors due to being an Access 2010 database.
In fact even using an update query and appending the records to a table and then using the table to filter the query does not allow the recordset to be updatable.
You can however create an Updatable query as long as the Treatment Record is not included in the query.

[OBP]

OK, take a look at the new Temp table and the reworking of your original query in to an Append query. It appends the records that have a max date over one year old. The query that brings it back together and is updatable is the new Temp Query.
So to make this work you will also need a Delete query to delete the old records from the temp table.
Run that query plus your append query and then ope a form based on the Temp Query to tick the Archive check box.
If you didn't need user intervention you could just use a query or vba to tick the archive boxes.

[Erm]

oooh sorry, never got a notification about this!! Will go through that now...at present had settled with it producing a list to use as a check list