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.

Just guessing here, but are you storing all the data in one table....?

Yep its a exact copy, one called tbllent,the other is tbllent_history, so records from tbllent copied into tbllent_history.

I believe what Alan is getting into is that if you have a way to tell which records need to be archived, then you don't need another table to store them. You can just use a query to get the information you're looking for from the main table. Hope that makes sense.

Think im getting what u mean, im gonna try something, i might be back !

You'd definitely need a way of identifying the records as theDBGuy says, but I was more concerned that there's a problem with the table structure ?

Any chance you could post your current table structure so we can take a look for you ?

will do just need a bit of time to ort it out

This link will help you describe your table structure. Good luck.

I was looking for that link earlier

Hi, Alan. I've been looking for it for a few days for another thread. I finally found it today!

I have attached the database in question. Just to recap, when a return date is entered, it flags it up as its ok to archive, so when the archive button is pressed, it checks to see what can be archived. It will then insert the relevant records into a tbl called tbllent_history, it will only insert recrods which have a return date.When the records are inserted, they will be deleted from tbllent. The insert and delete bit I can do, I just cant get it to recgonise which records to archive.

Just taken a quick look for you, and before getting into archiving (which you don't really need to do) you should take a couple of steps backwards and redesign your tables. At the moment, your structure isn't normalised...eg in your tblMedia you have Media Artist. All those repeating names should be one name in a different table (tblMediaArtist). You'd then just store the primary of that table in your tblMedia. Likewise with the Origin, Type Of Media, Media Title fields.

Also, it's never a good idea to use spaces (or any special characters) in your naming convention.

Here's a data model for you to have a look at that should be something like you need. Have a study of it to see how it all 'fits together', and please feel free to ask any questions you're not sure about.

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.

Cheers for that I shall have a look when i'm in work.

No problem. Alan and I are happy to help. Let us know if you have any more questions. Good luck.