Use Form button to move current record to another table

J

JMageeirbc

I have a database which consists of computer names and model numbers.
As time goes on the computers get old and are no longer in use. I also
have an identical table that I have been copying the old computers to.
Once copied to the old computer database I delete the computer record
from the current computer database. Is it possible to accomplish this
task with one button?
 
A

Al Campagna

Say you had a button on each record called cmdArchive, and it's purpose
is to move this record to an archive table, and delete that same entry from
your current table.
Each record should have a unique identifying key value. (ex. EquipmentID)
Use that value to run an Append query from the active table to the
archive table for just the record with that EquipmentID.
Then, run a Delete query against your active database, to remove the
record with that EquipmentID.

Private Sub cmdArchive_Click()
DoCmd.RunSQL 'your Append query SQL here
DoCmd.RunSQL 'your Delete query SQL here
End Sub

When every thing works correctly, you can add...

Private Sub cmdArchive_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL 'your Append query SQL here
DoCmd.RunSQL 'your Delete query SQL here
DoCmd.SetWarnings True
End Sub

to defeat the action query dialog boxes when the queries run..
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
J

Jerry Whittle

You should only have one table. In it put a Yes/No field named something like
"In_Use". When no longer in use, set the field to No. In all your queries
where you want to see only in use computers put Yes in the criteria for the
In_Use field.

That way you don't need to move records between tables or do things like
Union queries when you need to see both in use and not in use computers.
 
E

Ellie

Hi Al,

I am trying to do this exact same thing. My problem is that my form is
based directly from the table and not a query. How to I attach the query to
my form? Thanks. Happy Holidays!

Ruth,
 
J

Jerry Whittle, Microsoft Access MVP

You should only have one table. In it put a Yes/No field named something like
"In_Use". When no longer in use, set the field to No. In all your queries
where you want to see only in use computers put Yes in the criteria for the
In_Use field.

That way you don't need to move records between tables or do things like
Union queries when you need to see both in use and not in use computers.

As far as attaching a query to a form, first create the query will all the
fields needed by the form and give it a unique name. Next open the form in
design view and go to its properties. Go to the Data tab and type in the name
of the query or go to the drop down where the query should be listed.
 
Top