Audit trail code not picking up combobox list changes

Question

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

What do you mean "it does not work"? Please provide a better description of what you want to do, what you did, what you expected to happen, and what you observed including any error messages. — Steve Piercy, Aug 17 '17 at 23:59
Note that audit trails using VBA in Access are easy to bypass, even when you're working with a compiled database file. In Access, the go-to option for auditing is data macros. See [this page](https://scottgem.wordpress.com/2012/10/18/audit-trail-using-data-macros-2/) for a sample implementation. — Erik A, Aug 18 '17 at 10:00
I initially did the audit function with macros, new<>old, but found the code to be better and not as many audit table fields. When I mention it does not work - let me explain this better. I am creating an inventory scheduling database. There are 8 possible quarterly date changes that are text boxes. The audit code picks up changes to those text boxes. I also need "change reason" & "change initiator" dropdowns the user has to use when inventory dates change. There are 8 change reasons & 5 initiators to choose from. Changes to either of those dropdowns do not show in the audit table. — Erika, Aug 18 '17 at 12:38
Probably don't need these, but the audit table fields are: LogID, FormName, ControlName, FieldName, RecordID, UserName, OldValue, NewValue, TimeStamp — Erika, Aug 18 '17 at 12:40
I suppose I could add the macro back and those fields back in the table,just for the new and old drop down fields....the macro did work for those. — Erika, Aug 18 '17 at 13:03
Disregard, I recreated test macro fields into a new table and recreated the macro - still only records the text box changes and not the combobox changes. — Erika, Aug 18 '17 at 14:02

Erik A · Accepted Answer · 2017-08-21T09:39:49.710

1

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

edited Aug 21 '17 at 09:39

answered Aug 21 '17 at 09:34

Erik A

31,639
12
42
67

Thank you! My combobox changes are now recording. The only problem is if the user selects one value from the list because I don't have a save button, I just keep the form as save when they move from the field - the audit trail is recording every single change even if they accidentally select one value and then choose another. Any ideas on how I can keep the last field change when the person hits save? – Erika Aug 21 '17 at 19:28
As you're not actually using `Control.OldValue` anymore, you could just use the `Form_BeforeUpdate` action to call this code. You could create a collection of changed controls (for each control, in their After update event, check if they are already added to the collection, and add them if not), and then call this code for each control. Also, this allows you to cancel record updates if auditing somehow fails. – Erik A Aug 21 '17 at 19:51
@Erika I still haven't received a response on that last change. Does it work as wanted? If so, please mark this answer as the correct one. Else, please comment on what isn't working. – Erik A Aug 25 '17 at 11:38
Yes, it's working - but not seeing how to mark it correct. – Erika Aug 25 '17 at 15:00
There should be a greyed out check sign next to the vote buttons (see https://i.stack.imgur.com/fdNG0.jpg) – Erik A Aug 25 '17 at 15:00

Audit trail code not picking up combobox list changes

1 Answers1