ACC2000: How to Create a Collection of Collections (210035)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q210035
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY

A limitation of the Microsoft Visual Basic for Applications Collection object is that it cannot contain a user-defined data type. You can achieve similar functionality, however, by creating a collection that contains the elements that you would normally define in your user-defined data type, and then storing that collection in a second collection. This article shows you how to create such a nested collection.

MORE INFORMATION

To create a collection within a collection, follow these steps:
  1. Create a module, and then type or paste the following procedure: 
    Function CollectionCollection()
   Dim element
   Dim insideCollection As New Collection
   Dim outsideCollection As New Collection

   ' Populate the first element of the collection and add
   ' it to the parent collection.
   insideCollection.Add KEY:="fname", Item:="joe"
   insideCollection.Add KEY:="lname", Item:="smith"
   insideCollection.Add KEY:="age", Item:=12
   outsideCollection.Add insideCollection

   ' Clear the collection - prevents duplication of elements.
   Set insideCollection = Nothing

   ' Populate the second element of the collection and add
   ' it to the parent collection.
   insideCollection.Add KEY:="fname", Item:="fred"
   insideCollection.Add KEY:="lname", Item:="smith"
   insideCollection.Add KEY:="age", Item:=14
   outsideCollection.Add insideCollection

   ' Print the contents of the parent collection to the Debug window.
   For Each element In outsideCollection
      Debug.Print element("fname"), element("lname"), element("age")
   Next
End Function
  2. To test the function, on the View menu, click Immediate Window, type the following command, and then press ENTER:

    CollectionCollection

    Note that the contents of the collection appear in the Immediate window.

REFERENCES

For more information about collections, click Microsoft Visual Basic Help on the Help menu, type collection object in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbhowto kbinfo KB210035
©2004 Microsoft Corporation. All rights reserved.