Access97

I have a bound form with several bound subforms.
When the user clicks "save" I would like to review all the changes and write
the field name, old value & new values for each change.

My first inclination was to create a snapshot copy of the recordset at the
time the form opened and then make another recordset with the same query
after the save operation and then compare the two and make the list of
changes.

The problem is that, if a record has been deleted, you cannot refer to the
deleted record even if it is in the snapshot recordset and the snapshot has
not been updated. The data is there (I can view it in the debug window), but
Access will not let me refer to it.

Next, I thought about making arrays of the original data and then comparing
the arrays with the saved data, but the arrays go out of scope after the
form completes loading so they are not there after the save. I've tried
creating the arrays with "Static" and this doesn't help.

Does anyone have any suggestions?

Randy Sermons

Take a look at the discussion and suggested solution:
    Audit Trail - Log changes at the record level.
at:
    http://users.bigpond.net.au/abrowne1/AppAudit.html

The article addresses edits, inserts, and deletes for a form. It can also
be implimented for a subform.

--
Allen Browne, Microsoft MVP (Most Valuable Professional)
Perth, Western Australia. To reply, remove "_SpamTrap".
Tips for MS Access users:
    http://users.bigpond.net.au/abrowne1