Looping through Product Recipe Until found original Component

[Mark]

I have a recipe table (tblRecipeComponents) with 3 fields (ProductID,
ComponentID, Quantity) which holds all recipe components. A ProductID can
also be a ComponentID. So Product C55, can be made up of Components D32 and
F45
and product T87 can contain product C55 as a component. I am trying to
write a procedure to loop through the recipe table, running code for each
ComponentID which also appears in the table as a product. So far I have
code which loops through the first ProductID but what's the best way of then
looping through all the components which are also products?


Public Function GenerateComponents(ProductID As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ComponentCode As String
Dim strSQL As String

strSQL = "SELECT * FROM tblRecipeComponents WHERE [ProductID] = '" &
ProductID & "'

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
With rs
If Not .BOF Then
.MoveFirst
Do While Not .EOF
ComponentCode = !ComponentID
If DCount("*", "tblRecipeComponents", " [ProductID] = '" &
ComponentCode & "'") > 0 Then
MsgBox "Generating data for: " & ComponentCode, , ProductID &
" contains intermediate " & ComponentCode
GenerateComponentDate ComponentCode
End If
.MoveNext
Loop
End If
End With

End Function


Thanks