vba 将具有多个值的查找列中的数据复制到新记录 Access 2007

Copy data from lookup column with multiple values to new record Access 2007

提问by Lauren

I am copying a record from one table to another in Access 2007. I iterate through each field in the current record and copy that value to the new table. It works fine until I get to my lookup column field that allows multiple values. The name of the lookup column is "Favorite Sports" and the user can select multiple values from a dropdown list.

我正在 Access 2007 中将记录从一个表复制到另一个表。我遍历当前记录中的每个字段并将该值复制到新表。它工作正常，直到我到达允许多个值的查找列字段。查找列的名称是“Favorite Sports”，用户可以从下拉列表中选择多个值。

I believe the values of a multivalued field are stored in an array but I cannot access the values in VBA code! I've tried myRecordset.Fields("myFieldName").Value(index)but it didn't work. I don't understand how Access stores multiple values in one field.

我相信多值字段的值存储在数组中，但我无法访问 VBA 代码中的值！我试过myRecordset.Fields("myFieldName").Value(index)但它没有用。我不明白 Access 如何在一个字段中存储多个值。

I saw something about ItemsSelected on another forum but I don't know what Object is associated with that method.

我在另一个论坛上看到了一些关于 ItemsSelected 的内容，但我不知道什么 Object 与该方法相关联。

Thanks for any help!

谢谢你的帮助！

回答by David-W-Fenton

I would recommend against using multivalue fields for precisely the reason you're running into, because it's extremely complex to refer to the data stored in this simple-to-use UI element (and it's for UI that it's made available, even though it's created in the table design).

我建议不要使用多值字段，正是因为您遇到的原因，因为引用存储在这个易于使用的 UI 元素中的数据非常复杂（并且它是为 UI 提供的，即使它是创建的在表设计中）。

From your mention of "ItemsSelected," you seem to be assuming that you access the data in a multivalue field the same way you would in a multiselect listbox on a form. This is not correct. Instead, you have to work with it via a DAO recordset. The documentation for working with multivalue fielsexplains how to do it in code, something like this:

从您提到的“ItemsSelected”来看，您似乎假设您访问多值字段中的数据的方式与在表单上的多选列表框中访问数据的方式相同。这是不正确的。相反，您必须通过 DAO 记录集使用它。使用多值字段的文档解释了如何在代码中执行此操作，如下所示：

  Dim rsMyField As DAO.Recordset

  Set rsMyField = Me.Recordset("MyField").Value
  rsChild.MoveFirst
  Do Until rsChild.EOF
    Debug.Print rsChild!Value.Value
    rsChild.MoveNext
  Loop
  rsChild.Close
  Set rsChild = Nothing

Now, given that you can usually access the properties of a recordset object through its default collections, you'd expect that Me.Recordset("MyField").Value would be returning a recordset object that is navigable through the default collection of a recordset, which is the fields collection. You'd think you could do this:

现在，假设您通常可以通过默认集合访问记录集对象的属性，您会期望 Me.Recordset("MyField").Value 将返回一个记录集对象，该对象可通过记录集的默认集合导航，这是字段集合。你会认为你可以这样做：

  Me.Recordset("MyField").Value!Value.Value

This should work because the recordset returned is a one-column recordset with the column name "Value" and you'd be asking for the value of that column.

这应该有效，因为返回的记录集是一个列名为“Value”的单列记录集，并且您会要求该列的值。

There are two problems with this:

这有两个问题：

1. it doesn't actually work. This means that Me.Recordset("MyField").Value is not reallly a full-fledged recordset object the way, say, CurrentDB.OpenRecordset("MyTable") would be. This is demonstrable by trying to return the Recordcount of this recordset:

   Me.Recordset("MyField").Value.Recordcount
   

   That causes an error, so that means that what's being returned is not really a standard recordset object.

2. even if it didwork, you'd have no way to navigate the collection of records -- all you'd ever be able to get would be the data from the firstselected value in your multivalued field. This is because there is no way in this shortcut one-line form to navigate to a particular record in any recordset that you're referring to in that fashion. A recordset is not like a listbox where you can access both rows and columns, with .ItemData(0).Column(1), which would return the 2nd column of the first row of the listbox.

1. 它实际上不起作用。这意味着 Me.Recordset("MyField").Value 并不是像 CurrentDB.OpenRecordset("MyTable") 那样真正成熟的记录集对象。这可以通过尝试返回此记录集的 Recordcount 来证明：

   Me.Recordset("MyField").Value.Recordcount
   

   这会导致错误，因此这意味着返回的内容并不是真正的标准记录集对象。

2. 即使它确实有效，您也无法导航记录集合——您所能获得的只是多值字段中第一个选定值的数据。这是因为在此快捷单行表单中无法导航到您以这种方式引用的任何记录集中的特定记录。记录集不像列表框，您可以使用 .ItemData(0).Column(1) 访问行和列，后者将返回列表框第一行的第二列。

So, the only way to do this is via navigating the child DAO recordset, as in the code sample above (modelled on that in the cited MSDN article).

因此，执行此操作的唯一方法是通过导航子 DAO 记录集，如上面的代码示例（模仿引用的 MSDN 文章中的示例）。

Now, you could easily write a wrapper function to deal with this. Something like this seems to work:

现在，您可以轻松编写一个包装函数来处理这个问题。像这样的事情似乎有效：

  Public Function ReturnMVByIndex(ctl As Control, intIndex As Integer) As Variant
    Dim rsValues As DAO.Recordset
    Dim lngCount As Long
    Dim intRecord As Integer

    Set rsValues = ctl.Parent.Recordset(ctl.ControlSource).Value
    rsValues.MoveLast
    lngCount = rsValues.RecordCount
    If intIndex > lngCount - 1 Then
       MsgBox "The requested index exceeds the number of selected values."
       GoTo exitRoutine
    End If
    rsValues.MoveFirst
    Do Until rsValues.EOF
      If intRecord = intIndex Then
         ReturnMVByIndex = rsValues(0).Value
         Exit Do
      End If
      intRecord = intRecord + 1
      rsValues.MoveNext
    Loop

  exitRoutine:
    rsValues.Close
    Set rsValues = Nothing
    Exit Function
  End Function

Using that model, you could also write code to concatenate the values into a list, or return the count of values (so you could call that first in order to avoid the error message when your index exceeded the number of values).

使用该模型，您还可以编写代码将值连接到一个列表中，或返回值的计数（因此您可以先调用它，以避免在索引超过值的数量时出现错误消息）。

As cool as all of this is, and as nice as the UI that's presented happens to be (it would be really nice if they'd added selection checkboxes as a type for a multiselect listbox), I'd still recommend against using it precisely because it's so much trouble to work with. This just takes the problem of the standard lookup field (see The Evils of Lookup Fields in Tables) and makes things even worse. Requiring DAO code to get values out of these fields is a pretty severe hurdle to overcome with a UI element that is supposed to make things easier for power users, seems to me.

