Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Result of calculated variable incorrect in recursive call. Anyone know why?

Posted on 2012-08-28
2
Medium Priority
?
310 Views
Last Modified: 2012-08-29
Greetings,

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?

Many thanks!


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 
'strSoldItem
    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) 
  
        End If
        rstBOM.MoveNext
    Loop
    rstBOM.Close
    Set rstBOM = Nothing
    
End Sub

Open in new window

0
Comment
Question by:monkeybiz12345
2 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38344229
This was in the wrong spot...  
lngQtyReq = rstBOM!QtyPer * lngQtySold

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 strSoldItem
    Set db = CurrentDb
    Set rstBOM = db.OpenRecordset("SELECT ParentPart, Component, QtyPer FROM [tblBomStructure] WHERE [tblBomStructure].ParentPart= '" & strSoldItem & "'")
    
    
    ' Do until we reach the last record in the BomStructure query results
    Do Until rstBOM.EOF
        ' multiply to get total qty required
        lngQtyReq = rstBOM!QtyPer * lngQtySold

        CurrentDb.Execute "Insert Into tblOutPutTable " & _
            " (ParentID, ComponentID, Program, NumberRequired) Values ('" & _
            rstBOM!ParentPart & "','" & _
            rstBOM!Component & "','" & _
            varProg & "','" & _
            lngQtyReq & "')", dbFailOnError
           
        ' 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)
  
        End If
        rstBOM.MoveNext
    Loop
    rstBOM.Close
    Set rstBOM = Nothing
End Sub

Open in new window

0
 

Author Closing Comment

by:monkeybiz12345
ID: 38346252
Once again, thanks IrogSinta!  

Moving that one line does the trick!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question