Recursive code to create indented bill of material

[Gary Manigian]

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list the
parts and assemblies with their respective parts in a hierarchical list. I
want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found exactly
what I an looking for. I thought I would create a list of parts that
contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

[Alan Webb]

Gary,
I wrote one and ran into problems with filling the call stack before I had
maxed out my levels on my hierarchy. The one cool way around this is to
identify how deep the tree is and then build a table with as many columns as
there are levels in the tree. *Most* trees tend to be between 3 and 7
levels meaning most hierarchies could be mapped in a table with 7 columns.
Anyway, building your report would mean iterating through the columns of
this table from a beginning point. As an example, let's say CD jewel cases
are part of the finished good, Virtual PC. The jewel case is near the
bottom of the hierarchy because it consists of four components and contains
a CD, which is at the bottom of the hierarchy. The jewel case goes into an
interior box that holds it in place which goes into an outer box. Anyway,
by my count we've got four levels meaning we need four colums to map every
combination of CD, jewel case & boxes. Virtual PC is part #vpc_42005 in my
example. So, I find the row in the first column that has my part number and
then loop through my columns to find component parts. Done, and no
recursion needed.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Gary Manigian" <ga***@designconcepts.net> wrote in message
news:30****************@fe10.lga...

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list
the parts and assemblies with their respective parts in a hierarchical
list. I want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found
exactly what I an looking for. I thought I would create a list of parts
that contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

[pietlinden]

read these,,,
http://www.mvps.org/access/queries/qry0023.htm
http://www.mvps.org/access/modules/mdl0027.htm

[pietlinden]

read these,,,
http://www.mvps.org/access/queries/qry0023.htm
http://www.mvps.org/access/modules/mdl0027.htm

and if you really want a brain teaser, read Joe Celko's "SQL for
Smarties". Not being a smarty, I guess I'm not qualified...

[ByteMyzer]

"Gary Manigian" <ga***@designconcepts.net> wrote in message
news:30****************@fe10.lga...

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list the parts and assemblies with their respective parts in a hierarchical list. I
want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found exactly what I an looking for. I thought I would create a list of parts that
contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

For a TRULY recursive BOM, there needs to be some revision to the table
structure, for the sake of normalization, like so:

tblBOM: (Stores all part Keys, Part Numbers and Descriptions, no need for
this data in BOTH tables)
lngBOMID (PK)
strPartNo
strDescription

tblBOMDetail: (Stores only the parent-child relationships and the qty-per)
lngBOMDetailID (PK)
lngBOMID (FK)
intQuantity

Then, paste the following code into a new module:
'---------------<< begin code >>---------------
Public Sub EnumBOM(Optional llc As Long = 0, Optional PK As Long)

Dim rst As Recordset
Dim x As Long

If llc Then
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM INNER JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID=" & PK & ";")
Else
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM LEFT JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID Is Null;")
End If

Do While Not rst.EOF
For x = 1 To llc
Debug.Print vbTab;
Next x
Debug.Print rst!strPartNo; ": "; rst!strDescription
If IsNull(rst!FK) = False Then EnumBOM llc + 1, rst!lngBOMID
rst.MoveNext
Loop

rst.Close

End Sub

'----------------<< end code >>----------------

I tested the above with the following sample data in the tables:
tblBOM:
lngBOMID strPartNo strDescription
1 AssyNo1 Assembly No. 1
2 AssyNo2 Assembly No. 2
3 Comp1 Component 1
4 Comp2 Component 2
5 Comp3 Component 3
6 SubAssy1 Sub-Assembly 1
7 SubAssy2 Sub-Assembly 2
8 SubAssy3 Sub-Assembly 3

tblBOMDetail:
lngBOMDetailID lngBOMID intQuantity
1 3 1
7 3 1
1 4 1
2 4 1
7 4 1
2 5 1
6 5 1
8 5 1
1 6 1
2 7 1
7 8 1

