VBA dictionary as a property of a class-CodePudding

I have this simplified class named clsWarehouseSum.

Option Compare Database
Option Explicit

Private wh_units As Scripting.Dictionary    

Public Function availableUnits(warehouse As String) As Long
    'Debug.Print wh_units(warehouse)
    If wh_units Is Nothing Then Set wh_units = New Scripting.Dictionary
    
    If Not wh_units.Exists(warehouse) Then
    
        Dim SQL As String
        Dim RS As DAO.Recordset
        SQL = "SELECT sum(units) as tot_units " _
            & "FROM warehouse " _
            & "WHERE warehouse = '" & warehouse & "' "   
    
        Set RS = CurrentDb.OpenRecordset(SQL)
       
        wh_units.Add (warehouse), RS("tot_units")
        
    End If
    
    availableUnits = wh_units(warehouse)
    
    

End Function

I try to use it like this:

Sub test()

Dim wh As New clsWarehouseSum    
Debug.Print wh.availableUnits("Cohasset")
Debug.Print wh.availableUnits("Cohasset")

End Sub

While the first Debug.Print prints what's expected, the second one gives me an error: Run time error 3420, Object Invalid or no longer set. When I step through the code, it correctly evaluates both if statements as false. Yet, the last line of the function gives me the error mentioned above. What am I doing wrong? Why?

CodePudding user response：

Add Debug.Print TypeName(wh_units(warehouse)) before the availableUnits = wh_units(warehouse) line and if it prints anything else than Long to the Immediate window then you might want to cast to Long using CLng while you also have some error handler in place.

Or, you might want to make sure that the line wh_units.Add (warehouse), RS("tot_units") is adding a Long to your dictionary so you should check the type before you add.

As a general rule, when you return a specific data type from a dictionary or collection, you should always have checks in place either when you add the data to the dict/coll or when you return it so that you avoid type incompatibility and runtime errors.