Automated transfer between tables

S

ScotStuart

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
 
R

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
 
D

Daz

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?
 
Top