With the tables populated with the above data, running EnumBOM produced this
result:
AssyNo1: Assembly No. 1
Comp1: Component 1
Comp2: Component 2
SubAssy1: Sub-Assembly 1
Comp3: Component 3
AssyNo2: Assembly No. 2
Comp2: Component 2
Comp3: Component 3
SubAssy2: Sub-Assembly 2
Comp1: Component 1
Comp2: Component 2
SubAssy3: Sub-Assembly 3
Comp3: Component 3

If you study the above information carefully, you will see how it works. If
you would like a more detailed elaboration, by all means, ask.

[Steve Jorgensen]

On Wed, 13 Apr 2005 21:43:25 -0400, "Alan Webb" <kn*****@hotmail.com> wrote:

Gary,
I wrote one and ran into problems with filling the call stack before I had
maxed out my levels on my hierarchy. The one cool way around this is to

What version of Access was this? I wrote code to deal with BOMs using
recursive code a while back in Access 2000, and it worked fine at 12 levels or
so.

You do have to make sure there are no recursive tree branches! It's harder to
prevent this than you might think, and the user error is not as hard to make
as you might think.

[Johnson Cheung]

Back to 10 years.... Writing a recursive BOM listing in a language
not supporting recursive (like BASICA or RPG/400) would be painfull.
Write it in BASICA, then re-write in RPG/400.

Every local variables should put to stack implemented by array.

[Alan Webb]

Steve,
It was Access 2000 on a complex bill of materials for a fulfillment house
that did finish packaging of software. It was at Schwab where I saw the
table with each column of the hierarchy and I thought it was cool.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:m9********************************@4ax.com...

On Wed, 13 Apr 2005 21:43:25 -0400, "Alan Webb" <kn*****@hotmail.com>
wrote:

Gary,
I wrote one and ran into problems with filling the call stack before I had
maxed out my levels on my hierarchy. The one cool way around this is to

What version of Access was this? I wrote code to deal with BOMs using
recursive code a while back in Access 2000, and it worked fine at 12
levels or
so.

You do have to make sure there are no recursive tree branches! It's
harder to
prevent this than you might think, and the user error is not as hard to
make
as you might think.

[Phil Stanton]

If its any help I wrote a database for recipes (Actually Flavours for a
flavour making company). Each flavour consisted of ingredients and
optionally other flavours. E.G A cheese and onion flavour consisted of a
cheese flavour + oil of onion etc. The cheese flavour consists of a cheese,
a chemical etc
There was no limit on how name flavours were nested

Phil
"Gary Manigian" <ga***@designconcepts.net> wrote in message
news:30****************@fe10.lga...

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list
the parts and assemblies with their respective parts in a hierarchical
list. I want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found
exactly what I an looking for. I thought I would create a list of parts
that contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

[Bob Quintal]

"Gary Manigian" <ga***@designconcepts.net> wrote in
news:30****************@fe10.lga:

I have 2 tables, one-to-many, that contain bills of
material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I
need to list the parts and assemblies with their respective
parts in a hierarchical list. I want to do this recursively in
VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't
found exactly what I an looking for. I thought I would create
a list of parts that contained the indent level and from there
create the report.

What I am really stuck on is creating the recursive routine.
Can someone please get me started.

Thanks in advance.

First I'd make a couple of minor changes to your structure.It's
not normalized.

TBL_PARTNo
str_PartNo, PK
Str_Description
str_UM

Tbl_ProductStructure
str_ChildPartNo FK
str_ParentPartNo FK
currQtyPer (you may want to handle 0.5 tube of glue)

pk is the two FKs

The way I approached the problem was not to go recursively, but
to create an array of datasets, writing out to a temporary table.
a simple do until EOF() loop walks the first recordset, tests if
the current part is a parent, and opens the new recordset in the
next Indenture Level element of the array if it is. If it's not a
parent just move to the next record

Hit EOF() and decrement the Indenture level, pointing back to the
parent.

Loop.

It creates a 4000 row table in about 20 seconds, using SQL_Server
tables,

I also have a string array where I store the parent partNOs and
walk that to test for inbreeding (item is a child or grandchild
of itself) which leads to infinite depth BOMs.
--
Bob Quintal

PA is y I've altered my email address.

[Wayne Gillespie]

