Archiving an individual file.

[Guest]

Hello,
In my database there are forms where the information on an individual can be
viewed. On the form I wish to have a button that will archive the current
information viewable into another table. This will be done with an ammend
query and a delete query. But how can i make it just archive the individual
file rather than all the files in the table. Can this be done with the ID No
(autonumber) ?

 

[Rick B]

Generally, you would not do this. Just add an "INACTIVE" date field or
checkbox to your table and form. When this is entered, the record will be
considered "inactive". You can exclude these records from your forms,
reports, and queries.

If they come back, you just remove the entry. If you ever want to see
active and inactive items, it is all in one place.

Since your forms reports and queries should rarely be based on a table (but
on a query instead) there is almost no benefit in doing what you are trying
to do and moving them to other tables.

For more details, read the hundreds of previous posts that deal with
"archiving" records. You will find similar responses to almost all of them.

It is very very rare that you will move records around in a properly
constructed relational database.

 

[Guest]

Thank you for your response. I already have a fully working archiving system
for bookings made in my system. But as part of the archiving facility that
has been requested for this database, i will also need to archive records on
individuals in the system. For bookings this is simply done by date, anything
older than todays date is moved to an archived table which has the same
structure as the table is was moved from.
The reason for doing this is to keep up-to-date quieries in the system.
Due to the nature of this system, the solution you have stated would be
unsuitable. For the archiving of an individuals data, it will follow the same
method for archiving bookings, however as it cannot go by date, it must go by
the ID number currently being viewed on the form, is this possible?
thanks

 

[BruceM]

I suppose you could use the individual's ID as the query criteria just as
you have used a date range as the criteria for what you are already doing,
but I have not tried it since there is, as Rick pointed out, almost no need
to archive records in a properly designed database. What you have said
about archiving because you need to keep queries up-to-date does not make
sense. If you mark a record as inactive (by adding aYes/No field named
Inactive) it will not show up when you run a query in which the criteria for
the Inactive field is Yes. Base your form or report on that query, and you
have saved yourself a lot of work.

 

[Guest]

Thankyou. How can i use the individuals id though in the criteria of the
append and delete queries. The only way i know of is to have [enter id] which
would mean the user has to type in the desired ID. But is there a way that
the curently displayed ID on the forms can be copied/read straight into the
criteria of the append and delete queries?

 

[BruceM]

Maybe you could just copy it to your Windows clipboard, and paste it from
there. As for automating the process, I cannot think of a way offhand. I
expect it would be possible to use the form to set the query criteria. A
newsgroup search should provide you with some information if you use
something like this as a search string:
Access form enter report criteria
Help also has some information on the subject. Entering report criteria
means you are entering the criteria for the query on which the report is
based, so maybe you could get it to work for your situation. That's about
all I can suggest. I posted mainly to suggest you reconsider your choice to
archive records.

Thankyou. How can i use the individuals id though in the criteria of the
append and delete queries. The only way i know of is to have [enter id]
which
would mean the user has to type in the desired ID. But is there a way that
the curently displayed ID on the forms can be copied/read straight into
the
criteria of the append and delete queries?

I suppose you could use the individual's ID as the query criteria just as
you have used a date range as the criteria for what you are already
doing,
but I have not tried it since there is, as Rick pointed out, almost no
need
to archive records in a properly designed database. What you have said
about archiving because you need to keep queries up-to-date does not make
sense. If you mark a record as inactive (by adding aYes/No field named
Inactive) it will not show up when you run a query in which the criteria
for
the Inactive field is Yes. Base your form or report on that query, and
you
have saved yourself a lot of work.