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.

 

[Pat Hartman]

Several things would need to change.
1. You cannot use Screen.Active. anything. Your code needs to pass in a reference to each control as well as the PK of the record and a reference to the form.

Call ChangeLog(frm As Form, ctl As Control, PK as variant)

That way, you would get the name of the form using:

FormName = frm.Name

The value of the control using:
CntlVal = ctl.Value

The old value of the control using:
CntlOldVal = cntl.OldValue

The first thing would be to determine if the value actually changed:

If ctl.Value & "" <> ctl.OldValue & "" Then '''' value changed so log record.

The PK is a variant because not all your PK's might be long integers.


There are more complications as well. You don't want to log the update until AFTER the record is saved but unless you do the logging in the form's BeforeUpdate event, you lose the ability to differentiate between changed and not changed fields. The way around this is to create an array and add the control name as well as its old value to the array. If you do the logging in the Form's BeforeUpdate event, you may be logging a change that never got committed.

Anyway, I'm not going into this piecemeal. Others have written well throughout suggestions. Just make sure you understand them and that they are complete and handle the issues I pointed out.

Start with the similar threads below.

I also agree with Dave's suggestion above. Figure out how you will use this table BEFORE you decide how to make it. Putting a record together with the changes in pieces separated widely, will be very difficult. Logging each record in total is easier but makes it harder to find when a field got changed. Figure out what your use cases are and build to that. Is this a client request or are you just doing it because you think you should. A compound solution is slightly harder to build but probably more useful in the long run. You can also determine whether you care about ALL fields or only some. The combound solution would use a record with all fields plus a change date and then a flag for each field that was changed. If the table contains 35 fields but you only care about 5 of them, then all thirty five fields are included but you have only 5 flags. Otherwise, you have a flag per field to identify that this field was changed this time.

 

[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

 

[Pat Hartman]

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.

 

[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?

 

[Pat Hartman]

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.

 

[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!