title: VBA: Use Class module / Collection and/or dynamic array?
body: <p>My problem:</p>
  
  <p>I have multiple product structures that I need to be able to read through. I do not know ahead of time how many levels deep the product structure goes.  For Example I could have the following:</p>
  
  <p>Product A uses the following components </p>
  
  <ul>
  <li>A1</li>
  <li>A2</li>
  <li>A3</li>
  <li>A4</li>
  </ul>
  
  <p>but component A3 may be a sub assembly which has its own product structure that would need to be pulled.  So I would then end up with the complete product structure for Product A looking like the following:</p>
  
  <p>A uses:</p>
  
  <ul>
  <li>A1</li>
  <li>A2</li>
  <li>A3 (uses the following components):
  
  <ul>
  <li>A3A</li>
  <li>A3B (uses the following components):
  *A3B1
  *A3B2
  *A3B3</li>
  <li>A3C</li>
  <li>A3D</li>
  </ul></li>
  <li>A4</li>
  </ul>
  
  <p>and so on. </p>
  
  <p>My current code uses an array to contain the information retrieved via a DB query as shown here</p>
  
  <pre><code>Dim NumRecords As Integer
  Dim X As Integer
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim sSQL As String
  Dim PPS() As String 'Product structure returned from database query for the parent item
  Dim ParentName as String ' Parent Product
  Dim Plt as String ' Plant of Manufacture
  Dim DBPath as string 'File path for the database
  
  
  Set db = OpenDatabase(DBPath)
  sSQL = "SELECT Component, NumberUsed FROM ProdStructMstr WHERE (((Parent)='" &amp; ParentName &amp; "') AND ((Plant)='" &amp; Plt &amp; "')) ORDER BY Component;"
  Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
  rs.MoveLast
  rs.MoveFirst
  If Not rs.EOF Then NumRecords = rs.RecordCount
  If NumRecords &gt; 0 Then
      ReDim PPS(NumRecords - 1, 1)
      rs.MoveFirst
      For X = 0 To NumRecords - 1
          PPS(X, 0) = rs!Component
          PPS(X, 1) = rs!NumberUsed
          rs.MoveNext
      Next X
  Else
      MsgBox "ERROR: DB Table Empty or Not Found!", vbExclamation, "DATA ERROR"
  End If
  Set rs = Nothing
  Set db = Nothing
  </code></pre>
  
  <p>The problem I have with this is that it isn't capable of going further than 1 layer deep on the product structure, which means it won't pull the information for sub-assemblies.  I think I want to use a class module and a collection to over come this but I can't quite wrap my head around it.  </p>
  
  <p>The information for the product structure of the sub assembly A3 is listed in the ProdStructMstr table with A3 listed as the Parent and the components listed.</p>
  
  <p>An example of how the DB table looks for this would be:</p>
  
  <pre>
  Plant    |    Parent    |    Component    |    NumberUsed
  Z        |    A         |    A1           |    1
  Z        |    A         |    A2           |    3
  Z        |    A         |    A3           |    1
  Z        |    A         |    A4           |    2
  Z        |    A3        |    A3A          |    1
  Z        |    A3        |    A3B          |    1
  Z        |    A3        |    A3C          |    2
  Z        |    A3        |    A3D          |    1
  Z        |    A3B       |    A3B1         |    1
  Z        |    A3B       |    A3B2         |    4
  Z        |    A3B       |    A3B3         |    1
  </pre>