Moving Entries from One table to another

[BlindEagle4283]

So I have 2 tables, the first is for "open" orders (where entries get edited changed and deleted), and the Second is for "finished" orders (where once an order is completed it should move from the open orders table to the finshed one where it will no change and will remain for futre reference)

I need help with the transfering part of this problem. I would ideally like to make a form that allows you to pull up the info for the open order then edit it to finally add the finished order to the other table. Im not sure where to start on this one.

 

[The_Doc_Man]

First, if your history files aren't going to be very large, don't move everything right away. Just add a field that says "CLOSED" (or, if you prefer, "MOVE AT NEXT ARCHIVE CYCLE").

Then, build your archive table to have the same structure as your main table.

Then build a pair of queries. The first does an APPEND of all marked items in the main table to your archive table. The second (and don't do them in the wrong order) does a DELETE of all marked items from the main table. Run this query once per day, once per week, once per month ... you decide the "right" frequency based on your business needs.

(Why do it this way?) This minimizes table locking issues by minimizing the time of exposure for moving data around. Just marking the record when you close the order takes only a moment to update and lock the record, then you are done. This also allows you to run a daily report of transactions moved by filtering the table the other way - on closed / ready-to-move records.

Now, to keep your forms from seeing the marked items that have not been moved yet, don't run the form from the table. Run it from a query that only shows you unmarked items.

 

[BlindEagle4283]

Thanks that makes a lot of sense. However, Due to the nature of how we do things; it makes more sense to archive the orders as they are completed (since the archive is actually the key component for this project), and that leads to my other issue. As these orders are filled documents are created (PO's, Drawings, Memos, etc..) and I'd like to be able to add information on what there is and where its filed when these orders are archived.

So i thought I could make a form that takes an entry from 1 table and then lets me add the information in the extra fields that the archive table would have to have, and then append that entry to the archive including the new info.

Would that work? Is there and easier/better way?

 

[BlindEagle4283]

any help would be greatly appreciated!