On Wed, 13 Apr 2005 20:42:49 -0400, "Gary Manigian"
<ga***@designconcepts.net> wrote:

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list the
parts and assemblies with their respective parts in a hierarchical list. I
want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found exactly
what I an looking for. I thought I would create a list of parts that
contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

I have a couple of test databases demonstating recursive routines for
a BOM at -

http://www.bestfitsoftware.com.au/do...s/bomtests.zip

Files are in Access97

[Gary Manigian]

Identifying the number of levels is part of my problem. Each BOM contains
subassemlies which could contain other subassemblies. I won't know until I
traverse the entire BOM. Or am I missing something?

"Alan Webb" <kn*****@hotmail.com> wrote in message
news:Ir********************@comcast.com...

Gary,
I wrote one and ran into problems with filling the call stack before I had
maxed out my levels on my hierarchy. The one cool way around this is to
identify how deep the tree is and then build a table with as many columns
as there are levels in the tree. *Most* trees tend to be between 3 and 7
levels meaning most hierarchies could be mapped in a table with 7 columns.
Anyway, building your report would mean iterating through the columns of
this table from a beginning point. As an example, let's say CD jewel
cases are part of the finished good, Virtual PC. The jewel case is near
the bottom of the hierarchy because it consists of four components and
contains a CD, which is at the bottom of the hierarchy. The jewel case
goes into an interior box that holds it in place which goes into an outer
box. Anyway, by my count we've got four levels meaning we need four
colums to map every combination of CD, jewel case & boxes. Virtual PC is
part #vpc_42005 in my example. So, I find the row in the first column
that has my part number and then loop through my columns to find component
parts. Done, and no recursion needed.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Gary Manigian" <ga***@designconcepts.net> wrote in message
news:30****************@fe10.lga...

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list
the parts and assemblies with their respective parts in a hierarchical
list. I want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found
exactly what I an looking for. I thought I would create a list of parts
that contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

[Gary Manigian]

I would like to thank everyone for their replies. It will take me a while to
work my way through it.

Gary
"Gary Manigian" <ga***@designconcepts.net> wrote in message
news:30****************@fe10.lga...

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list
the parts and assemblies with their respective parts in a hierarchical
list. I want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found
exactly what I an looking for. I thought I would create a list of parts
that contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

[pietlinden]

While you're at it, read Joe Celko's comments on recursive trees
here... LOTS of fun.

[Gary Manigian]

Here are some interesting links:

Joe Celko's Trees and Hierarchies in SQL:
http://www.elsevier.com/wps/find/boo...on#description

Unconventional Ways to Avoid SQL Recursion:
http://weblogs.asp.net/jezell/archiv.../10/87010.aspx

Graphs, Trees and Hierarchies:
http://www.dbazine.com/celko24.shtml

Trees in SQL
http://www.dbmsmag.com/9603d06.html

Storing Hierarchical Data in a Database
http://www.sitepoint.com/article/hie...-data-database
<pi********@hotmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

While you're at it, read Joe Celko's comments on recursive trees
here... LOTS of fun.

[Gary Manigian]

I set up a db to try your approach but I:
1) get a "Join expression not supported" error when executing EnumBOM w/o
parameters
2) didn't create a PK for tblBOMDetail.lngBOMDetailID because there is
duplicate data.
3) changed tblBOMDetail2 to tblBOMDetail in the SQL string.

"ByteMyzer" <sb*@news.sf.sbcglobal.net> wrote in message
news:Za****************@newssvr14.news.prodigy.com ...

"Gary Manigian" <ga***@designconcepts.net> wrote in message
news:30****************@fe10.lga...

I have 2 tables, one-to-many, that contain bills of material(BOMs):

tblBOM:
lngBOMID (PK)
strAssemblyPartNo
strDescription

tblBOMDetail:
lngBOMDetailID (PK)
lngBOMID (FK)
strPartNo
strDescription
intQuantity

The BOMs contains individual parts and other assemblies. I need to list

the

parts and assemblies with their respective parts in a hierarchical list.
I
want to do this recursively in VBA w/ DAO or ADO.

I have searched around for a solution for a while but haven't found

exactly

what I an looking for. I thought I would create a list of parts that
contained the indent level and from there create the report.

