CODEWITHSUNDEEP
dictionaryms-accessvba
Andrew Seabrook
Andrew Seabrook

Reputation: 407

VBA Dictionary not behaving as expected

Using a Dictionary in Access VBA, but retrieving values is prooving a problem. Can anybody tell me how to get around this, or what I am doing wrong?

So I am looping through a bit of code and set up a dictionary

           For i = 0 To .Fields.Count - 1
                If Not dict.Exists(.Fields(i)) Then

                    If i = 0 Then
                        dictValue = "ID"
                    Else
                        dictValue = "F" & CStr(i)
                    End If

                    dict.Add .Fields(i), dictValue
                Else
                    'Duplicate column name error
                     Err.Raise 514
                 End If

            Next

The .Fields values from the dataset represent a single row of data extracted from a table and the dictionary sets up as expected.

Later accessing the dictionary to retrieve values is causing a problem: serviceRequestId = dict("Service Request ID") is adding a new key to the dictionary as if the key does not exist, but it does, so I am getting a duplicate key added! This applies whichever key value I try to retrive from the dictionary. A brake point prior to the dereferencing shows that the correctly setup dictionary has not changed, and that the key added during the loop still indeed exists.

Any thoughts on this much appreciated!

Upvotes: 0

Views: 206

Answers (1)

user6432984
user6432984

Reputation:

Dictionaries can store any kind of object as a key or value

This line is storing the Field object itself as the key not it's value.

If Not dict.Exists(.Fields(i)) Then

In the diagram blow I stored the Field object in the dict and inspected it in the locals window.

enter image description here

Here is what you actually want to do

    If Not dict.Exists(.Fields(i).Value) Then
        dict.Add .Fields(i).Value, dictValue
    Else
        'Duplicate column name error
        Err.Raise 514
    End If

Upvotes: 4

Related Questions