Move Record From One Table To Another ?

D

Dave Elliott

I have a Customers table where I wish to move via the customers form a
record to another table tblarchivedcustomers
How can I do this? I wish to do it via an on change event of a combo box.
i.e. status change from active to archived
The combo only has 2 choices "Active";"Archived" (No Default) is
set
 
A

Allen Browne

Dave, why are you moving the record, instead of just leaving them in the
same table, and using the combo to set their status? It is dead easy to then
filter the records to show only the Active ones in any form or report, with
a query.

If you want to move them anyway, you need to execute an Append query to add
the record to the other table, followed by a Delete query to remove it from
this table. For any all-or-nothing result, you need to wrap both queries in
a transaction. Details of how to do that in:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html
 
A

Allen Browne

You can set the Filter of your form to:
Status = 1
FilterOn = True
and then show all records again with:
FilterOn = False

Alternatively, if you have 2 queries - one that returns only Active records
(WHERE Status = 1), and the other that returns all records, you can change
the form's RecordSource:
Me.RecordSource = "Query2"

If you have actually moved the records to a different table, life is more
complex. Not recommended.
 
Top