Let me start by saying that I'm playing at the very edge of my current knowledge here by trying to modify this code....
I'm puzzled. When the code is called recursively, it isn't picking up the QtyPer correctly and not calculating lngQtyReq correctly. Instead of getting the new QtyPer of the component (which is passed to the function during the recursive call), it retains the QtyPer of the previous iteration. The MsgBox's, which I added to help me see what's going on, aren't displayed during the recursive calls either.
My BOMStructure test data:
ParentPart Component QtyPer
XLQ25730050-002 XLQ2521209C0815 10
XLQ25730050-002 XLQ25990209 2
XLQ25730050-002 XLQ25404161-311 2
XLQ25404161-311 XLQ25404161-900 1
XLQ25990209 XLQ25980020 1
My result set:
ParentID ComponentID Program NumReq
XLQ25730050-002 XLQ2521209C0815 CD 20
XLQ25730050-002 XLQ25404161-311 CD 20
XLQ25404161-311 XLQ25404161-900 CD 2
XLQ25990209 XLQ25980020 CD 2
XLQ25730050-002 XLQ25990209 CD 20
Multiplier is 2.
The 3 numbers in bold are correct. I would've expected the other two numbers to both be 4.
Can anyone tell me what's up with this and suggest a way to fix it?
Public Sub GetComponents(strSoldItem As String, varProg As Variant,
lngQtySold As Long)
Dim db As DAO.Database
Dim rstBOM As DAO.Recordset ' BOM Structure file
Dim lngQtyReq As Long ' to calculate qty required
' Query the BOM Structure file for all components needed for
Set db = CurrentDb
Set rstBOM = db.OpenRecordset("SELECT ParentPart, Component, QtyPer
FROM [tblBomStructure] WHERE [tblBomStructure].ParentPart= '" & strSoldItem & "'")
MsgBox ("BOM Qty Per is " & rstBOM!QtyPer)
MsgBox ("Multiplier is " & lngQtySold)
' multiply to get total qty required
lngQtyReq = rstBOM!QtyPer * lngQtySold
MsgBox ("Multiplied qty required is " & lngQtyReq)
' Do until we reach the last record in the BomStructure query results
Do Until rstBOM.EOF
DoCmd.RunSQL "Insert Into tblOutPutTable " & _
" (ParentID, ComponentID, Program, NumberRequired) Values ('" & _
rstBOM!ParentPart & "','" & _
rstBOM!Component & "','" & _
varProg & "','" & _
lngQtyReq & "')"
' Check to see if component is also a parent. If so, call routine recursively
If DCount("*", "tblBomStructure", "[ParentPart]='" &
rstBOM!Component & "'") > 0 Then
Call GetComponents(rstBOM!Component, varProg, lngQtySold)
Set rstBOM = Nothing