Move record to different table

  • Thread starter Thread starter Paul Breslin
  • Start date Start date
P

Paul Breslin

Hello -

Access 2003

I have two tables: tblContractors and tblContractorArchive
I have two forms: frmContractors and frmContractorArchive

What I want to do is place a cmd button on frmContractors that will take a
record, (the one in view), and move it to tblContractorArchive from the
tblContractors and delete it from tblContractors.

I do this operation frequently and generally do it by hand - copy/paste
But now I have a 'helper' :-)

Thanks,
Paul
 
What I want to do is place a cmd button on frmContractors that will take a
record, (the one in view), and move it to tblContractorArchive from the
tblContractors and delete it from tblContractors.
Click to expand...

Why?

Consider a much simpler alternative: add a Yes/No field to tblContractors
named Archived. Base frmContractors on a query with a criterion of False on
this field.

This will keep archived contractors in the table, just not visible in your
form; and you need no extra table, no UNION queries to look at all contractors
past and present, no code.

If you want to keep the archive table anyway, you'll need VBA code to run an
Append query followed by a Delete query. They should be wrapped in a
Transaction to ensure that either both or neither get run.

John W. Vinson [MVP]
 
True. And, one additional hint. Rather than two forms, put a control
(command button, check box, or option group) that will allow you to select
current records, archive records, or both (if you want). Just change the
filtering of the form and requery behind the control.
 
Back
Top