Article ID: 210035 - Last Review: July 15, 2004 - Revision: 1.3

ACC2000: How to Create a Collection of Collections

View products that this article applies to.
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).

Expand all | Collapse all

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.
Back to the top

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.
Back to the top

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.
Back to the top
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Back to the top
Keywords: 
kbhowto kbinfo KB210035
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations