I have a need for a "track-changes" capability in Access 2010. I ran across this script which should do the trick with a little work.
It's clear and understandable but it doesn't quite yet handle all of the cases I need for textboxes. (I'll implement other controls later.) This is the pertinent structure as written. It's called as an event procedure for the Before Update event on a form:
Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Control)
'Track changes to data.
'recordid identifies the pk field's corresponding
'control in frm, in order to id record.
Dim ctl As Control
Dim varBefore As Variant
Dim varAfter As Variant
Dim strControlName As String
Dim strSQL As String
On Error GoTo ErrHandler
'Get changed values.
For Each ctl In frm.Controls
With ctl
'Avoid labels and other controls with Value property.
If .ControlType = acTextBox Then
If .Value <> .OldValue Then
'Add record of change to logging table
End If
End If
End With
Next
Set ctl = Nothing
Exit Sub
ErrHandler:
'Handle error
End Sub
This works as long as the values within the text box are not blank. If one of .Value
or .OldValue
is null (or nothing -- I'm confused on this point) then the inequality fails.
I tried updating the part within With ctl
to the following. This allows me to log changes for which one of the values (before or after) was null. The rest of the commented-out stuff is my attempt to record all of the cases where the value is null, but doesn't change. In other words, the IsNull(ctl)
lets everything through, but I couldn't figure out how to filter the cases of equality back out again.
If .ControlType = acTextBox Then
If IsNull(ctl) Or .Value <> .OldValue Then
'If Not (Len(.Value & vbNullString) = 0 And Len(.OldValue & vbNullString) = 0) Then
'If oldValueIsNull Then
' varBefore = "--NULL--"
'Else
' varBefore = .OldValue
'End If
'If newValueIsNull Then
' varAfter = "--NULL--"
'Else
' varAfter = .Value
'End If
'Add record of change to logging table
'End If
End If
End If
Any help is appreciated.
If you are happy to just avoid the whole null issue, which may not suit everyone, you can say
If .Value & "" <> .OldValue & "" Then
Which will compare strings.