Recursive function call not happening


I'm trying to write a function that returns a list of lot numbers used in a manufactured assembly.  The idea is that I give  it the stock code and lot number of the manufactured item and the function gives me all of the stock codes and lots used to manufacture that item.

The lot information data is stored in a table that looks like this:
StockCode      Lot         ParentPart                  ParentLot
C1                       41                       SA2                          163
C1                       41                       SA1                    164
C2                       42                       SA2                    163
C2                       42                       SA1                          164
C5                       45                       PA                          165
SA1                     164                       PA                      165
SA2                     163                       PA                           165

In this example, PA is my manufactured item.  It is made up of SA1, SA2, and C5.  SA1 is made up of C1 and C2.  SA2 is also made up of C1 and C2.

When I run my function on this data, I see the message box telling me that the item has components but the function is not being called recursively for that item.

First I pass it item PA and lot 165.  It finds C5. No components for C5 so it moves on to the next item.  Next item is S1.  It finds 2 components for S1. I'd expect it to now call the function recursively for item S1, lot 164 but it doesn't.  It moves on to item S2 where it finds 2 components and again does not call the function recursively.  

What am I missing?

Many thanks!

Function LookupLot(ParentPart, ParentLot)
dim iArray
iArray = BuildStockInfo          ' builds an array. used to look up component count 

dim cn, strConnectionString, strSQLInstanceName, strDatabaseName, strSQL, rstLotsForParent
set cn = createObject("ADODB.Connection")
strConnectionString = "Provider=SQLOLEDB;Data Source=" & strSQLInstanceName & ";Persist Security Info=False;Initial Catalog=" & strDatabaseName & ";Integrated Security=SSPI"

cn.Open strConnectionString

 strSQL = "SELECT StockCode, Lot " & _
    "FROM LotTrack " & _
    "WHERE ParentPart='" & ParentPart & "' AND ParentLot='" & ParentLot & "'"
 Set rstLotsForParent = CreateObject("ADODB.Recordset")

   'Open the recordset object executing the SQL statement and return records
   rstLotsForParent.Open strSQL,cn

dim rStk, rLot
'loop through list of lots for this parent.  Show me the details
	Do While NOT rstLotsForParent.EOF
		rStk = rstLotsForParent.Fields("StockCode").Value
		rLot = Cint(rstLotsForParent.Fields("Lot").Value)
		msgbox rStk & "  " & rLot
	' output to temporary table
	' if component count <>0, this item has a structure. get the lots for the sub components here
	    If GetCompCount(iArray, rStk) <> 0 Then
	    	msgbox "this item has components"
		    [b]LookupLot rStk, rLot[/b]
	        msgbox "this item has no components"
    	End If

' Next item to process


' Close & release the recordset
   set rstLotsForParent = nothing

   set cn = nothing

End Function

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
I suspect you are actually calling the function recursively, but then returning from it sooner than you expect.

I would add a message box on entry displaying the parms, and then a few more message boxes as the function progresses.  Perhaps the SQL query isn't returning any rows, etc.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
monkeybiz12345Author Commented:
Thanks Bill!  That was it. I had a type conversion problem involving leading 0's that I missed.  

The line  rLot = Cint(rstLotsForParent.Fields("Lot").Value)  stripped the leading 0's from my lot number (which I'm sure I thought was a great idea when I originally wrote it that way) causing the SQL string to return no records.  When I ran my SQL query manually to make sure I had the statement right, I typed in the data with the leading 0's and didn't catch it.
Bill PrewIT / Software Engineering ConsultantCommented:
Great, glad you were able to track it down and it was easily resolved, thanks for the feedback.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.