Nested loop design help please

Greetings,

I'm working on a very ambitious (for me!) project and, after going down a path design wise, am at a point where I cannot see how to implement the rest of my idea.

The objective: The purpose of this application is to tell us when we will run out of stock for the component parts that are required to build the products we sell to our customers.  This tool will be used by our Purchasing department to make sure they have ordered everything we need for manufacturing.

My code needs to do the following:

1) query our sales orders to find out the product sold, quantity sold, and promised ship date

2) sum the total of the quantity sold by product and date

3a) query the bills of materials structure file for the product sold to get the component parts required and qty per. If the component part is purchased, write this part number, the ship date required from the sales order query, and the (qty per * the qty sold from the sales order query) to another table in my database.  

3b) If the component part is a subassembly (made in house), query the bills of material structure file for that part number, again looking for purchased parts.  Repeat until all components for the product sold have been processed.

4) Move on to the next product sold.  Repeat step 3 until I've found all the purchased parts required to make all products we've sold.

I need help with 3a / 3b / 4.

My Sales Order query (rstSO) has the following fields:
StockCode
ShipDate
QtySold

My Bills of Materials Structure (rstBOM) file has the following fields:
ParentPart  (to be matched to the StockCode in rstSO)
Component
PartCategory  (B or M)
QtyPer

Data in this file looks like this:
P1      C1      B      2
P1      C2      M      1
P1      C3      M      10
C2      C15      B      2
C3      C22      M      2
C3      C200      B      15
C22      C6      B      2
P2      C1      B      24
P5      C12      B      5

ParentPart P1 has one or more components which may be either bought out or made in.  Components may themselves be parent parts. I'll never know ahead of time how many levels of subassemblies each parent has.  In other words, there's no field that says how many levels are on a bill of materials.  And, of course, the data file is large - currently around 100K records and certain to get bigger over time.

Here's what I have right now that doesn't do what I want and I can't wrap my head around how to get from here to there.

Private Sub GetBomStructure()

Dim db As DAO.Database
Dim rstSO As DAO.Recordset           ' Query from Sales Orders
Dim rstBOM As DAO.Recordset          ' BOM Structure file
Dim rstBComp As DAO.Recordset        ' Bought Out components
Dim rstMComp As DAO.Recordset        ' Made In Components
Dim strSoldItem As String

Set db = CurrentDb
Set rstSO = db.OpenRecordset("qryPartsSoldSumQtyByShipDate-5")

rstSO.MoveFirst

' Do until we reach the last record in the Sales Order query results
Do Until rstSO.EOF
   
strSoldItem = rstSO!stockcode
' Tell me which stock code we're on
MsgBox ("Stock code sold is " & strSoldItem)
    
' Query the BOM Structure file for all components needed for strSoldItem
Set rstBOM = db.OpenRecordset("SELECT ParentPart, Component, PartCategory, QtyPer FROM [tblBomStructureTest] WHERE [tblBomStructureTest].ParentPart= '" & strSoldItem & "'")

' Open the 2 tables to store the component info
Set rstBComp = db.OpenRecordset("tblComponentsBoughtOut")
Set rstMComp = db.OpenRecordset("tblComponentsMadeIn")
  
  ' Do until we reach the last record in the BomStructure query results
  Do Until rstBOM.EOF
    
   If rstBOM!PartCategory = "B" Then
   
   ' Write the record for the bought out item to the new table
        With rstBComp
          .AddNew
          rstBComp!ParentPart = rstBOM!ParentPart
          rstBComp!Component = rstBOM2!Component
          rstBComp!PartCategory = rstBOM!PartCategory
          rstBComp!QtyPer = rstBOM!QtyPer
          .Update
        End With
   Else
        
        ' Write the record for the made in item to the new table
        With rstMComp
          .AddNew
          rstMComp!ParentPart = rstBOM!ParentPart
          rstMComp!Component = rstBOM!Component
          rstMComp!PartCategory = rstBOM!PartCategory
          rstMComp!QtyPer = rstBOM!QtyPer
          .Update
        End With
        
    End If
    
      rstBOM.MoveNext
   Loop
   
 rstSO.MoveNext

Loop

rstSO.Close
rstBOM.Close
rstBComp.Close
rstMComp.Close

Set rstSO = Nothing
Set rstBOM = Nothing
Set rstBComp = Nothing
Set rstMComp = Nothing

End Sub

Select allOpen in new window

I started by creating a query that contains only records that match the stock code sold.  Now that I've done that, I realize that doesn't help me requery the structure file for a part that's made in.  My logic is all wrong.  It seems like this kind of thing should be easy but I'm just not seeing it... There's gotta be a better way.

What I'd like:
- what is the proper way to structure this? what's the better way?
- code snippets that implement the suggestion also most welcome

Thanks!