Collections and Object Variables: Watch Your Type!

Last week I had a perplexing problem with some code in one of my databases. In my code I was navigating up and down a hierarchical table and creating a collection of all the children or all the parents of a given record. All worked well until I tried to retreive the items from the collection, when I got Run-time error 3420: Object invalid or no longer set.

I've reproduced the problem with the following simplified code, which reads through an item table adding the item IDs to a collection and then listing the items in the collection:

Sub Test()
  Dim clnTest As Collection
  Dim rsData As DAO.Recordset
  Dim lngCounter As Long
    
  Set clnTest = New Collection
    
  ' read through tblItems and add the ItemIDs to the collection
  Set rsData = CurrentDb.OpenRecordset("tblItems")
  Do Until rsData.EOF
    clnTest.Add rsData!ItemID
    rsData.MoveNext
  Loop
  rsData.Close
    
  ' now list the data in the collection
  For lngCounter = 1 To clnTest.Count
    Debug.Print clnTest(lngCounter)
  Next lngCounter
    
  ' clean-up
  Set clnTest = Nothing
  Set rsData = Nothing
End Sub

Go ahead- try it- you'll get "Object invalid or no longer set" on the Debug.Print line.

What went wrong? Can you spot the problem? It turns out it's with the line clnTest.Add rsData!ItemID. Collections are always collections of variants. So when you add the ItemID field to the collection, it adds an object reference to the field itself. When you try to access it later, after closing the recordset, the object doesn't point to anything anymore. Hence the error.

What's the solution? You could change the offending line to: clnTest.Add rsData!ItemID.Value. Or you could cast it to the appropriate type, with something like clnTest.Add CLng(rsData!ItemID). Or you could use an intermediate variable with the appropariate type and assign the field to that variable and then add it to the collection.

Which is "better"? I'm not sure. I think I'd want to adopt a practice I could use all the time, not something that kicks in only when I'm using collections. Since best practive with VBA is to use Option Explicit and declare all your variables, and to be specific about any type conversions by using the appropriate conversion function, I guess maybe any time you convert out of a variant or an object which behaves like a variant, such as a field or a collection, you should use a type conversion function. (That would go double when you're putting the result into another variant.) In this case that might look like:

clnTest.Add CLng(rsData!ItemID)
....
Debug.Print CLng(clnTest(lngCounter))