如何将查询中的多值字段传递给VBA函数 [英] How to Pass Multivalued field in query to VBA function

问题描述

在我的项目中，我有一个table1，它具有一个ID和一个文本字段. table2具有一个多值字段 multifield，其中我可以从table(通过查找)中选择多个值.

In my project I have a table1 that has an ID and a text field. table2 has a multi-valued field multifield wherein I can select multiple values from table (via lookup).

现在，我有query1，我想在其中从multifield中提取值-而不是multifield.value并将其传递给VBA函数，如下所示:IDsToNames: table2.multifield.问题是VBA给我这个错误:

Now, I have query1 where I want to pull the value from multifield - not multifield.value and pass it to a VBA function, like this: IDsToNames: table2.multifield. Problem is that VBA gives me this error:

The multi-valued field 'table2.multifield` is not valied in the expression IDsToNames(table2.multifield)

现在，我尝试使用IDsToNames([table2].[multifield])获得相同的结果.

Now, I've tried with IDsToNames([table2].[multifield]) with the same results.

这是我查询的SQL:

SELECT table2.Title, table2.multifield, IDstoNames(table2.multifield) AS FieldNames
FROM table2;

如果我从SQL中删除IDsToNames函数，则table2.multifield本身将返回ID，如:5, 4, 1正确.我正在尝试获取table1的第二列，而不是包含ID的第一列.所以我想我会尝试将该字段传递给函数并执行字符串拆分，然后他们在循环中使用dlookup查找它们.但是我无法将字段数据输入到函数中.

If I remove the IDsToNames function from the SQL, then table2.multifield by itself will return the IDs like: 5, 4, 1 properly. I'm trying to fetch the second column of table1 instead of the first one that includes the IDs. So i figured I'd try passing that field to a function and perform a string split and them look them up with dlookup in a loop. But I can't get the field data into the function.

有没有办法做到这一点?

Is there a way to do this?

推荐答案

是否可以通过SQL语句将多值字段直接传递给VBA函数?不，很遗憾.

Is there a way to pass a multivalued field directly to a VBA function from within an SQL statement? No, regrettably.

但是，可以使用多种替代方法来获取存储在字段中的值列表.轻松讨论Access多值字段的优缺点.我将不做详细说明，但是值得一提的是，多值字段的主要好处是Access接口的便利性和表面上的简单性，它能够自动列出并允许选择一对多关系的多个值.试图模仿Access中的行为可能非常笨拙和繁琐(通常是不可能的).多值字段的许多实现细节都是隐藏的"(即文档记载不充分或未暴露于标准SQL或VBA编程接口).这包括将多值字段从SQL语句中传递给VBA函数的功能.不管预期行为看起来多么直观，Access都不会简单地将它显示的相同的串联值列表传递给另一个函数.但是有时候，人们只是想要一个值列表，就可以在一个简单的列表中访问这些值.信息

However, there are various alternative methods that you can implement to get the list of values stored in the field. It's easy to ramble on about the pros and cons of Access multivalued fields. I'm not going to do that in detail, but it is worth stating that the primary benefit of a multivalue field is the convenience and apparent simplicity of the Access interface being able to automatically list and allow selection of multiple values of a one-to-many relationship. Trying to mimic that behavior in Access can be very awkward and cumbersome (and often impossible). Much of the implementation details for the multivalued fields are "hidden" (i.e. not well documented or are not exposed to the standard SQL or VBA programming interfaces). This includes the ability to pass the mutlivalued field to a VBA function from within an SQL statement. Regardless of how intuitive the intended behavior seems, Access will not simply pass the same concatenated list of values that it displays to another function. But there are still times when one simply wants the list of values, made accessible in a simple list. The information linked to by Gustav is useful and should be well understood for querying multivalued fields, but it still does not address other perfectly reasonable actions required for multiple values. Hopefully the following pointers are useful.

1. 如果标准SQL语句中需要这些值，则建议将主键值传递给VBA函数.然后让VBA函数查找记录，并使用DAO记录集检索多值字段值.
   • 因为这将调用每行的VBA函数，所以这可能(非常)缓慢.可以使用各种技术来优化功能，例如打开静态记录集对象.进一步的细节不在此答案的范围内.
   • 由于此时您已经在编写代码，并且可以根据需要构造VBA和查询，所以最高效的查询将绕过多值字段本身，并使用标准SQL连接来获得所需的内容.例如，如果要获取所有相关的用户名，则打开并枚举以下记录集以建立名称列表:

