Search code examples
vbams-accessdrop-down-menucomboboxaudit-logging

Audit trail code not picking up combobox list changes


I have looked at this post: Saving changes to a multivalued ComboBox via AuditTrail

And tried to take tidbits and put it into my code, but it just didn't work! I am not really great at SQL but I need to get this done. Here is my code and it works for textboxes, but can someone tell me exactly where and exactly what to put what I need for my combobox dropdown list changes?

Thanks in advance!!

Function LogChanges(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 = dbs.TableDefs("ztblDataChanges").OpenRecordset

    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !RecordID = lngID
        !UserName = Environ("username")
        If Not IsNull(varOld) Then
            !OldValue = CStr(varOld)
        End If
        !NewValue = CStr(varNew)
        .Update
    End With
    'clean up
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
End Function

Solution

  • You can't get the values of multi-valued fields using .Value and .OldValue. These properties always return Null. As far as I know, there's no reliable way to get the old value (also, a proper audit trail doesn't need an old value, since the old value is the previously added new value if everything gets audited properly).

    When only saving the new values, and if you're saving them into a text field and not a multivalued field, you could use the following:

    Use this function to get a string value for all selected items:

    Public Function JoinMVF(MVFControl As Control, Optional Delimiter As String) As String
        Dim i As Variant
        For Each i In MVFControl.ItemsSelected
            JoinMVF = JoinMVF & MVFControl.ItemData(i) & Delimiter
        Next i
    End Function
    

    And then, adjust your recordset piece to the following:

    With rst
        .AddNew
        !FormName = strFormName
        !ControlName = strControlName
        If strField = "" Then
            !FieldName = strControlName
        Else
            !FieldName = strField
        End If
        !RecordID = lngID
        !UserName = Environ("username")
        If Not IsNull(varOld) Then 'varOld will always be Null for a multi-valued field
            !OldValue = CStr(varOld) 'Thus this will never get called
        End If
        'Add some If multivalued field then
        !NewValue = JoinMVF(Screen.ActiveControl, "; ")
        .Update
    End With