Can this 'field specific' change log code be modified to apply to the whole form?

[gojets1721]

I have a table that captures changes made to my form's fields. Here's the module's code:

Function ChangeLog(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 = CurrentDb.OpenRecordset("tblChangeLog", dbOpenDynaset, dbSeeChanges)
  
    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !Fieldname = strControlName
        Else
            !Fieldname = strField
        End If
        !EventNumber = lngID
        !Username = Username()
        !StaffMemberName = StaffName()
        !TimeStamp = Now()
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With

    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

Then I call the module in a specific's field's before update event:

Private Sub txtFollowUp_BeforeUpdate(Cancel As Integer)
    Call ChangeLog(ComplaintNumber, "FollowUp")
End Sub

My question is: is it possible to alter this code so that it tracks every field in the form? If I wanted to do that right now, I'd have to call the module individually in every field's before update event which doesn't seem ideal (i have 60-70 fields in this form).

I was wondering if it's possible to alter the code so that it could just call it in the form's before update event or elsewhere

 

[gemma-the-husky]

It's actually quite difficult to track every change, and use that history. You ought to think carefully about why you want to track changes. If a contact changes his contact phone number for instance, why would you need a history of old phone numbers.

 

[arnelgp]

not tested:

Function ChangeLog(ByVal lngid As Long, Optional ByRef frm As Form = Nothing)

    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
    Dim tStamp as Date

    Dim ctl As Control
  
    If frm Is Nothing Then
        strFormName = Screen.ActiveForm.Name
        Set frm = Forms(strFormName)
    Else
        strFormName = frm.Name
    End If
  
    tStamp = Now()
    Set dbs = CurrentDb()
    Set rst = CurrentDb.OpenRecordset("tblChangeLog", dbOpenDynaset, dbSeeChanges)
  
    For Each ctl In frm.Controls
        If TypeOf ctl Is SubForm Then
            Call ChangeLog(lngid, ctl)
      
        Else
            If TypeOf ctl Is TextBox Or _
                            TypeOf ctl Is ComboBox Or _
                            TypeOf ctl Is ListBox Or _
                            TypeOf ctl Is CheckBox Or _
                            TypeOf ctl Is OptionGroup Or _
                            TypeOf ctl Is OptionButton Then
          
          
                strControlName = ctl.Name
                varOld = ctl.OldValue & ""
                varNew = ctl.Value & ""
              
                If varOld <> varNew Then
              
                    With rst
                        .AddNew
                        !FormName = strFormName
                        !ControlName = strControlName
                        If strField = "" Then
                            !FieldName = strControlName
                        Else
                            !FieldName = strField
                        End If
                        !EventNumber = lngid
                        !UserName = UserName()
                        !StaffMemberName = StaffName()
                        !TimeStamp = tStamp
                        If Not IsNull(varOld) Then
                            !OldValue = CStr(varOld)
                        End If
                        !NewValue = CStr(varNew)
                        .Update
                    End With
                  
                End If
        End If
    End If
              

    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

you only need to pass the PK value on the Form's BeforeUpdate:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call ChangeLog(ComplaintNumber)
End Sub

 

[gojets1721]

Just remember, when you log the changes in the form's BeforeUpdate event, you WILL end up logging changes that never happened because the update didn't happen because an error occurred during the save -- unless you handle that situation.

How would you suggest to handle that situation?

 

[gojets1721]

I gave you a suggestion in #3. I don't have any code to post and I'm not building any at this point. There are other alternatives. See the Allen Browne solution below. It is the most comprehensive I've examined.

The discrepancy may not be important to you. Do you have a clear idea of how you will use the logged data? That should help you to decide how important the issue is and how frequently a record goes into the BeforeUpdate event and doesn't actually get saved. If you are not bothering with validation, then your code won't stop the bad record from being saved and therefore won't interfere with the logging process. Then all you have to worry about is whatever RI you defined at the table level.

I haven't used Allen's code but others recommend it. This is a copy but there is a link to the website inside. Go to the website and get the most recent version in case there has been a change.

Apologies; I didn't even realize there were other comments besides your last one (yesterday was long). I'll give this all a shot and circle back with any issues. Thanks!