CODEWITHSUNDEEP
vbadictionaryms-access
Jsqsh
Jsqsh

Reputation: 120

Nested dictionary object not found

I am attempting to take some table, a list of fields to group by, and a number field as input, to produce an output table that contains the group-by fields and medians for the number field.

I have this working for a single field to group by, but not multiple fields.

I am using nested dictionaries to eventually aggregate my grouped-field values and their medians.

When I try to access the inner dictionary I get a

'424' Run-time error: Object required

Once I put the inner dictionary in the outer dictionary I cannot access it like one would suspect.

Public Sub MedianByGroups(ByVal inputTable As String, ByVal strField As String, _
ByVal strGroup As String, ByVal outputTable As String, _
Optional ByVal strCriteria As String)

    DoCmd.SetWarnings False
    Dim db As DAO.Database: Set db = CurrentDb()
    Dim rstDomain As DAO.Recordset
    Dim strSQL As String
    Dim varMedian As Long: varMedian = 0
    Dim intRecords As Long
    Dim splitgroup As Variant

    splitgroup = Split(strGroup, ", ")

    strSQL = "SELECT DISTINCT " & strGroup & " INTO TMP_GROUPINGTABLE FROM " & _
    inputTable & ";"

    DoCmd.RunSQL (strSQL)

    Set rstDomain = db.OpenRecordset("TMP_GROUPINGTABLE")

    Dim i As Integer: i = 0

    Dim group_dict As New Dictionary
    Dim tmp_dict As Dictionary
    Set tmp_dict = New Dictionary

    If Not (rstDomain.EOF And rstDomain.BOF) Then
        rstDomain.MoveFirst
        Do Until rstDomain.EOF = True
            For Each Label In splitgroup
                tmp_dict.Add Label, rstDomain.Fields(Label).Value
            Next Label

            group_dict.Add i, tmp_dct
            Set tmp_dict = New Dictionary
            i = i + 1
            rstDomain.MoveNext
        Loop
    End If

    rstDomain.Close

    For Each key In group_dict.Keys
        For Each addlKey In group_dict(key).Keys
            Debug.Print addlKey, group_dict(key)(addlKey)
        Next addlKey
    Next key

    DoCmd.SetWarnings True
end sub

If one had a table, called "MYTABLE", that looked like this:

GROUP1    GROUP2    VAL1
A         C         400
B         D         500

One would call this sub like:

call MedianByGroups("MYTABLE", "VAL1', "GROUP1, GROUP2", "OUTPUTTABLE")

This doesn't do the Median or any other parts yet, as I am hitting this stumbling point with the dictionaries.

Where it fails is the inner for loop on that nested for loop near the end.

For Each addlKey In group_dict(key).Keys

Edit: As ComputerVersteher pointed out, the issue was a spelling mistake I had made. The solution to avoid this as Andre mentioned is to add the option explicit to the module.

Upvotes: 1

Views: 149

Answers (1)

PeterT
PeterT

Reputation: 8557

Not a complete answer -- I set up an example without the MS Access-specific parts and the logic in your code seems to run fine. As near as I can tell, there's no real difference in the logic below and your code above. Does this example run for you?

Option Explicit

Sub TestingNestedDictionaries()
    Dim group_dict As New Dictionary
    Dim tmp_dict As Dictionary
    Set tmp_dict = New Dictionary

    Dim labels As Variant
    labels = Array("red", "orange", "yellow", "green", "blue", "indigo", "violet")

    Dim i As Long
    For i = 1 To 5
        Dim label As Variant
        For Each label In labels
            tmp_dict.Add label, label & "-" & i
        Next label
        group_dict.Add i, tmp_dict
        Set tmp_dict = New Dictionary
    Next i

    '--- pull out one color
'    Const THIS_COLOR As String = "green"
'    Dim group As Variant
'    For Each group In group_dict
'        Dim subDict As Dictionary
'        Set subDict = group_dict(group)
'        Debug.Print subDict(THIS_COLOR)
'    Next group

    Dim key As Variant
    Dim addlKey As Variant
    For Each key In group_dict.Keys
        For Each addlKey In group_dict(key).Keys
            Debug.Print addlKey, group_dict(key)(addlKey)
        Next addlKey
    Next key

End Sub

Upvotes: 2

Related Questions