在VBA中基于两个数组字段创建一个数组 [英] Creating an array in VBA based on two array fields

问题描述

我有几个表，其中的查找字段指向第三个表中的相同数据，并且两个字段都可以选择多个值.用户可以从每个表中选择一条记录，而我需要能够在VBA中分离出重叠的值.

I have a couple tables with lookup fields that point to the same data in a third table, and both fields can select multiple values. A user can select a record from each table, and I need to be able to separate out the overlapping values in VBA.

为此，我尝试在VBA中创建第三个数组，并将结果值转储到该数组中，但是出现Type Mismatch错误.我似乎找不到发生这种情况的原因.以下是我尽可能减少的代码:

To do this, I've tried creating a third array in VBA into which I'll dump the resulting values, but I'm getting a Type Mismatch error. I can't seem to find a reason that this would occur. The code, reduced as much as I can without losing the sense of it is below:

Function SetEnabledColours()
'Indexes for arrays of available colours
Dim IndA As Long, IndG As Long, IndO As Long
'Arrays of available colour options
Dim AuthorCol, GenreCol, OverlapCol()

AuthorCol = DLookup("[AllowedColours]", "tblAuthor", "[Author]= '" & cmbAuthor & "'")
GenreCol = DLookup("[AllowedColours]", "tblGenre", "[Genre]= '" & cmbGenre & "'")

'Separate overlapped options
'Cycle through AuthorCol
For IndA = LBound(AuthorCol) To UBound(AuthorCol)

[然后我得到了错误]

[And then I get the Error]

    'Check each GenreCol against this AuthorCol
    For IndG = LBound(GenreCol) To UBound(GenreCol)
        If GenreCol(IndG) = AuthorCol(IndA) Then
            'Add to OverlapCol(CountO)
            ReDim Preserve OverlapCol(IndO)
            OverlapCol(IndO) = GenreCol(IndG)
            IndO = IndO + 1
            'Skip over to next AuthorCol
            GoTo Escape1
        End If
    Next IndG
Escape1:
Next IndA

最初，我已经将索引调暗为整数，但是后来我意识到这是因为我将数组视为范围.我知道这种类型的数据存储为数组而不是范围.

Originally I had Dimmed the Indexes as integer, but I have since realised that this was because I was thinking of the arrays as ranges. I understand data of this type is stored as an array rather than a range.

此处Erik的答案将我引向了数组:将多选查询字段数据VBA

Erik's answer here points me towards arrays: Multi-select Lookup Field data to VBA

这就是我基于以下内容创建重叠数组的原因: https://www.experts-exchange.com/questions/28530517/remove-array-element-in-access-vba.html

And this is what I'm basing my overlap array creation on: https://www.experts-exchange.com/questions/28530517/remove-array-element-in-access-vba.html

推荐答案

DLookup for multiple values字段返回一个字符串，该字符串的值列表用逗号(和空格)分隔，因此您应使用Split函数对此进行转换字符串到数组:

DLookup for multiple values field returns a string with list of values separated by comma (and a space), so you should use the Split function for converting this string to an array:

AuthorCol = Split(DLookup("[AllowedColours]", "tblAuthor", "[Author]= 'd'"), ", ")
GenreCol = Split(DLookup("[AllowedColours]", "tblGenre", "[Genre]= '" & cmbGenre & "'"), ", ")

这篇关于在VBA中基于两个数组字段创建一个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！