尽管这一切很酷，而且与所呈现的 UI 恰好一样好（如果他们将选择复选框添加为多选列表框的类型，那就太好了），我仍然建议不要精确地使用它因为工作太麻烦了。这只是解决了标准查找字段的问题（请参阅表中查找字段的弊端），并使事情变得更糟。在我看来，要求 DAO 代码从这些字段中获取值是一个非常严重的障碍，需要克服一个 UI 元素，该元素应该使高级用户的工作更轻松。

回答by onedaywhen

For a quick and dirty way of getting the values outof a multivalued ('complex data') column, you can use an ADO Connectionwith the Jet OLEDB:Support Complex Dataconnection property set to Falsee.g. the connection string should look something like this:

为了得到值的快速和肮脏的方式进行多值的（“复杂数据”）栏，您可以使用ADOConnection与Jet OLEDB:Support Complex Data连接属性设置为False例如连接字符串应该是这个样子：

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\dbs\TestANSI92.accdb;
Jet OLEDB:Engine Type=6;
Jet OLEDB:Support Complex Data=False

The multivaled type column will now be of type MEMO(adLongVarWChar) with each value separated by a semicolon ;character.

多值类型列现在将是MEMO( adLongVarWChar)类型，每个值用分号;字符分隔。

But that's only half the problem. How to get data intoa multivalued column?

但这只是问题的一半。如何将数据放入多值列？

The Access Team seem to have neglected to enhance the Access Database Engine SQL syntax to accommodate multivalued types. The 'semicolon delimiter' trick doesn't work in reverse e.g.

Access 团队似乎忽略了增强 Access 数据库引擎 SQL 语法以适应多值类型。“分号分隔符”技巧不起作用，例如

INSERT INTO TestComplexData (ID, weekday_names_multivalued) 
   VALUES (5, 'Tue;Thu;Sat');

fails with the error, "Cannot perform this operation", ditto when trying to update via ADO recordset :(

失败并出现错误“无法执行此操作”，尝试通过 ADO 记录集更新时同样如此:(