using a date as archiving trigger

[Grasavong]

Hello,
I have a question that I'm not sure if it's possible for Access 2003. I'm
creating a database where we would like to use a project's end-date as the
trigger for archiving the record (row) to an 'archived' or 'completed' table.

For example: Suppose Project #10 was finished on 10/10/09. Once someone
enters that field (which is called 'Completed Date') with a date, the record
is automatically removed from the current table and sent to an 'archived' or
'completed' table.

I was wondering if this is possible instead of having to manually cutting
each row that has a finished project to the completed table.

 

[Piet Linden]

Hello,
I have a question that I'm not sure if it's possible for Access 2003. I'm
creating a database where we would like to use a project's end-date as the
trigger for archiving the record (row) to an 'archived' or 'completed' table.

For example: Suppose Project #10 was finished on 10/10/09. Once someone
enters that field (which is called 'Completed Date') with a date, the record
is automatically removed from the current table and sent to an 'archived'or
'completed' table.

I was wondering if this is possible instead of having to manually cutting
each row that has a finished project to the completed table.

Unless you have a LOT of data (in excess of 2 million rows or so), you
shouldn't need to do this... If you filter for archived/non-archived
and base queries on that, everything will work fine. Just make sure
you index the 'Completed Date' column.

You could do what you want in the AfterUpdate event of the
CompletedDate control on your form. Something like:
-- NOTE: both queries will have the same filter (e.g. = Me.RecordID)
DBEngine(0)(0).Execute "appendQuery"
DBEngine(0)(0).Execute "deleteQuery"

I would STRONGLY recommend testing this out on a junk table. So if
you make a mistake you don't mess up your real data.

 

[KARL DEWEY]

There is a better and simpler way to do what you desire.
Add criteria to your queries of [Completed Date] Is Null for all
using currrent data.

If you need an annual report all projects, open and completed are in the
same table to provide yearly totals.

If you insist on doing it your way then in a macro named Autoexec have 2
actions. The first to append from current records to archive table all that
have a date in the [Completed Date] field. The second action to delete all
current records that have a date in the [Completed Date] field.

 

[Jeff Boyce]

As others have already pointed out, "moving" records is not necessary or
desireable in a relational database.

For example, should you wish to take a look at all the projects managed by
PersonA, over the last 10 years, do you really want to have to find the old
archive tables and add all those records to the ones that are current (or
more recent) to find all of PersonA's projects?!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.