We help IT Professionals succeed at work.

Access Record Edits in form

Murray Brown
Murray Brown asked
on
Hi
I have an Access form were the RecordSource is a table called "t_Licence". If the user edits any field I want to keep a record of what the field was before and after. What events and VBA code would I use to do this?
Comment
Watch Question

Systems architect
BRONZE EXPERT
Top Expert 2006
Commented:

That is not a new requirement and many people have different approaches to it.

Take as starting point this article:

http://allenbrowne.com/AppAudit.html



Scott McDaniel (EE MVE )Infotrakker Software
SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
BRONZE EXPERT
Most Valuable Expert 2014
Commented:

It is not a simple task.  At heart, you need to cut off users ability to do ANYTHING to the data EXCEPT through interfaces that you have coded.  Depending upon your present design that may be simple (you already have a healthy scepticism about users screwing up data and have built a UI that already controls data change) or hard ( you give users things like datasheet views and split forms where you the developer exercise no control)

You have seen two takes on this task.  This tangentially is a third

https://www.experts-exchange.com/articles/6692/Overcoming-unbound-continuous-forms-limitations-by-staging-data.html

What you do is put data in a ‘temp’ location and bind your form to the temp data each time the form loads.  The users are then NOT mucking with the real data and you can code both the changes to the real data and the audit trail through events on the form ( a commit button perhaps)


No matter what, it isn’t a simple task, since YOU have to code all the CRUD operations yourself and twice — once for the actual data operations and once for the audit records

Nick67


Scott McDaniel (EE MVE )Infotrakker Software
SILVER EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:

If all you're after is (a) what the data state was before the change and (b) what the data state is after the change, then the audit log methods suggested would suffice. Using temp-bound forms for situations like this is just overkill.



BRONZE EXPERT
Most Valuable Expert 2014

Commented:

<grin>
IF and it is a big if, you’ve built some fairly secure forms already, then staging data might be more work.  If you’ ve got loosey-goosey forms, it might be less.  Much too depends on how rigorous the data auditing needs to be

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much for the advice