
Code needs amended to track data changes
Hi,
Original post was placed in formscoding forumn with no luck. Can someone
help me out here?
Listed below is the code from Microsoft's KB on how to track to changes to
records using a form. It works well but the problem is, as my form has 30
text boxes, if I made a change in one text box it will list that change but
it will also list the other 29 boxes as having no change! The result is
that my memo is getting populated with useless info about fields which
haven't changed.
It would be great is someone could amend the code so that it only shows
details of the records that I have changed only using text, combo or
ListBoxes.
Very much appreciated and very much needed.
Tony
Function AuditTrail()
On Error GoTo Err_Handler
Dim MyForm As Form, C As Control, xName As String
Set MyForm = Screen.ActiveForm
'Set date and current user if form has been updated.
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"Changes made on " & Date & " by " & CurrentUser() & ";"
'If new record, record it in audit trail and exit sub.
If MyForm.NewRecord = True Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
"New Record """
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 Updates field.
If C.Name <> "Updates" Then
' If control was previously Null, record "previous
' value was blank."
If IsNull(C.OldValue) Or C.OldValue = "" Then
MyForm!Updates = MyForm!Updates & Chr(13) & _
Chr(10) & C.Name & "--previous value was blank"
' If control had previous value, record previous value.
ElseIf C.Value <> C.OldValue Then
MyForm!Updates = MyForm!Updates & Chr(13) & Chr(10) & _
C.Name & "==previous value was " & C.OldValue
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