VBA modify recursive code

[monkeybiz12345]

I have a piece of code below that works. HOWEVER, now that I've seen the results I realize that it doesn't work quite the way I want it to and have spent the last 12 hours trying to modify it without success.

Just so you have some context, I'm trying to get all of the purchased parts and qtys required for multi-level bills of materials.

I have a table, tblBOMStructureTest that contains the following data:

tblBomStructureTest
ParentPart Component PartCategory QtyPer
Part 1 Part 4 B 2
Part 2 Part 3 B 1
Part 1 Part 2 M 2

My code separates the bought out and made in items into 2 different tables. Then, if the current record is a made in item, recursively calls the same function to get the bought out item at the lower level.

When the code is finished, I have this:

tblComponentsBoughtOut
ParentPart Component PartCategory QtyPer
Part 1 Part 4 B 2
Part 2 Part 3 B 1


tblComponentsMadeIn
ParentPart Component PartCategory QtyPer
Part1 Part 2 M 2

Turns out, what I really need as my result is this:

tblComponentsBoughtOut
ParentPart Component PartCategory QtyPer
Part 1 Part 4 B 2
Part 2 Part 3 B 2


The QtyPer for an item on a lower level of the BOM needs to be the QtyPer for the current component multiplied by the Qty Per of its ParentPart (the made in part).

Public Sub GetComponents(strSoldItem As String)
    Dim db As DAO.Database
    Dim rstBOM As DAO.Recordset          ' BOM Structure file
 
    ' Query the BOM Structure file for all components needed for strSoldItem
    Set db = CurrentDb
    Set rstBOM = db.OpenRecordset("SELECT ParentPart, Component, PartCategory, QtyPer FROM [tblBomStructureTest] " & _
                " WHERE [tblBomStructureTest].ParentPart= '" & strSoldItem & "'")
 
 
    ' Do until we reach the last record in the BomStructure query results
    Do Until rstBOM.EOF
        ' tell me which component we're on
        MsgBox ("Current component is " & rstBOM!Component & " " & rstBOM!PartCategory & " " & rstBOM!QtyPer)
 
        DoCmd.RunSQL "Insert Into " & IIf(rstBOM!PartCategory = "B", "tblComponentsBoughtOut", "tblComponentsMadeIn") & _
            " (ParentPart, Component, PartCategory, QtyPer) Values ('" & _
            rstBOM!ParentPart & "','" & _
            rstBOM!Component & "','" & _
            rstBOM!PartCategory & "'," & _
            rstBOM!QtyPer & ")"
' If the component is itself a ParentPart, call routine recursively        
If DCount("*", "tblBomStructureTest", "[ParentPart]='" & rstBOM!Component & "'") > 0 Then
 
 
            MsgBox ("Calling GetComponents recursively on " & rstBOM!Component)
            Call GetComponents(rstBOM!Component)  'call routine recursively
        End If
        rstBOM.MoveNext
    Loop
    rstBOM.Close
    Set rstBOM = Nothing
 
End Sub

My most recent attempt to modify this involves trying to store the QtyPer in variables at various points in the program, calculate what the QtyPer of the lower level item should be, and update the value of that record in tblComponentsBoughtOut. And, it appears that no matter where I set the variables in my code and calculate the new QtyPer for the lower level item, the calculation is incorrect. Variable values are being lost, zeroized along the way.

Here is the code that does not work. QtyBought is being set to 0 somewhere so CalcQty is also zero. There's no error message. I just can't figure out why I'm getting zero.

Public Sub GetComponents(strSoldItem As String)
    Dim db As DAO.Database
    Dim rstBOM As DAO.Recordset          ' BOM Structure file
    Dim flgMadeIn As Boolean
    Dim CompQty As Long
    Dim CalcQty As Long
    Dim QSub As Long        ' made in subassy qty
    Dim QBought As Long     ' component of subassy qty
 
 
 
    ' Query the BOM Structure file for all components needed for strSoldItem
    Set db = CurrentDb
    Set rstBOM = db.OpenRecordset("SELECT ParentPart, Component, PartCategory, QtyPer FROM [tblBomStructureTest] " & _
                " WHERE [tblBomStructureTest].ParentPart= '" & strSoldItem & "'")
 
    flgMadeIn = False
 
    ' Do until we reach the last record in the BomStructure query results
    Do Until rstBOM.EOF
        ' tell me which component we're on
        MsgBox ("Current component is " & rstBOM!Component & " " & rstBOM!PartCategory & " " & rstBOM!QtyPer)
       QtyBought = rstBOM!QtyPer
        MsgBox ("QtyBought is " & QtyBought)
        DoCmd.RunSQL "Insert Into " & IIf(rstBOM!PartCategory = "B", "tblComponentsBoughtOut", "tblComponentsMadeIn") & _
            " (ParentPart, Component, PartCategory, QtyPer) Values ('" & _
            rstBOM!ParentPart & "','" & _
            rstBOM!Component & "','" & _
            rstBOM!PartCategory & "'," & _
            rstBOM!QtyPer & ")"
 
           CompQty = rstBOM!QtyPer
 
 
 
 
        If DCount("*", "tblBomStructureTest", "[ParentPart]='" & rstBOM!Component & "'") > 0 Then
 
            flgMadeIn = True
            QMade = rstBOM!QtyPer
            MsgBox ("QMade is " & QMade)
            CalcQty = QMade * QBought
            MsgBox ("CalcQty is " & CalcQty)
            MsgBox ("Calling GetComponents recursively on " & rstBOM!Component)
            Call GetComponents(rstBOM!Component)  'call routine recursively
 
            If flgMadeIn = True Then
 
            MsgBox ("QBought is " & QBought)
 
 
            MsgBox ("Updating qty required for" & rstBOM!Component.Value)
                DoCmd.RunSQL "UPDATE tblComponentsBoughtOut SET [QtyPer] = " & CalcQty & " WHERE [tblComponentsBoughtOut].ParentPart= '" & rstBOM!Component & "'"
            End If
            ' reset the flag
            flgMadeIn = False
        End If
        rstBOM.MoveNext
    Loop
    rstBOM.Close
    Set rstBOM = Nothing
 
End Sub

Finally, I don't even know if I'm on the right track here.

Can anyone suggest ...

- a way to modify my original code so it writes the calculated quantity for lower level components to tblComponentsBoughtOut in the first place? (my preference if it's possible)

- if I'm stuck with trying to calculate the qty and updating the table later, is there another way to do it? I tried using DLookup on tblComponentsMadeIn to get the quantity to multiply by and didn't manage to get that working either

I'm relatively new to VBA and this is my first attempt at recursive code and I know I don't fully understand it... Not able to wrap my head around it yet or, more likely, I've been looking at it too long.

Many, many thanks!

PS. Apologies for the formatting. Can't seem to use TAB to line things up properly.

 

[DJkarl]

Without looking to closely at your code, the first thing I see is the reason your variables are resetting to zero values. This is by design, if you want to retain the value of those variables you have a couple of options, move them out of the current subroutine to the top of the current module, or declare them as static variables in your current subroutine.

 

[monkeybiz12345]

Thanks for the tip, DJkarl! This tells me where I should go from here.