What I am really stuck on is creating the recursive routine. Can someone
please get me started.

Thanks in advance.

For a TRULY recursive BOM, there needs to be some revision to the table
structure, for the sake of normalization, like so:

tblBOM: (Stores all part Keys, Part Numbers and Descriptions, no need for
this data in BOTH tables)
lngBOMID (PK)
strPartNo
strDescription

tblBOMDetail: (Stores only the parent-child relationships and the qty-per)
lngBOMDetailID (PK)
lngBOMID (FK)
intQuantity

Then, paste the following code into a new module:
'---------------<< begin code >>---------------
Public Sub EnumBOM(Optional llc As Long = 0, Optional PK As Long)

Dim rst As Recordset
Dim x As Long

If llc Then
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM INNER JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID=" & PK & ";")
Else
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM LEFT JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID Is Null;")
End If

Do While Not rst.EOF
For x = 1 To llc
Debug.Print vbTab;
Next x
Debug.Print rst!strPartNo; ": "; rst!strDescription
If IsNull(rst!FK) = False Then EnumBOM llc + 1, rst!lngBOMID
rst.MoveNext
Loop

rst.Close

End Sub

'----------------<< end code >>----------------

I tested the above with the following sample data in the tables:
tblBOM:
lngBOMID strPartNo strDescription
1 AssyNo1 Assembly No. 1
2 AssyNo2 Assembly No. 2
3 Comp1 Component 1
4 Comp2 Component 2
5 Comp3 Component 3
6 SubAssy1 Sub-Assembly 1
7 SubAssy2 Sub-Assembly 2
8 SubAssy3 Sub-Assembly 3

tblBOMDetail:
lngBOMDetailID lngBOMID intQuantity
1 3 1
7 3 1
1 4 1
2 4 1
7 4 1
2 5 1
6 5 1
8 5 1
1 6 1
2 7 1
7 8 1

With the tables populated with the above data, running EnumBOM produced
this
result:
AssyNo1: Assembly No. 1
Comp1: Component 1
Comp2: Component 2
SubAssy1: Sub-Assembly 1
Comp3: Component 3
AssyNo2: Assembly No. 2
Comp2: Component 2
Comp3: Component 3
SubAssy2: Sub-Assembly 2
Comp1: Component 1
Comp2: Component 2
SubAssy3: Sub-Assembly 3
Comp3: Component 3

If you study the above information carefully, you will see how it works.
If
you would like a more detailed elaboration, by all means, ask.

[William McGee]

ByteMyzer,
I implemented your code and I have a question. The Enum sub as written
will operate one of two ways, if I enter Enum 0,1 I get all the bills,
if I enter Enum 1,1 I get the bill for part 1 without the parent level?
Is this as designed?
I created Enum as a function. Then I created a function to basically
debug.print the part I enter as the parent level and pass the part to
the Enum function. This seems extra to me.
Also if I guard against the passing of null values, then I don't need
to allow for printing of all bills. The reason I am belaboring this
point is that my BOM file is 5 million records and growing, if someone
accidentally passed nulls, we would be here for days or weeks waiting
for results.
Here are my functions below:

Function testbom()

Dim dummy As Boolean

dummy = RunBOM(8) 'Part Number 8

End Function

Function RunBOM(Part As Long) As Boolean
Dim dummy As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQl As String

Set db = CurrentDb

strSQl = "SELECT lngBOMID, strPartNo, strDescription " _
& "FROM tblBOM " _
& "WHERE tblBOM.lngBOMID = " & Part & ";"

Set rst = CurrentDb.OpenRecordset(strSQl)

Debug.Print rst!strPartNo; ": "; rst!strDescription

dummy = EnumBOM(1, Part)

End Function
*** Sent via Developersdex http://www.developersdex.com ***

[ByteMyzer]

"William McGee" <ha*******@beaerospace.com> wrote in message
news:XI***************@news.uswest.net...

