Solved Weird Dictionary Issue

[LGDGlen]

Hi All

As an example I have 2 master records with 6 and 2 child records respectively selected from a recordset and want to get the counts of master and child into a dictionary to use in calculations later:

    Set rs = Form.Recordset.Clone
    With rs
        .MoveFirst
        Do While Not .EOF
            delProdID = !del_prod_id
            If Me.txtSelected Like "*," & delProdID & ",*" Then
                consID = !cons_id
                If Dict_consDeliveryNumbers.Exists(consID) = False Then
                    Dict_consDeliveryNumbers.Add Key:=consID, Item:=1
                Else
                    Dict_consDeliveryNumbers(consID) = Dict_consDeliveryNumbers(consID) + 1
                End If
            End If
            .MoveNext
        Loop
        .MoveFirst
    End With
    
    Dim consSplitAmount As Double
    consSplitAmount = Me.txtTotalAmount / Dict_consDeliveryNumbers.Count

    For Each iKey In Dict_consDeliveryNumbers.Keys
        Debug.Print "!!!" & iKey & "!!!", Dict_consDeliveryNumbers(iKey)
        'Debug.Print "For Consignment: " & iKey & " the amount for each of the " & CStr(Dict_consDeliveryNumbers(iKey)) & " deliveries would be:  " & CStr(consSplitAmount / CInt(Dict_consDeliveryNumbers(iKey)))
    Next iKey

Weirdly i get:

!!!34283!!!
!!!34283!!!  6
!!!34285!!!  2

as the debug output, I have no idea where that first dictionary key is coming from, it shouldn't exist as its the same as the second 1.

Anyone got any ideas what i'm doing wrong, never seen this before and i've used dictionaries before and not had an issue so i'm sure i'm doing something stupidly wrong

Thanks in advance

Glen

 

[LGDGlen]

IGNORE ME, i'm an idiot, i had a bit of debug code further up that i hadn't removed, sorry, it was there for me to use to remember the format of the adding to a dictionary and forgot to delete it ffs

 

[LGDGlen]

@Pat Hartman thanks for the feedback, i'm not sure "no reason" would be accurate, the above code was using an example, there would be potentially 10s of combinations of the consignment parents to delivered products children i'd have to requery many times getting totals for the deliveries under each consignment, unless (and this is totally probably) i don't quite understand how total queries would work. the user can select a number of parents from a list which is stored in a hidden form variable, this would need to be the IN for a query to return ALL the consignments (parents) then i'd need to get the totals of the number of deliveries for each of the consignments so i could

- firstly divide the total invoice amount by the number of consignments
- and then each portion is portioned further by the number of deliveries under each consignment

if that is possible in 1 query without having to do any VBA work to update the query could you give an example as i'd honestly REALLY like to be able to do this in a better way and want to learn but i am not experienced enough in queries to even know where to start with that

 

[LGDGlen]

@Pat Hartman thanks for responding, it is much appreciated. i want to learn better ways to do things as i come from a development background in things like c++ etc so my go to is "code it" and i have no sql/query/database experience so this is all new to me. i usually know what i want to achieve and can see ways to do it, but usually the ways are not the most efficient.

i was pretty sure i would need some VBA to do this particular functionality due to the nature of capturing the data and manipulating it to enable me to perform this specific task.

i'll take a look at your examples and see if there are things that i can adapt and improve for my specific situation, if i have questions i'll raise em (so probably )

once again thanks for the detailed response it is much appreciated.