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

[June7]

You can't. Access 2003 doesn't have the abillity to put code behind tables (Access 2010 introduced Data Macros) and therefore not behind queries. Must use forms and reports.

[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

[June7]

You don't show any code in that procedure. Review this article about maintaining an audit log. http://allenbrowne.com/appaudit.html

[jacjacjac]

Obviously there is no code, hence the question, how to retrieve the value that has been changed in the datasheet view?

The code is going to go into the sub....but how can i get a value from the datasheet that has been changed, if it was a textbox on a form it would be NameOfTextbox.value.

Private Sub PersonID_AfterUpdate()


End sub

[June7]

Not really understanding what you want. If you want the new value just input into a record available for further use after leaving the record, then populate an unbound control or a global variable before leaving that record. Probably need to use the BeforeUpdate event.

Me.unboundtextboxname = Me.NameOfTextbox

or

glblVarName = Me.NameOfTextbox

[jacjacjac]

Ok, so I dont seem to be getting anywhere.
Imagine you have a datasheet that has 7 columns (fields) with about 100 rows of data in it.
If a user happens to change the 3rd coloum in row 68, then how can i dtermine programitcally that this column was changed?
The afterupdate event triggers, yes, correct, but the trigger isn't the issue, its getting the column/field changed and value that was changed.
(as i said if it was textbox on a form, it would be textboxname.value)

Now the datasheet is a subform, after reading the posts, i thought i will just access the subform is code: (something like)

Forms!NameOfSubform.subformvalue (that was changed),

However this relies on you needing to know the value that was changed (hardcoded), so what i need is when the trigger for afterupdate is done, it basically gives me the data i need, so effectivly gives me the subform name and field and value. I NEED THE CODE TO DO IT not the principal.

[June7]

Data controls have Value and OldValue properties. Both properties can be addressed in the BeforeUpdate and AfterUpdate events.

I suppose the form BeforeUpdate event could compare the OldValue and Value properties of each data control to determine if any changed. Complicated code.

Certainly the BeforeUpdate event of each control would be triggered if there is a change.

Unless you maintain an audit log of all changes, there is no way to track that a value was ever changed after record is committed to table.

I don't understand purpose of this.