The database is a lending library. I wish certain records to be archived to a duplicate table, so I can create history of items people have lent.

The database contains name, item lent, date lent and date returned. When a command button is pressed it will only archive records that have a return date in them.

I have tried to use the 'After Update', to set a flag, and when the command button is pressed it checks if it has been set, but I just cant get it to work. I can do the other bits, such as Insert int otable.

Any help would be appreciated.

Chris,

Please follow Alan's advice on your table structure as it will make your life easier in the long run. But just to reemphasize what Alan said about not needing to store the archived records in another table, take a look at what I did with your db. I created two queries that simulates the two tables that you wanted to have. Then I modified the form to use the query instead of the table. Hope this further clarifies things for you.

Good luck with your project.