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:
My Bills of Materials Structure (rstBOM) file has the following fields:
ParentPart (to be matched to the StockCode in rstSO)
PartCategory (B or M)
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")
' 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
rstBComp!ParentPart = rstBOM!ParentPart
rstBComp!Component = rstBOM2!Component
rstBComp!PartCategory = rstBOM!PartCategory
rstBComp!QtyPer = rstBOM!QtyPer
' Write the record for the made in item to the new table
rstMComp!ParentPart = rstBOM!ParentPart
rstMComp!Component = rstBOM!Component
rstMComp!PartCategory = rstBOM!PartCategory
rstMComp!QtyPer = rstBOM!QtyPer
Set rstSO = Nothing
Set rstBOM = Nothing
Set rstBComp = Nothing
Set rstMComp = Nothing
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