Strange (?) dictionary behaviour

[Zakraket]

I'm using a dictionary to build a list of keys and values.

The keys comes from a recordset (stringfield), the items are Excel-columns (A, B, C etc). The code determines the Excelcolumn based on a "columnname" in the table Meta by searching for a column with the corresponding name, then gets the Excel columnnumber, and adds both to a Dictionary
The dictionary holds the columnnames and corresponding columnumber

In my recordset rstMeta there are 4 columnnames and numbers that need to be added (NUMBER, BRIDGETYPE_ID, DESCRIPTION, WEIGHT) to the dictionary

With dictA
    dictA.compare = vbTextCompare
    do while not rstMeta.EOF
        "some code that searches for the matching column in xlSht...."
        .add rstMeta!MetaValue, Split(xlSht.Cells(x, y).ADDRESS, "$")(1)
        rstMeta.movenext
    loop
End With

When I run this it generates an error when adding the second columnname (BRIDGETYPE_ID) stating that the key already exists (which is not true since I have 4 unique values looping through rstMeta).

I found that when I do this:

        .add [COLOR="Red"]([/COLOR]rstMeta!MetaValue[COLOR="Red"])[/COLOR], Split(xlSht.Cells(x, y).ADDRESS, "$")(1)

it works....the same second value/key (BRIDGETYPE_ID) can be added without problem and my dictionary is filled as I intended with 4 "records"

I have absolutely no idea why adding "( )" would fix my problem... Anybody that can explain this?

 

[static]

dunno. Does it work if you don't use add?

do while not rstMeta.EOF
    "some code that searches for the matching column in xlSht...."
    .item(rstMeta!MetaValue) = Split(xlSht.Cells(x, y).ADDRESS, "$")(1)
    rstMeta.movenext
loop

edit

Adding parentheses around an object returns the default property value.
Since you haven't specified the property it's returning the field. Adding the brackets is equivalent to rstMeta!MetaValue.value

 

[MarkK]

Keep in mind that technically this...

rstMeta!MetaValue

...is a DAO.Field object. You may need to explicitly use the .Value property to get correct results, particularly where you pass a value to another method. That method's signature may receive the whole field, not the value it contains, as a parameter.
See if using...

rstMeta!MetaValue.Value

...solves the problem.
hth
Mark

 

[Zakraket]

Well...sounds reasonable. I was not aware of the functionality of ( )

The second pass the code tries to add the same fieldobject and not the value to the dictionary which triggers the error.

But what determines when the code interprets rstMeta!Metavalue as a value or as a field?

I have not run into a situation like this before using rst!field (I think the "problem" is that a dictionary can - as far as my knowledge goes - hold any type /object as a key value so when it gets a fieldobject it uses it because it can, usually when I use rst!field the result is put into a variable of some specific type, f.e. string. Code probably automatically then uses the value instead of fieldobject

tnx!

 

[ashleedawg]

Am I mistaken or wouldn't the problem be the placement of the with/end? Because this:

With dictA
    dictA.compare = vbTextCompare
    do while not rstMeta.EOF
        "some code that searches for the matching column in xlSht...."
        .add rstMeta!MetaValue, Split(xlSht.Cells(x, y).ADDRESS, "$")(1)
        rstMeta.movenext
    loop
End With

means the same as:

[COLOR=Blue]With dictA[/COLOR]
    dictA.compare = vbTextCompare
    do while not rstMeta.EOF
        "some code that searches for the matching column in xlSht...."
        [COLOR=Red][B]dictA[/B][/COLOR].add rstMeta!MetaValue, Split(xlSht.Cells(x, y).ADDRESS, "$")(1)
        rstMeta.movenext
    loop
[COLOR=Blue]End With[/COLOR]

very different than

    .add [COLOR=Red]([/COLOR]rstMeta!MetaValue[COLOR=Red])[/COLOR], Split(xlSht.Cells(x, y).ADDRESS, "$")(1)

 

[The_Doc_Man]

Looking at this, the first thing that jumps out at me is:

In my recordset rstMeta there are 4 columnnames and numbers that need to be added (NUMBER, BRIDGETYPE_ID, DESCRIPTION, WEIGHT) to the dictionary

If I understand correctly, you want to define the returned data as multiple fields i.e. the Item will actually be an Array object. If that is what you are trying to do, I don't think it will work quite that way. If you wanted to add an array as a single .Item value, I'll bet dollars to donuts you have to add the .Item as an extant array, not a piece at a time.

The syntax of the .Add that you showed us will assign the .Item as containing ONE element of the SPLIT (the 2nd column, since split is zero-based). So I'm going to take a wild-eyed guess here that you had already added the dictionary objects for the same key and column 1? If so, the duplication occurred when you went back to add the 2nd column to the extant key entry.

If you are using the same key to add multiple columns then you are trying to REPLACE the contents of the .Items field with a different value. I think that is the source of your duplication message. You can change a .Key value dynamically but to replace any .Item you need to .Remove the old one first.

I think the "problem" is that a dictionary can - as far as my knowledge goes - hold any type /object as a key value

From this link: https://msdn.microsoft.com/en-us/library/x4k5wbx4(v=vs.84).aspx

The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.

The idea that you have to use .Value or the () to reduce the !MetaValue to a single value tells me that somehow the dictionary key things that the raw !MetaValue looks like an array of some kind. I know, it isn't supposed to be that way, but apparently the dictionary object doesn't like it unless you qualify it. I would say to take the Zen approach and just say "that's what it wants; that's what I will give it."