Audit Trail Help (1 Viewer)

[HeatherO]

Good afternoon,

I have audit trail tracking set up in my database using the following:

Function LogChanges(lngID As Long, Optional strField As String = "")
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varOld As Variant
Dim varNew As Variant
Dim strFormName As String
Dim strControlName As String

varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.Value
strFormName = Screen.ActiveForm.Name
strControlName = Screen.ActiveControl.Name
Set dbs = CurrentDb()
Set rst = dbs.TableDefs("tblTracking").OpenRecordset

With rst
.AddNew
!FormName = strFormName
!ControlName = strControlName
If strField = "" Then
!FieldName = strControlName
Else
!FieldName = strField
End If
!RecordID = lngID
!UserName = Environ("username")
If Not IsNull(varOld) Then
!OldValue = CStr(varOld)
End If
!NewValue = CStr(Nz(varNew, 0))
.Update
End With
'clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function


I have VBA set up to Call LogChanges on the "Before Update" event in certain fields on a form. I have a field called "Status" that pulls from a value list. It always defaults to "Call" and when we're done with the billing, we change it to "Work Completed". Since I've set up the tracking, I've had issues with this field. If we change this field FIRST, it works just fine. If we change this field after changing any other field, we get the following error.

Any ideas why this works if we change the status FIRST, but errors if we change it after making changes to any other field?

Thanks,
Heather

 

[theDBguy]

Hi. I could be wrong but maybe OldValue is only available in the BeforeUpdate event of a control.

 

[pbaldy]

dbGuy is correct but I suspect the error is due to a control being used that doesn't have an OldValue property. In other events OldValue would simply report the current value rather than the old value you want. Hit debug at that point and type this into the immediate window:

?Screen.ActiveControl.Name

or put this line before the one causing the error and see what the variable contains:

strFormName = Screen.ActiveForm.Name

What type of control is being tested (textbox, etc)?

 

[HalloweenWeed]

It really depends on how the form is used, but I have found the best way to set-up a reliable audit trail is to create a logging table, with more than twice the fields of the audited table: a field for each previous field, and a field for each for changed values. Then a field for the username as logged-on name:

User_Modified = Environ("UserName")

and finally a date/time of modification.
For instance, if you have two fields:

Field1
Field2

you create 4 in the auditing table:

Prev_field1
New_field1
Prev_field2
New_field2
plus:
User_Modified
Date_Modified

(not showing the index field - Key - here.)

You open the log table recordset in append-only mode (so you can't overwrite anything already there) and add a new record to the table upon the form's "current" event (each time a record is accessed), immediately use the .AddNew command (.Edit is not used with .AddNew), write all the "Prev_" Fields, and only write the "New_" fields when the change event happens - write all New_ fields, regardless of how many has changed, you can overwrite with new changes later if another field changes, you can keep writing them all infinite times before the .Update command is issued. You can use a "change" flag to indicate that a change has occurred, and thus code an .Update command upon exiting (Unload event) the form. If the change event doesn't happen, you close the recordset without saving the record, to prevent a huge amount of unnecessary records. Upon the next "current" event, you save the changes (.Update) and then record (rinse/repeat).

 

[pbaldy]

I don't think I like the lack of normalization in the audit table.

 

[Minty]

Me neither - I have clients tables of sparse data that have 60 + fields, that would be a nightmare to manage!

 

[HalloweenWeed]

I don't think I like the lack of normalization in the audit table.

For normalization, you merely do the same things with all the different tables, in different audit tables.

 

[pbaldy]

For normalization, you merely do the same things with all the different tables, in different audit tables.

Oh, I disagree. What you have is no different than having Product1, Product2... in an invoice table instead of a related table with a record for each product purchased. If you add a field to the transaction table, you have to add 2 new fields to your audit table.

 

[HalloweenWeed]

Oh, I disagree. What you have is no different than having Product1, Product2... in an invoice table instead of a related table with a record for each product purchased. If you add a field to the transaction table, you have to add 2 new fields to your audit table.

Hmm, I am eager to learn! I already read Minty's (new) Normalization explanation.

 

[pbaldy]

Hmm, I am eager to learn! I already read Minty's (new) Normalization explanation.

That has probably explained it better than I can!

 

[Minty]

That has probably explained it better than I can!

I've just updated the sig link - It had moved a bit!!