If it were on SQL Server, you could code in a nice trigger to do this. If your data entry/deletions is done via a form, on the delete button you could put in some code which copies the data to a separate table before deleting. I would make sure you have the fields DateDeleted and DeletedBy (get the loginID of the user - see posts on this) as definate fields to have in this "DeleteLog" table. In regards to editing the record, how detailed do you want to get? If you simply want to know that some field of the record is edited, that can be accomplished by adding a DateModified and ModifiedBy field to the main table and then on the form's AfterUpdate event, put some code in to write in today's date/time and the loginID of the user into those fields. If you want to get at the detailed level of what field was edited (i.e. old value verses new value, etc), it gets a little more complex. I know I used a routine regarding editing a table on a form where I picked out the main fields which I wanted to track changes on and had a "ChangeLog" type of table where I put in some vba code on the AfterUpdate of those specific fields (taking into account that there was an existing value and it was not a new record). The code would then write to the "ChangeLog" table if the values were changed (again with the DateModified and ModifiedBy fields in that table and what field was modified.)
The key to whole thing being you want to have Date fields to track when the change/deletion was made and some type of field representing the loginID of who changed/deleted the record. Otherwise, your log tables are somewhat useless (plus it's fun to track down the culprit of why any report totals change because someone edited/deleted a record.)
Last edited by pkstormy; 09-14-06 at 14:18.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)