Combining non-similar records

[RossWindows]

I have an audit trail table that stores every change made to a particular field.
It stores EVERY change, so if a user opened the form changed the field to one thing and then changed it back to another, the audit trail stores it as two changes, but essentially the user just changed it from one thing to another.

I attached a sample of what I'm talking about.
It includes some explanations where there are two records stored, but I just want it to show as one record per the explanation in the .xls

 

[ajetrumpet]

the excel file is not much help (at least to me Ross). is this an access ?, or an excel ?

my guess is that you will need to use variables to store initial values open opening of the form, and then analyze the changes from there. possibly having to revert back to the previous values that were stored in the variables you made. make sense?

 

[RossWindows]

Yes, this is an access question, I just used Excel to show a representation of my audit trail table.

Your idea of storing initial values in variables upon opening moving to a record and then recording the changes when the form is closed is a pretty good idea. It would also have to run if the user moves to a different record on the same form.

Should I use Form_BeforeUpdate, Form_Current or a different event for something like that?

I've also reattached the .xls with an added explanation. Hope that helps a little.

 

[ajetrumpet]

this looks like a project to take on ross. Perhaps you should look at farming it out to an IT person that can give you a polished sample? or finished work? That's what I would suggest. it's just too long to talk about here on the forum for simple stuff. IMO.

 

[Dennisk]

there are a couple of techniques you could look at, one is the forms me.Dirty property. This is set to true if any field is changed, or you could do it your self by creating a onChange event for each data entry field where you would set a flag in code. The latter technique will allow you to monitor changes to individual fields by maintaining a dirty flag for each field. In which case you need to create a boolean variable for each field.

 

[RossWindows]

...Perhaps you should look at farming it out to an IT person that can give you a polished sample?...

We don't need no stinkin' IT!

I am using the OnChange event of the field with an if-then statement;
Works fine for me.

If Field1.NewValue <> Field1.OldValue then
     AddAudit Field1.OldValue, Field1.NewValue, fOSUserName(), fOSMachineName(), Now(),  Etc
End If

 

[gemma-the-husky]

one way is to save all the changes in the FORM's before update event, rather than for each control's before update event

then you can look at all the data controls, and if control.oldvalue <> control.value then save the change. this automatically bypasses any aborted changes

eg - what do you do if a user starts a change the presses <esc> to undo it?