通过AuditTrail将更改保存到多值组合框 [英] Saving changes to a multivalued ComboBox via AuditTrail

问题描述

我有一个使用多值字段的Access 2010数据库(Access的内置方法是在两个表之间具有m:n关系).
为了跟踪对数据库的更改，每次更新相应的表单时，我都会使用AuditTrail VBA过程，将所有更改保存到历史记录表中.

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.

现在，当我更改ComboBox的值并且循环到达绑定到多值字段的ComboBox时，由于数据类型不兼容，该过程将引发错误:

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

如何从组合框中获取实际的Value和OldValue在VBA中转换为字符串?

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

我尝试了combobox.focus，然后尝试了combobox.Text 这可行，但是对解决OldValue问题没有帮助.

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

如何正确使用组合框的value和oldvalue属性?组合框的官方VBA对象参考完全没有帮助. https://msdn.microsoft.com/en-us/library/office /ff821691.aspx

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

推荐答案

这不是一个优雅的解决方案，只是一种快速的&肮脏的解决方法:

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

修改您的代码，以便对ComboBox1和其他控件进行不同的检查. .Value和.OldValue基本上是一个变体数组.

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

这篇关于通过AuditTrail将更改保存到多值组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！