Tracking changes in records

[Mason Hall]

I am building a database which I want to track current and historical changes
to employee information. For example I want to be able to report on current
employee location, supervisor, etc... and past employee information.

I am stuck with how to get the query to work and how to get a form which
will pull current employee record but also allow me to update just fields
that are changing and create a new record for each time updates occur.

Thus far I have created a parent table (with employee IDs and name) and a
child table (with employee detailed information, unique transaction ID and
transaction date).

Any help is greatly appreciated!

 

[dcichelli]

I can help you with this....

The difficult part is making code work with a subform. However, if you
are only having users input data into a main form


Here is an example of a function that writes change records to a hidden
form bound to a table to track changes to records.


Function AuditTrail()

' Created on 1/18/2005 by Diane Cichelli
' Purpose of function is to record any changes to the active form
' when the form's "Before Update" event property is set to
=AuditTrail()

On Error GoTo Err_Handler

Dim MyForm As Form, C As Control, hideform As Form
Set MyForm = Screen.ActiveForm

'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
DoCmd.OpenForm "frm_audit_main_sub", , , , , acHidden
DoCmd.GoToRecord acDataForm, "frm_audit_main_sub", acNewRec
Set hideform = Forms!frm_audit_main_sub
hideform!ChangeDate = Now
hideform!IssueKey = MyForm!Issue
hideform!Type = "New Record"
hideform!DetailKey = 0
DoCmd.Close acForm, "frm_audit_main_sub", acSaveYes
End If

'Check each data entry control for change and record old value of
Control.


For Each C In MyForm.Controls
'Only check data entry type controls.
Select Case C.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup
' Skip non-editable fields .
If C.Name <> "Field" And _
C.Name <> "Previous" And _
C.Name <> "Type" And _
C.Name <> "NewVal" Then

' If control was previously Null or empty, record "previous
' value was blank. Do nothing if new value is also blank or
null

If IsNull(C.Value) Or C.Value = "" Then
If C.OldValue = "" Or IsNull(C.OldValue) Then
Else
MyForm!Field = C.Name
MyForm!Previous = C.OldValue
MyForm!Type = "Delete"
MyForm!NewVal = C.Value
End If
Else
If C.OldValue = "" Or IsNull(C.OldValue) Then
MyForm!Field = C.Name
MyForm!Previous = C.OldValue
MyForm!Type = "Edit"
MyForm!NewVal = C.Value

' This code records the new and old values if there
is a change

ElseIf C.Value <> C.OldValue And C.Value <> "" Then
MyForm!Field = C.Name
MyForm!NewVal = C.Value
MyForm!Previous = C.OldValue
MyForm!Type = "Edit"
End If
End If
If IsNull(MyForm!Field) Or MyForm!Field = "" Then
Else
DoCmd.OpenForm "frm_audit_main_sub", , , , ,
acHidden
DoCmd.GoToRecord acDataForm, "frm_audit_main_sub",
acNewRec
Set hideform = Forms!frm_audit_main_sub
hideform!ChangeDate = Now
hideform!IssueKey = MyForm!Issue
hideform!DetailKey = 0
hideform!Field = MyForm!Field
hideform!Previous = MyForm!Previous
hideform!NewVal = MyForm!NewVal
hideform!Type = MyForm!Type
DoCmd.Close acForm, "frm_audit_main_sub",
acSaveYes
MyForm!Field = ""
MyForm!NewVal = ""
MyForm!Previous = ""
MyForm!Type = ""
End If
End If
End Select
Next C

TryNextC:
Exit Function

Err_Handler:
If Err.Number <> 64535 Then
MsgBox "Error #: " & Err.Number & vbCrLf & "Description: " &
Err.Description
End If
Resume TryNextC

End Function

 

[Mason H]

dcicchelli:

Thanks for the code! Unfortunately, my code skills are admittedly weak.

If I am reading it correctly. The code would create an audit trail
(seperate table) which would record the date and information on fields that
were changed. But, would not create a full record of the employee's
information (repeating fields that did not change)?

Would the AuditTrail allow me to later query and report on an employee's
full record as of a given date?

Thanks again!

 

[Ron2006]

Change the logic to have it update a table with same construct as the
original table with a couple of extra fields like when done by whom.
and on the change write out the whole record and then a copy with the
changes.

Search the groups on audit log, there may be some more examples out
there that are closer to what you want to do.

Another option is not allow changes except through one form that only
has one record at a time can make a prior copy ONLY then they have
finished all changes and said to save the record. It is more work on
this end but be easier to audit changes from the other side.

 

[Ron2006]

With the first option if you select the first OLD version for a
timeframe and the last NEW version in that time frame you now have a
before and after image.

 

[dcichelli]

Just add the primary key from the employee table to the audit trail
table and then query on all the fields you want to see...

 

[Mason H]

I want to make sure I am understanding the concept of what is going on.

1. I am inserting the code (using the code builder ... button) on the Before
Update event of my form (frmEmployeeInfo - which I created off of my table
called EmployeeInfo). This table EmployeeInfo contains the details of all
the employee's job assignments.
2.After the installing the code. I will use my form (frmEmployeeInfo) to
update employees' records. A new table and hidden subform should show up in
my tables and form objects listing.
3.From the new AuditTrail table I should be able to query to get historical
information based upon Associate ID.

What is the appropriate way to reference this code in my Event Procedure.
Do I build the code outside the form and then reference the name
AuditTrail(). Or just open the event builder/code builder and paste the
appropriate code?

thanks!

 

[dcichelli]

1. Yes that is the way to insert the code.
2. No - you will need to create the new table and hidden subform
referenced in the code
3. Yes and if you create a query joining your AuditTrail table to
EmployeeInfo on Associate ID you will get the change info plus the
unchanged fields.

reference it in the before update section as =AuditTrail()

 

[Italiangirl]

1. Yes that is the way to insert the code.
2. No - you will need to create the new table and hidden subform
referenced in the code
3. Yes and if you create a query joining your AuditTrail table to
EmployeeInfo on Associate ID you will get the change info plus the
unchanged fields.

reference it in the before update section as =AuditTrail()

 

[Italiangirl]

I am confused and new at VB.
Can you tell me exatly what to name my tables and my forms to make this work
so I can test it and then learn how it all goes together.
Then I can use it for a different database.
Thanks