Code needs amended to track data changes 
Author Message
 Code needs amended to track data changes


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

Very much appreciated and very much needed.


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

    Exit Function

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

Wed, 07 Jan 2004 01:07:53 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Changing CD Track in code

2. Data Handling - Problem with tracking changes

3. Tracking data append and updates with code

4. Code for Sat tracking needed

5. Changing Data Type using code

6. Table Data Type Change thru Code

7. Code to change data type in table

8. need module code to change all records in a table

9. Change a field's data type through code

10. Changing a fields data type in code

11. Need to Avoid Verifying Table Changes with Code

12. Changing Field Data Type with Code


Powered by phpBB® Forum Software