Move Record From One Table To Another ?

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
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
 
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
 
How do I then show all records since I now have a Like '1' in my query
criteria for my customers form.?
1 is the default which is active and 2 is archived . The field in the
customers table is named Status and is a numeric value.
In code form via my customers form, I have a label that i want to click to
show all records.
Tried DoCmd.ShowAllRecords, but that didnt work...?
 
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.
 
Back
Top