1. If the values are needed within a standard SQL statement, I suggest passing the primary key value(s) to a VBA function. Then have the VBA function look up the record and retrieve the multivalued-field values using DAO recordsets.
   • Because this will call the VBA function for every row, this can be (very) slow. It is possible to optimize the function using various techniques, like opening a static recordset object. Further details are beyond the scope of this answer.
   • Since you're already in code at this point and can structure VBA and queries however you want, the most efficient query will circumvent the multivalued-field itself and use standard SQL joins to get what you need. For instance, if you want to get all of the related user names, then open and enumerate the following recordset to build your list of names:

---

 sSQL = "SELECT table2.key, table2.multifield.value, UserTable.Username " & _
     " FROM UserTable INNER JOIN table2 ON UserTable.ID = table2.multifield.Value" & _
     " WHERE (table2.key = [KeyParameter])"
 Set qry = CurrentDb.CreateQueryDef(, sSQL)
 qry.Parameters("KeyParameter") = keyPassedToFunction
 Set rs = qry.OpenRecordset

---

1. 如果可以/将在代码上下文中将SQL查询作为DAO记录集打开，并且，您仍然需要检索多值字段作为单个字段，是一种枚举VBA中的多值字段的方法.
   • 如果代码最终反复打开和关闭多个记录集，尤其是在多个嵌套循环中，则很可能可以通过使用适当的联接重组SQL并更改数据处理顺序来提高效率.
   • Rant:您可能会注意到，尽管Access SQL引擎拒绝将此类对象传递给VBA函数，但SQL语句的基础记录集对象确实确实返回了可以在代码中枚举的对象，这在一定程度上是不一致的. SQL引擎已经可以将数据装箱和拆箱到VBA变量类型中，因此，在实现多值字段时，他们本可以让SQL引擎简单地将多值记录集对象装箱并将其传递给VBA函数进行处理，这似乎是合理的.类似于以下代码...因此，在该问题中的原始尝试并非没有道理.
   • 以下代码片段说明了多值字段作为包含DAO.Recordset2对象的DAO.Field对象返回:

1. If the SQL query can/will be opened as a DAO recordset in a code context and you still need to retrieve the multivalued-field as a single field, there is a way to enumerate the multivalued-field in VBA.
   • If the code ends up repeatedly opening and closing multiple recordsets, especially in multiple nested loops, it is likely that you could improve efficiency by restructuring the SQL using appropriate joins and changing the data processing order.
   • Rant: As you might notice, it is somewhat inconsistent that the underlying recordset object of an SQL statement does indeed return an object which can be enumerated in code, although the Access SQL engine refuses to pass such an object to a VBA function. The SQL engine already deals with boxing and unboxing data into the VBA variant type, so it seems reasonable that when implementing the multivalue fields, they could have had the SQL engine simply box the multivalued recordset object and passed it to a VBA function to be handled similar to the following code... so the original attempt in the question was not unreasonable.
   • The following code snippet illustrates that the multivalue field is returned as a DAO.Field object containing a DAO.Recordset2 object:

---

Dim rs as Recordset2
Set rs = CurrentDB.OpenRecordset("SELECT table2.multifield ... FROM ...")

Dim sList As String
sList = ""
If TypeOf rs![multifield] Is Recordset2 Then
    Dim rsMVF As Recordset2
    Set rsMVF = rs![multifield]

    Dim bFirst As Boolean
    bFirst = True

    rsMVF.MoveFirst
    Do Until rsMVF.EOF
        If bFirst Then
            sList = rsMVF.Fields(0)
            bFirst = False
        Else
            sList = sList & "," & rs.Fields(0)
        End If
        rsMVF.MoveNext
    Loop
    '* DO NOT CLOSE the Recordset based on the Multivalue field.
    '*   Access will close it automatically.
End If
'* sList will contain comma-separated list of values

这篇关于如何将查询中的多值字段传递给VBA函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！