Moving Records from 1 Table to Another After Status PER is Reached

[Guest_imported]

After the Status of a record(s) gets changed to "PER", they need to be moved to a new table (and kept forever) and removed from the old one. I've tried using a macro that when a button is pushed, an append query runs, finds all occurrences of the status PER and then puts them into the other table. Great, right? Unfortunately, it does not delete them from the other table. Is there any way to do this ... move to another table and remove the occurrences from the other table? Any help would be greatly appreciated. Thanks!

 

[ghubbell]

You have to run almost the same query, this time make it a delete query and delete any records in your first table with the criteria "PER". Gord
ghubbell@total.net

 

[LarryDeLaruelle]

Word of warning from "been there, done that":

Make sure the delete query cannot be run accidentaly. It will most certainly ruin your day.
Larry De Laruelle
larry1de@yahoo.com

 

[JerryDennison]

It isn't necessary to move records to another table in order to "archive" them or make them "permanent". You can setup your forms such that those records whose designation has been changed are simply ignored. You can also setup your form(s) such that if the designation is one thing you can't edit them, only view them, and if it's something else they may be edited. You can even do this all on the same form by having an unbound checkbox in the header or footer that will "turn on" or "turn off" the display of "permanent" records. The moral of this story is, don't move the records just flag them and have your forms react accordingly.

 

[ghubbell]

This is very true although if you are dealing with a great number of records that are really no longer required, moving them out of the mainstream will increase performance. Gord
ghubbell@total.net