Avatar of monkeybiz12345
monkeybiz12345
 asked on

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

Open 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!
Microsoft Access

Avatar of undefined
Last Comment
Eric Sherman

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
IrogSinta

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Eric Sherman

To be honest and fair, with VBA you can pretty much accomplish any concept like the one you presented.  The problem is you are not asking a general question about Access/VBA functionality ... your question is more related to designing the entire project/app.  Most experts here including myself would charge a client an hourly fee to review, digest, evaluate then design functions to accomplish your objectives.  It's not something that you can answer in just one post or comment.  A process like this one ... I generally flowchart it to get a clear understanding of the entire process ... then build code based on that.  That whole process would take a lot of time IMO.  

Good luck with your project.

ET
monkeybiz12345

ASKER
Thanks IrogSinta!  This is exactly what I was looking for.  The vocabularly I didn't have was the word "recursive".
monkeybiz12345

ASKER
@etsherman, I'm guessing that you're feeling concerned because you need to know that random people aren't going to try to take advantage of the gracious Experts who freely post here by expecting a level of response that would normally be considered a consulting gig.

I agree that would be inappropriate.  However, I don't think my question was inappropriate. Perhaps I didn't explain it as well as it needed to be explained but I was clear that all I was looking for was assistance with the structure of part 3.  

As a self-taught programmer who learns and works alone, I have enormous gratitude for everyone who contributes here and elsewhere on the internet where knowledge and code snippets are shared.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Eric Sherman

Not a problem ... I just read your original post real quickly and got that impression based on these comments:

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

Glad you got it resolved ... I was going to come back to this question later on.

ET