Track changes of raw table data information

[jacjacjac]

Hello,

NOTE: Im working in MS Access 2003, result in ACcess ONLY please

Ok this might be strange or not possible;

Im creating an audit trail for users actions in a form, to monitor and keep track of what fields were updated/entered/removed from the database, these actions are essentially done from command buttons (and the audit trail, is coded on the button click)

However, I have a few search screens, that return a subform listing results. These datasheets returned are effectivly like opening the raw table and hence are editable, (permission to change is granted as they are admin only searches, yes, they can change the raw table)

How can I track the change to a raw table field, ie, if someone updates the field SURNAME from 'Smth' to 'Smith', is there something that triggers this (obviously if it was in an interface textbox, you could audit this) is their a field change. The only way i can suggest it make a recordset of the intial results, then compare that to the results in the table as they leve and compare the two. any ideas?

Thanks,

jac

[Rabbit]

All forms, and controls on those forms, regardless of how you display the form, should have events that can trigger. You can use those events to create your audit trail. Therefore, it is not "raw" data in the strictest definition of the word. What I mean is a datasheet view of a form is still not the "raw" data. Displaying a form in datasheet view is not effectively like opening the table as you say in your first post.

You can not audit "raw" data in the strictest definition. That being opening the table directly and editing the data.

If you are using Access 2010, you can use a data macro which is similar to a trigger in other databases.

[NeoPa]

Do you know the event that triggers when data is updated in Datasheet mode Rabbit?

[Rabbit]

I would assume the BeforeUpdate or AfterUpdate event would trigger. I no longer have 2003 so I can't test in that environment but I know it triggers at least from 2007 onwards.

[NeoPa]

I have 2003 but nothing that uses Datasheet mode. I seem to recall hearing (reading) that none of the events fire when in Datasheet mode. If they do in later versions I'll see if I can knock something up to have a look (I still have 2003).

[NeoPa]

Update: Both control and form AfterUpdate() events fired just fine. That clearly wasn't something I remembered properly (or alternatively whoever wrote it was wrong. I prefer the second explanation :-D).

@Jac,

With that information are you happy you have all you require to keep an audit trail in your database?

[jacjacjac]

Hello,

Topic: Auditing results in a datasheet (which i have learnt is more similar to a FORM than a table)

So I now use the AfterUpdate method/function/sub and it triggers when a field is changed.
The question i have is how to determine exactly what value was changed, if there is 100 records and i change the 10th one, it triggers when i leave the field so can i extract the value (in the field) that was changed (ie, the new updated value, not what is previously was)

The method/function/sub used:

Private Sub PersonID_AfterUpdate()

End sub

Thanks,

Jac

[zmbd]

jacjacjac:

I have just such an event that works for several forms in a V2003 database that are in the datasheet mode.

The concept I use is that the Before_update event fires for the the current record. I use a simple message box to confirm the change. The message is a string that is built by pulling the old values and the current values from each field of the current record. If the two values match (other than the primary key) then I ignore it otherwise the field name with the old and new values is added to the message string. If the user confirms that the changes are correct as shown, then I record the string the table name, the primary key in that table, and the date and user information in the audit table and let the update finish.

Now in the afterupdate, I use the table name and the primary key to find the entry in the audit table. I then parse the string to get the new values recorded recorded earlier in the before update and compare those against the current values in the record. If there's a difference I record the current values in the audit table along with a note that there is an issue. This has happened once or twice... usually because I didn't handle a null or a user entering an invalid date format or something... there for the record save was canceled.

You should be able to take the above and write some code that we can work with. :)

Yes, I could just post you a complete example; however, that would be like telling you the end of a good book or movie.

[jacjacjac]

I understand the methodology behind what you are/wish to achieve but i need a piece of code to get the field value from a datasheet. Thats the part i can't do, if it was a textbox on a form it would be NameOfTextbox.V alue, but this is a value in a subform datahseet on a form. Any code would help?

[Rabbit]

It's the same. Like I said earlier, a form displayed in datasheet view is still a form. The code you use would be the same. There are textboxes on there. The events still fire. The code is the same.

[NeoPa]

Referring to Items on a Sub-Form explains how to reference those items Jac. Something else to consider, which may or may not be appropriate in your situation, is whether the code should be run from the main form at all. Maybe the code should run from the form itself (IE. The sub-form).

NB. In case you aren't aware, controls that manage data have a .OldValue property that reflects the original value for the related field in the current record. I expect this property is what Z was working with in his Before_Update() procedures. Once the update has occurred this property becomes the new value, so trying to access it from the AfterUpdate() procedure would be a waste of time (The two versions would always be identical by that stage of the process).

[zmbd]

Neopa has it correct in the order of events and the reasoning.
The before update to get at the old values
The after update to ensure something didn't bugger the record save such as an index or validation violation.

Generally, I do not give code. If however, you will post your attempts then we can work thru the puzzle.

[ADezii]

FYI, it is my understanding that the OldValue Property of a Control can be retrieved in its AfterUpdate() Event. It is not necessary to capture it in the BeforeUpdate() Event of the Control.

[zmbd]

Adezii:
Although the MS textbooks tend to give that impression, I've not found this not to be true in practice.

Easiest way to show this for yourself is to place a debug.print for a control so as to print the .value and .oldvalue in each of the before_* events, the on_current, and finally for each of the after_* events.

What I've found is that only the Before_* events will these properties contain the old value and the edited value. Once the after-update event fires, the old value is lost.

Indeed, My very first attempt at verifying the value of a control against the former value was a miserable failure in that I kept checking for the old value in the after-update type events. I actually use a variation of the the solution offered thru Allen Brown's website http://allenbrowne.com/appaudit.html which I found to be a bit overkill for my purposes. So was this http://www.vb123.com/toolshed/00_acc...ditrecords.htm

[ADezii]

@zmbd:
I am speaking about Control related Events, not the Form's.