Tracking records that have been deleted

[r_shawn]

I am creating a scheduling project and I wanted to know if there was a
way to track the records that have been deleted from the database,
either from rescheduling appointments, canceling appointments, to
'no-shows'. I would like to be able to call this the 'Archive', and be
able to have it empty out every five years. I heard this was called an
'Audit Trail Form'?

If this is possible, please someone help me!!

Thanks,
Robin

 

[Rick B]

Simply add a checkbox to the record that says "deleted". NEWVER delete the
records, just mark them as deleted. Then exclude those from all your
standard queries, reports, and forms.

You might even add the current user, current date,a nd current time to a
"timestamp" field wen this box is checked.
In genral, you don't "archive" items, you simply leave them where they are
and exclude them from your other objects based on date or status. If you
run into performance issues, or have data you will NEVER need, then you
might think about 'archiving' it to a separate file, or deleting it.

 

[Craig Alexander Morrison]

Tip: If you want true Audit Trail do not allow the system to physically
delete anything.

Add a field a Yes/No (boolean) type will do to indicate whether the record
is deleted or not. You may also consider it a good idea to record the date
"deleted" along with (or instead of) the marker.

Disable all areas where the record can be physically deleted and add code to
set the above marker to true.

When running all the forms and objects that use records that are not deleted
ensure you query the new field to check that it is set to false.

The archive screens can use it when the marker is set to true.

When using this approach it is a good idea to use a Select Query as a
wrapper for the table(s). Say the table is Appointment you could create a
Query called QAppointmentDeleted and one called QAppointment. You then use
these queries instead of the base table as the basis for all subsequent
queries used in your forms and reports.

This approach allows you to query live and deleted data either together or
seperately and even recover deleted records on the archive screens by
resetting the marker.

 

[KARL DEWEY]

You could realy get fancy by adding a history table with the following fields