Creating an array in VBA based on two array fields

Question

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.

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's answer here points me towards arrays: Multi-select Lookup Field data to VBA

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

Answer 1

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 & "'"), ", ")