I want to be able to track or 'audit' all changes done to records in my database. Essentially, there are records in the database, e.g PEE-1000, which would have a wealth of information asssociated with that record. How would i track every change done to that record? including user name, type of change, value before change and after change, field name changed, etc.
I know system objects can do a little bit of that, but it tracks changes done to queries, tables in general. does it state wat the change was? and the user?
If I wanted to that I would have do it "manually" and this roughly what I would do. I say roughly because I will probably forget a couple of things here.
Firstly, I would put a series of unbound text boxes on the form, but hidden, and one for each field that you needed a record of.
Secondly, when someone clicked in any field a SetValue macro action (or counterpart in code) would set the value of the unbound text boxes to match the fields. Thus the existing record details would be stored.
I would then need to replace the Access navigation buttons with my own and then have a macro that would run when my navigation buttom was clicked and I would also replace the Access close X with my own Close button or label so I have the macro run when the form was closed.
The macro would run if any of the entries in the unbound text boxes were different to the fields that they matched up with.
The macro would open a form at a new record (and based on another table) and then set the value of the fields in that record with the value of the fields on your form and then it would open the form again at a new record and set that new records fields with the value of the unbound text boxes. It would then close the form and then clear the unbound text boxes.
You could also have it SetValue a field in the other form/table with something like "changes" for the values taken from the fields on your form. You would also have the macro set an unbound text box with the time and use that value to set the value in each of the two new records created in the other form.