Recursive function call not happening

Greetings,

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")
strSQLInstanceName="WIN-FVVKGITEQC8\SQLEXPRESS"
        
strDatabaseName=SystemVariables.CodeObject.CompanyDatabaseName
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]
    	Else
	        msgbox "this item has no components"
    	End If

' Next item to process
      rstLotsForParent.MoveNext

	Loop

' Close & release the recordset
   rstLotsForParent.Close
   set rstLotsForParent = nothing

   cn.close
   set cn = nothing

End Function

Select allOpen in new window