MS Access: Track/Log table changes from the table form (Record Level)

This seemed to me a very easy thing to do at the beginning, but it got complicated and very challenging. I am pretty new to MS Access, so -after struggling for many days- I hope to find the solution here.

Say we have a simple table like this:

Student Table
Now, I need to track changes on this table on the record level and Log the changes in another table (Say Log table) like this:

Track/Log Table
Where track_type:

1 for add(insert)
2 for edit(update)
3 for delete

The changes(add/edit/delete) should be logged when the record on the Student Form is changed.

My Problem is that I am not able to generate the changes correctly.

My form has to refresh a lot which make excessive entries in the log table like this one:

bad logging example
As you can see, instead of one log for the insert, it takes two logs to insert a record.

I searched the net thoroughly, but I cannot find this kind of logging. I found information about logging on the field level, but this is not what I want. I know it may be better in most cases, but not for my case.

So, I will appreciate any guiding information/code.

a minimized database that focuses on the problem is posted Here and you can download it and see what I have tried and where I am stuck.


1- Changes should be logged only when the user: - Move from the current record on the form. - Exit the form
2- Changes Should not happen while on the same record even if these changes committed to the Student table.
3- Refresh is unavoidable.
4- Audit Trail is not helpful if on the field level.
5- Data Macro is not helpful as changes can be committed more than once.
Shadi SalehAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Assuming you have a form bound to this table, you would use code in the BeforeInsert, BeforeUpdate and BeforeDelete events.  It would essentially be the same for all three:
s = "INSERT INTO tblLog (TrackID,st_ID, st_Name, st_Age, track_type) VALUES (" & me.TrackID & ", " & Me.st_ID & ", " & chr(34) & Me.st_Name &  Chr(34) & ", 1)"
currentdb.execute s, dbFailOnError + dbSeeChanges

Open in new window

Note that the Chr(34)'s are double quotes, used to delimit text values.

The only difference between the code in the different events would be the "1" at the end, which would need to be changed to 2, 3, etc depending on whether you are tracking insert, updates or deletions.

You might also need to check for a new record in the BeforeUpdate event to ensure that Inserted records don't get double-logged:

If me.newRecord = false then
   '... above code goes here
end if

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In addition to Miriam's advice, Allen Browne has an excellent writeup on creating an Audit Log here:
Shadi SalehAuthor Commented:
Thank all, I already tried that.

My problem happens because after/before insert/update events occurs more than one time while I am on the same record. I guess it is because the "Me.Refresh" method. So, I cannot depend on these events. I am trying to do all of them from the current event via detecting changes then log them. The problem is that on current event itself can occur more than once without changing the record on the form.

I think I need a kind of event like (Form_Before_Record_Departure).
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

There is no "Before_Departure" event in Access, so you would have to code custom navigation and form close buttons and set the visible property of the built-in buttons to false/No, to give you greater control over what happens at the precise moment when the user changes records.

The code to insert the record into the log table would have to be placed before GoToRecord statement:

GoToRecord Method
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My problem happens because after/before insert/update events occurs more than one time while I am on the same record
You could also use the "flag" method. Add a form-level variable named "fLogged" (or something like that), and then check the value of that before you write to the log:

If Not fLogged Then
  <write to your log>
  fLogged = True
End If

Then in the Current Event of the form, set fLogged back to False.

If you need to track different types of logging (like Updated, Added, etc), you can use multiple flag variables (fUpdateLogged, fAddLogged, etc).
Dale FyeOwner, Developing Solutions LLCCommented:
Then lets go back and ask a leading question, why are you refreshing the form so often?

You might also want to read my article on "Simple Audit Log" which uses a slightly different process than that used by Allen Browne in his article.

I would question why you are refreshing the form.  A side effect of Me.Refresh, as you have discovered, is that the record is saved.  If your form is allowing an incomplete record to be saved - and it is - then your validation code is in the wrong event because it is not protecting you.

Let's get to the bottom of the multiple refreshes and then you'll be able to use standard methods and hopefully get your validation code into the correct event (Form's BeforeUpdate) so that it actually keeps bad data from being saved.
Shadi SalehAuthor Commented:
Thanks all,

Let us say at the beginning that database is provided as it is and the need is to add this log function without changing the interface and without re-write  the existing code.

I went through Dale Fye page and it is very nice, but unfortunately it serves other purpose.

Scott McDaniel, I already tried flag method, but here is the difficulty to me. I am not able to reset the flag in the correct place due to the way events work.

PatHartman, Your idea seems promising for me. Cancelling any updates or insertion until moving to the next record. but this solves part of the problem. I still need to know somehow that the record is about to change on the form to a different record. otherwise if the record is full of data and I want to commit all of them as one transaction my tests against empty fields will fail.

I will try to mix flags and checks together and see what can I come up with.

Meanwhile, I welcome any other suggestions/solutions.

The main point as I can see it is all about creating -by vba code- an effect similar to un-existing event (Form_BeforeRecordChanging).
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I would agree with Pat regarding the multiple Refresh events. While I'm sure there are reasons for this in some applications, the overwhelming majority of apps generally do not need to continually refresh forms. What's the reasoning behind that? Perhaps we could help you come up with a different method to achieve what you need.
I still need to know somehow that the record is about to change on the form to a different record. otherwise if the record is full of data and I want to commit all of them as one transaction my tests against empty fields will fail.
There is an event that does that for you but you don't seem to be using it.  You can force a record to save deliberately by issuing a save command.  You can force  a record to save accidentally by using Refresh or Requery or setting Me.Dirty to false.  Access can decide it needs to save the record because you move focus from the main form to a sub form or scroll to the next record or close the form or close the database  or any number of other reasons.  The point I'm making is that there is ONE and ONLY ONE event where you can be CERTAIN that your validation code can actually prevent a record from being saved and that event is the Form's BeforeUpdate event.  As long as the bulk of your validation code is in that event, you can be certain that required fields are present, values are valid based on range or relationship checks.

Based on the symptoms you described, your validation code is NOT in the BeforeUpdate event because it is allowing incomplete records to be saved.

If you can tell us what you are trying to accomplish with the multiple "Refresh" commands, we can help to resolve the problem.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Shadi SalehAuthor Commented:
Thank you very much all. specially PatHartman, using your contribution and suggestions I was able to do it in more than way.

using flags gives a great control over the code and gets you where you want, however the before_update event is great and eliminate all the excessive work.

I do not remember why I gave up using it although it was the first thing I tried.

Anyway. It is solved and thanks for all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.