ByteMyzer,
I implemented your code and I have a question. The Enum sub as written
will operate one of two ways, if I enter Enum 0,1 I get all the bills,
if I enter Enum 1,1 I get the bill for part 1 without the parent level?
Is this as designed?
I created Enum as a function. Then I created a function to basically
debug.print the part I enter as the parent level and pass the part to
the Enum function. This seems extra to me.
Also if I guard against the passing of null values, then I don't need
to allow for printing of all bills. The reason I am belaboring this
point is that my BOM file is 5 million records and growing, if someone
accidentally passed nulls, we would be here for days or weeks waiting
for results.
Here are my functions below:

Function testbom()

Dim dummy As Boolean

dummy = RunBOM(8) 'Part Number 8

End Function

Function RunBOM(Part As Long) As Boolean
Dim dummy As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQl As String

Set db = CurrentDb

strSQl = "SELECT lngBOMID, strPartNo, strDescription " _
& "FROM tblBOM " _
& "WHERE tblBOM.lngBOMID = " & Part & ";"

Set rst = CurrentDb.OpenRecordset(strSQl)

Debug.Print rst!strPartNo; ": "; rst!strDescription

dummy = EnumBOM(1, Part)

End Function
*** Sent via Developersdex http://www.developersdex.com ***

If you examined my code closely, you would realize that it was merely sample
code for the purpose of enumerating an ENTIRE Bill of Materials, not for a
specific top-level part. However, with the following modified function,
this is possible:

'---------------<< begin code >>---------------
Public Function EnumBOM(Optional llc As Long = 0, Optional PK As Long = -1)
As Boolean

Dim rst As Recordset
Dim x As Long

If llc Then
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM INNER JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID=" & PK & ";")
Else
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM LEFT JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE " _
& IIf(PK > -1, "tblBOM.lngBOMID=" & PK & ";",
"tblBOMDetail.lngBOMDetailID Is Null;"))
End If

Do While Not rst.EOF
For x = 1 To llc
Debug.Print vbTab;
Next x
Debug.Print rst!strPartNo; ": "; rst!strDescription
If IsNull(rst!FK) = False Then EnumBOM llc + 1, rst!lngBOMID
rst.MoveNext
Loop

rst.Close

End Sub

'----------------<< end code >>----------------
Then modify YOUR code thus:

'---------------<< begin code >>---------------
Function RunBOM(Part As Long) As Boolean
Dim dummy As Boolean

dummy = EnumBOM(0, Part)

End Function
'----------------<< end code >>----------------

NOTE:

EnumBOM
This will enumerate the entire Bill of Materials
EnumBOM 0,1
This will enumerate the Bill of Materials with the part associated with
the PK value of 1 as the Top-Level Part.
EnumBOM 0,4
This will enumerate the Bill of Materials with the part associated with
the PK value of 4 as the Top-Level Part.

Do NOT make calls to EnumBOM with the first value of anything other than 0.
This is done internally by the function itself to accurately execute the
recursive enumerations.

[Hal McGee]

Bytemizer,
Thanks for the quick reply. I had to make the following changes to get
the code to work, but it works now. Probably differences in our set ups.
I also had to add DAO to my reference list since mine defaults to ADO.

Public Function EnumBOM3(Optional llc As Long = 0, Optional PK As Long =
-1) As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim x As Long
Dim strLLCTest As String
Dim blDummy As Boolean
Set db = CurrentDb

If PK > -1 Then
strLLCTest = "tblBOM.lngBOMID=" & PK & ";"
Else
strLLCTest = "tblBOMDetail.lngBOMDetailID Is Null;"
End If

If llc Then
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM INNER JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID=" & PK & ";")
Else
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK " _
& "FROM (tblBOM LEFT JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE " & strLLCTest)
End If

Do While Not rst.EOF
For x = 1 To llc
Debug.Print vbTab;
Next x
Debug.Print rst!strPartNo; ": "; rst!strDescription
If IsNull(rst!FK) = False Then
blDummy = EnumBOM3(llc + 1, rst!lngBOMID)
End If
rst.MoveNext
Loop

rst.Close

End Function

Function RunBOM3() As Boolean
Dim dummy As Boolean

dummy = EnumBOM3(0, 2) 'Part 2

End Function

