Automated transfer between tables

[Guest]

Is it possible to build a macro or some other function to transfer records
from a table say where status changes from pending to closed? e.g orders
fulfilled

 

[Roger Carlson]

It is certainly possible to do this, but I would advise against it. You
should have all your orders in one table with a field (OrderStatus) which
tells what the status of the order is. Then you can query on the order
status. Having pending orders in one table and closed orders in another is
very bad relational design.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Yes it is possible.

You need an append query first of all that will actually perform the
transfer of the records from one table to the other.
Then you may wish to remove the records from their original table, so you'll
need a delete query, to remove them.
Your macro simply needs to call the queries in the order append then delete.
Obviously you could setwarnings to false so that you don't get asked whether
or not you really want to do it.

If you need it to happen on a Form you could check the on change event of
your status control for whatever status triggers the transfer and then call
the macro to perform it. You'll probably need to refresh the form afterwards
in order for the changes to show.

Hope that's of some help?

 

[Joseph Meehan]

Is it possible to build a macro or some other function to transfer
records from a table say where status changes from pending to closed?
e.g orders fulfilled

Sure it is possible, you would use an append query and likely would want
to do some clean up to automate it, but why? It is almost always more
efficient to add a status binary field to identify inactive records. You
can then easily hide those records using a simple query or filter statement.