Before Update record logging - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Before Update record logging

Brian Crawford

3 years ago

I have a Sub that want to I use in a form that will write field-by-field before-and-after values for only the fields that have changed in a record.  It works well, but I can't figure out how to properly initiate it.  I tried putting the code in the Before Update section, but it ends up logging changes even when the user just edits the field, but doesn't actually choose to save the record.  For example if a user changes a field value, then moves to another field on the form, then either escapes out or changes the field back (as with a mistake),  This is because the Before Update triggers when the user leaves the field after changing a value (before any attempt to save or cancel happens), so this causes my log Sub to add an entry that I don't want.

I tried using the Before Update at the field and form levels, and get the same behavior.  I have also watched the Before Update video on this site.  I can't figure this out.

Is there a way to change where I call this Sub and/or VBA code changes I can add to make this log happen only when the record is being changed?  I could cheat and prevent changes and add a Save button, but I want to use my Sub in several different forms and associated RecordSource tables so I don't want to add buttons all over the place, and I would also like to use this with Datasheet forms.

Kevin Yip @Reply

3 years ago

You need to compare the field values from the moment the user enters the record (On Current) to the moment he leaves the record -- compare the field values in those two moments, and that's how you track the changes.  If the user has made changes, AfterUpdate will run after he leaves the record.  If the user hasn't made changes, AfterUpdate won't run.  Specifically, when the Current event runs (when the user first enters a record), you use variables to store all the field values at that moment.  When the AfterUpdate event runs, you compare the variables to the fields' current values, and log the changes accordingly.  If the user didn't make changes, AfterUpdate won't run and won't log changes, exactly what you intend.

A text box also has a .OldValue property that stores the value of the text box before a user changes it.  But that may be useless in your case because the user may make multiple changes while he is in the record, but .OldValue will only store the last change.  .OldValue may be useful in some other circumstances so you may want to keep it in your back pocket.

You also need to think about how you should log additions or deletions of records, because those are changes too.  You could use the On Delete and AfterInsert events to check for record additions and deletions.

Brian Crawford OP @Reply

3 years ago

Thanks for the reply, but as I mentioned, My Sub already works successfully to identify the changes and write them into a history table, which has fields for tableName, fieldName, dataType, oldValue, newValue, Modified, and ModifiedBy. My issue is how/where to call it.

When I was building and testing it, I used the debugger and entered data into the form then called the Public Sub using the VBA Immediate Window. It did exactly what I wanted to do, but when I added it to the Before Update events in the controls in the form, I noticed that it would log changes every time the user made a change in value on a control and moved away from it. I do not want to call my Sub until the point just before the record is update. Another option is to continue to use the Before Update event, but add lines in my code to exit the sub whenever the record data is being updated.

One solution that could work would be to remove navigation and create a Save button that could call my Sub and update the record in an OnClick event. I prefer to not use this report, because I would like to also use this feature in Datasheet views where a user is editing table.

Kevin Yip @Reply

3 years ago

As I said, you put the code in the form's Current and AfterUpdate events. To prevent logging repetitious user changes, you only need to check two places:

- The field values when the user first enters the record, i.e. Current event.
- The field values when the user exits the record; and if the record's data have been changed, the AfterUpdate event will run.

Compare the two sets of field values, and you know which change(s) you need to log.

Brian Crawford OP @Reply

3 years ago

Kevin, thanks for the help. I found the issue. It was (as you mentioned) related to control.OldValue. I had previously compared control.Value to control.Text instead of control.OldValue, and in order to use control.Text, the control mush have focus, so I had other code to save the current focus and move it around to each control so that I could check for changes. This moving around of the focus confused the BeforeUpdate event and caused it to trigger multiple times. I had changed my value checks to compare control.Value to Control.OldValue, but forgot to remove the focus stuff that I had when I was using control.Text.

Once I removed the code shifting around of focus, everything worked as expected.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View