As an aside, I believe you are copying the SQL from Access queries, if
so I will warn you that Access does a very conservative job of writing
SQL statements, as such, performance can suffer. I took a good SQL class
and now I write my own SQL and it is much more efficient that the SQL
Access writes.
Thanks again for the quick reply. This will be the base from which I
will modify to get the reports we need.
*** Sent via Developersdex http://www.developersdex.com ***

[Hal McGee]

Oops!

Small problem, we repeat assemblies on our bills, this code only returns
one iteration of the assembly.

I will fix it, just wanted others to know about this issue.

*** Sent via Developersdex http://www.developersdex.com ***

[Hal McGee]

Fixed it! Code below but explanations first.

I work with two commercial products, while there may be some
disagreement about repeating assemblies on a bill, it is a common
practice for a variety of reasons. The two commercial products I work
with have two different methods for working with repeated lines. One
commercial product is keyed over the parent and a line number. This
combination must be unique for the file. Although you can have an index
over parent and child for efficiency, it cannot require uniqueness. The
other commercial product uses an Enterprise key, which in a nutshell is
an autonumber (grossly oversimplified) for each line in the bill. I
added both to my BOM table. In the example below, I am using the line as
the field to generate the proper BOM. The index could also be used.

Public Function EnumBOM3(Optional llc As Long = 0, Optional PK As Long =
-1) As Boolean
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim x As Long
Dim strLLCTest As String
Dim blDummy As Boolean
Dim strSQL As String

Set db = CurrentDb

If PK > -1 Then
strLLCTest = "tblBOM.lngBOMID=" & PK & ";"
Else
strLLCTest = "tblBOMDetail.lngBOMDetailID Is Null;"
End If

If llc Then
strSQL = "SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK,
tblBOMDetail.intLine " _
& "FROM (tblBOM INNER JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE tblBOMDetail.lngBOMDetailID=" & PK & ";"
Set rst = CurrentDb.OpenRecordset(strSQL)
Else
strSQL = "SELECT DISTINCT tblBOM.*, " _
& "IsNull(tblBOMDetail2.lngBOMID) AS FK,
tblBOMDetail.intLine " _
& "FROM (tblBOM LEFT JOIN tblBOMDetail " _
& "ON tblBOM.lngBOMID=tblBOMDetail.lngBOMID) " _
& "LEFT JOIN tblBOMDetail AS tblBOMDetail2 " _
& "ON tblBOM.lngBOMID=tblBOMDetail2.lngBOMDetailID " _
& "WHERE " & strLLCTest
Set rst = CurrentDb.OpenRecordset(strSQL)
Debug.Print strSQL
End If

Do While Not rst.EOF
For x = 1 To llc
Debug.Print vbTab;
Next x
Debug.Print rst!strPartNo; ": "; rst!strDescription
If IsNull(rst!FK) = False Then
blDummy = EnumBOM3(llc + 1, rst!lngBOMID)
End If
rst.MoveNext
Loop

rst.Close

End Function

Function RunBOM3() As Boolean
Dim dummy As Boolean

dummy = EnumBOM3(0, 2) 'Part 2

End Function

Table Data
I added Index as an autonumber and intLine as an integer representing
the line on which the parent child relationship exists.
tblBOMDetail
Index lngBOMDetailID lngBOMID intQuantity intLine
1 1 3 1 1
3 1 4 1 2
9 1 6 1 3
4 2 4 1 2
6 2 5 1 3
12 2 7 1 4
10 2 7 1 5
7 6 5 1 2
2 7 3 1 2
5 7 4 1 3
11 7 8 1 4
8 8 5 1 2

When I run this for Parent part 2, I get the following:
AssyNo2 *: Assembly No. 2
Comp2 **: Component 2
Comp3 **: Component 3
SubAssy2: Sub-Assembly 2
Comp1 **: Component 1
Comp2 **: Component 2
SubAssy3: Sub-Assembly 3
Comp3 **: Component 3
SubAssy2: Sub-Assembly 2
Comp1 **: Component 1
Comp2 **: Component 2
SubAssy3: Sub-Assembly 3
Comp3 **: Component 3

Thanks again for all the help. Let me know if furhter explanation is
needed.

*** Sent via Developersdex http://www.developersdex.com ***