Audit Trail / History

Posted on 2005-05-12
Last Modified: 2008-01-09
Hello Experts,

    I realize that this question has been asked and answered many times and I am aware of the different approaches previously suggested but I think I have a fresh approach and would like to hear everyone's thoughts.

Basically I will be using a class object to register my forms with that will hook the AfterUpdate, AfterInsert, AfterDelete events and write the data along with a timestamp to a seperate History database.

I already have a function called myOpenForm which is a wrapper around the DoCmd.OpenForm that adds the form to the myForm and myForms classes and then calls the DoCmd.OpenForm. I can expand the myForm class to hook the events and write to history but am thinking that I need the equivalent of a Msys table to track which forms I need to track history on and I think I am going to need to write additional code to see if the form has subforms and adds them to the myForm / myForms class tree.

I am still in the early stages of thinking about this and wonder if I should expand to having myControl and myControls classes tree'd to the myForm class so I can iterate values and only write history if there really was a change or if the .Value and .OldValue going to give accurate returns?

Question by:stevbe
    LVL 26

    Assisted Solution

    Hey Steve.

    From the sounds of it, you need something to not only track data changes, but object changes too...?
    Is that right..?

    Tracking data changes is quite simple (there's an example at for tracking history). At the same site, there is also a way to trap an entry into unbound controls by usig globals.

    I suppose the easiest way would be to have an OldValue and CurValue global, which is looped through when teh form is exited, and it's values appended/updated into a table.
    LVL 39

    Author Comment

    Thanks for you interest,

    I am only interested in tracking data history not objects.

    The code from the link you posted would require I write code in and for all my forms and have seperate variables for every field, this is way more work and maintenance than I think is necessary.

    Can you give me your thoughts on my proposed process?

    LVL 48

    Accepted Solution

    You will probably need WithEvents for this.

    Study the demos at John Colby's site:

    LVL 39

    Author Comment

    Thanks, I have WithEvents working fine :-)
    LVL 48

    Expert Comment

    by:Gustav Brock
    You already did - or you have now?

    LVL 39

    Author Comment

    I have been using With Events for a few years.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now