Saving changes to a multivalued ComboBox via AuditTrail

Question

I have a Access 2010 Database using a multivalued field (the Access inbuilt way to have m:n-relation between two tables).
To keep track of changes to the database I use an AuditTrail VBA procedure every time the corresponding form is updated, saving all the changes to history table.

Now, when I change the value of the ComboBox and the loop reaches the ComboBox bound to the multivalued field, the procedure throws an error because of incompatible Data types:

    For Each ctl In Screen.ActiveForm.Controls
    If ctl.Tag = "History" Then
        If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
            With rst
                .AddNew
                ![timestamp] = datTimeCheck
                ![UserName] = strUserID
                ![FormName] = Screen.ActiveForm.Name
                ![recordid] = Screen.ActiveForm.Controls(IDField).Value
                ![FieldName] = ctl.ControlSource
                ![beforeValue] = ctl.OldValue
                ![afterValue] = ctl.Value
                .Update
            End With
        End If
    End If
Next ctl

How do I get the actual Value and the OldValue from a combobox converted to string in VBA?

I tried combobox.focus and then combobox.Text This works, but doesnt help with the OldValue problem.

How to properly use the value and oldvalue property of comboboxes? The official VBA object reference for comboboxes doesn't help at all. https://msdn.microsoft.com/en-us/library/office/ff821691.aspx

Answer 1

When ctl references a multi-valued combo box with at least one item selected, your test condition ...

Nz(ctl.Value) <> Nz(ctl.OldValue)

... will certainly throw a type mismatch error. In that situation, ctl.Value is actually a variant array, and Nz() can't cope with it. The problem is essentially the same as this ...

Debug.Print Nz(Array(1,2,3)) '<- type mismatch error

Perhaps you would prefer to grab a string of the concatenated combo selections ...

Debug.Print Join(ctl.Value, ",")

If that seems useful, beware that ctl.Value will be Null when none of its items is selected. And attempting to Join() Null will also trigger type mismatch error.

Note those issues also apply to ctl.OldValue.

But there may be yet another complication. Based on my testing I'm skeptical whether OldValue is reliable for a multi-valued combo box. If you also find that to be the case, use the form's current event to store the combo's initial selections in a form level variable and reference that variable (instead of OldValue) in your audit procedure.

Answer 2

It is not an elegant solution, only a quick & dirty workaround:

Modify your code so that it checks differently ComboBox1 and the other controls. The .Value and .OldValue are basically an array of variants.

Dim afterValue as variant
Dim beforeValue as Variant

For Each ctl In Screen.ActiveForm.Controls
    If ctl.Tag = "History" Then
        If Ctl.name = "ComboBox1" then
           err.clear
           on error resume next
           I=0
           afterValue = ""
           beforeValue = ""
           while err=0
               '
               ' Throws an error if 'out of range', i.e. after the last value
               '
               afterValue = afterValue + Nz(Cstr(ComboBox1.Value(I))) + ";"
               beforeValue = beforeValue + Nz(Cstr(ComboBox1.OldValue(I))) + ";"

           wend
        else
           afterValue = ctl.Value
           beforeValue = Nz(ctl.OldValue)
        endif
        If Nz(ctl.Value) <> a$ Then
           With rst
              .AddNew
              ![timestamp] = datTimeCheck
              ![UserName] = strUserID
              ![FormName] = Screen.ActiveForm.Name
              ![recordid] = Screen.ActiveForm.Controls(IDField).Value
              ![FieldName] = ctl.ControlSource
              ![beforeValue] = beforeValue
              ![afterValue] = afterValue
              .Update
           End With
        End If
    End If
Next ctl