Object Model Problem

[Iain Bishop]

I'm trying to model objects for the following problem:

A building site contains assemblies, each of which can contain other
assemblies and/or materials.

I have modelled this using a Site class, Assembly class, and Material class
as follows...

Site Class (clsSite):
Option Explicit
'General Details
Public ProjectNumber As String
Public SiteWBSCode As String
Public SiteType As String
Public LastUpdateFirstName As String
Public LastUpdateSurname As String
Public LastUpdateDate As Date
Public TotalPriceOfAllMaterials As Double
Public GeneralNotes As String
'Contents of Site
Public SiteAssemblies As clsAssemblies 'a collection class of clsAssembly
Public SiteMaterials As clsMaterials 'a collection class of clsMaterial

Assembly Class (clsAssembly):
Option Explicit
'General Details
Public ID As Long
Public QuantityRequested As Integer
Public QuantityIssued As Integer
'Contents
Public AssemblyAssemblies As clsAssemblies 'a collection class of
clsAssembly
Public AssemblyMaterials As clsMaterials 'a collection class of clsMaterial

Material Class (clsMaterial)
Option Explicit
'General Details
Public InventoryCode As String
Public Rate As Double
Public QuantityRequested As Integer
Public QuantityIssued As Integer
Public TotalPrice As Double
Public DateAmended As Date

My questions are:
1. Have I modelled the problem correctly?
2. There is the potential for an infinite hierarchy of Assemblies - is this
ok?
3. If so, I am having problems imagining how I would iterate through say all
the materials to update their 'Rate' for example.
4. I'm also having problems imagining how I would go and find a material if
its 'Quantity Requested' has changed, given that it could be any number of
levels down.

I've spent hours without making much progress. Please help if you know the
answer to any of these questions.

Many Thanks
Iain

[Dikkie Dik]

You did quite well, I think. The fact that assemblies contain
assemblies, and therefore do not have a defined ending level is true,
and not a problem.
Do not search. Let the object model itself do the search for you. For
instance: the TotalPriceOfAllMaterials could easily be a property of
both the materials collection and the assemblies collection. As an
assembly is only made up of subassemblies and materials, You just add
the totals form both collections. The collections are then responsible
to get the totals from all their members. This "drills down" to every
level automatically (this technique is called the Propagation Pattern).
If you are searching for something, just pass down an empty results list
and let the collections fill it.
This may seem a bit performance-heavy, but it isn't. Especially if the
number of possible hits is limited (like searching for an ID), a
collection can stop searching when the limit is reached, thereby
preventing other collections to start an unnecessary search.

There is no risk for an infinite number of assemblies, unless a
subassembly can have a "parent" assembly as one of its own subassemblies
(or even itself as a subassembly). Like a tree, it can grow as many
twigs as it has the chance to, but it will remain finite.

4. I'm also having problems imagining how I would go and find a
material if its 'Quantity Requested' has changed, given that it
could be any number of levels down.
Good question. I think the answer does not lie in your object model yet,
but in the real world. Who changes the 'Quantity Requested' property?
And how does he know about it? Materials may be part of the
assembly-structure, but also part of, say, a bill of materials. If the
same instances are used, updating them in the bill of materials
automatically updates them in the assembly structure.

This may also be an alternative answer to the update question; if the
materials are also in a list, it may be easier to update them there.

Do not be afraid to start with an object model that is not perfect. You
can always refine as you work on it. Speaking from my own experience, it
is better to improve a simple scheme than to try to do a "Big Design
Upfront". Just keep asking what responsibility should lie in what class
and you can solve most questions.

Best regards
Iain Bishop wrote: I'm trying to model objects for the following problem:

A building site contains assemblies, each of which can contain other
assemblies and/or materials.

I have modelled this using a Site class, Assembly class, and Material class
as follows...

Site Class (clsSite):
Option Explicit
'General Details
Public ProjectNumber As String
Public SiteWBSCode As String
Public SiteType As String
Public LastUpdateFirstName As String
Public LastUpdateSurname As String
Public LastUpdateDate As Date
Public TotalPriceOfAllMaterials As Double
Public GeneralNotes As String
'Contents of Site
Public SiteAssemblies As clsAssemblies 'a collection class of clsAssembly
Public SiteMaterials As clsMaterials 'a collection class of clsMaterial

Assembly Class (clsAssembly):
Option Explicit
'General Details
Public ID As Long
Public QuantityRequested As Integer
Public QuantityIssued As Integer
'Contents
Public AssemblyAssemblies As clsAssemblies 'a collection class of
clsAssembly
Public AssemblyMaterials As clsMaterials 'a collection class of clsMaterial

Material Class (clsMaterial)
Option Explicit
'General Details
Public InventoryCode As String
Public Rate As Double
Public QuantityRequested As Integer
Public QuantityIssued As Integer
Public TotalPrice As Double
Public DateAmended As Date

My questions are:
1. Have I modelled the problem correctly?
2. There is the potential for an infinite hierarchy of Assemblies - is this
ok?
3. If so, I am having problems imagining how I would iterate through say all
the materials to update their 'Rate' for example.
4. I'm also having problems imagining how I would go and find a material if
its 'Quantity Requested' has changed, given that it could be any number of
levels down.

I've spent hours without making much progress. Please help if you know the
answer to any of these questions.

Many Thanks
Iain

[Steve Gerrard]

Some general comments and ideas to consider:
---
Something like TotalPriceOfAllMaterials should probably be a method, not a
variable. Unless you have a serious performance problem, it will be cleaner to
actually add up all the material costs each time, rather than storing it and
trying to make sure it stays updated.
---
If you are familiar with Interfaces, or willing to become so, they could make
your model easier. If you had an interface called IMaterial, for instance, then
the Assembly class and Material class could both implement it. This would allow
you to have a single collection that was a mixture of Assembly and Material
objects. I can explain more about it if you are interested - it is not difficult
to do.
---
Your Site class does not need to repeat the structure of an Asssembly - it can
simply contain one. So you could have

'Contents of Site
Public SiteAsm As clsAssembly

which would give you
SiteAsm.AssemblyAssemblies
and
SiteAsm.AssemblyMaterials

and avoid duplication of code.

Remember that your user does not have to know that the entire material structure
of a site is being treated as a single assembly; it is just a programming
convenience. Object design should serve the programmer's purposes, not the end
user's. You could then put TotalPriceOfAllMaterials in the Assembly class, as
described in the post from Dikkie Dik.
---
There is nothing wrong with adding an object reference to two collections (you
only need to make sure that you don't create a cross reference, where two
objects have references to each other).

So, if you are adding a clsMaterial object to some assembly, you can also add it
to a separate MasterList:
Dim MasterList As clsMaterials
Dim Site As clsSite
Dim Mat As clsMaterial

Set Mat = New clsMaterial
Site.SiteAssemblies(2).AssemblyAssemblies(4).Assem blyMaterials.Add Mat
MasterList.Add Mat

This means you can find a material by drilling down through the Site structure,
or by looking in the MasterList. Both collections contain a reference to the
same object, so any changes to it would be seen from either vantage point. You
could even make MasterList a property of the Site class.
---

"Iain Bishop" <ie******@yahoo.co.uk> wrote in message
news:lK**************@news-server.bigpond.net.au...

I'm trying to model objects for the following problem:

A building site contains assemblies, each of which can contain other
assemblies and/or materials.

I have modelled this using a Site class, Assembly class, and Material class
as follows...

Site Class (clsSite):
Option Explicit
'General Details
Public ProjectNumber As String
Public SiteWBSCode As String
Public SiteType As String
Public LastUpdateFirstName As String
Public LastUpdateSurname As String
Public LastUpdateDate As Date
Public TotalPriceOfAllMaterials As Double
Public GeneralNotes As String
'Contents of Site
Public SiteAssemblies As clsAssemblies 'a collection class of clsAssembly
Public SiteMaterials As clsMaterials 'a collection class of clsMaterial

Assembly Class (clsAssembly):
Option Explicit
'General Details
Public ID As Long
Public QuantityRequested As Integer
Public QuantityIssued As Integer
'Contents
Public AssemblyAssemblies As clsAssemblies 'a collection class of
clsAssembly
Public AssemblyMaterials As clsMaterials 'a collection class of clsMaterial

Material Class (clsMaterial)
Option Explicit
'General Details
Public InventoryCode As String
Public Rate As Double
Public QuantityRequested As Integer
Public QuantityIssued As Integer
Public TotalPrice As Double
Public DateAmended As Date

My questions are:
1. Have I modelled the problem correctly?
2. There is the potential for an infinite hierarchy of Assemblies - is this
ok?
3. If so, I am having problems imagining how I would iterate through say all
the materials to update their 'Rate' for example.
4. I'm also having problems imagining how I would go and find a material if
its 'Quantity Requested' has changed, given that it could be any number of
levels down.

I've spent hours without making much progress. Please help if you know the
answer to any of these questions.

Many Thanks
Iain

[Iain Bishop]

Thanks a lot Dikkie and Steve for your responses. Its very much appreciated.
I'm beginning to get an idea of what I need to do now.

Steve, you mentioned interfaces. I know of them but have never used them, so
am not familiar with them or how they could help. Would you mind explaining
how I could use them?

Thanks
Iain
"Steve Gerrard" <my********@comcast.net> wrote in message
news:bI********************@comcast.com...

Some general comments and ideas to consider:
---
Something like TotalPriceOfAllMaterials should probably be a method, not a
variable. Unless you have a serious performance problem, it will be cleaner to actually add up all the material costs each time, rather than storing it and trying to make sure it stays updated.
---
If you are familiar with Interfaces, or willing to become so, they could make your model easier. If you had an interface called IMaterial, for instance, then the Assembly class and Material class could both implement it. This would allow you to have a single collection that was a mixture of Assembly and Material objects. I can explain more about it if you are interested - it is not difficult to do.
---
Your Site class does not need to repeat the structure of an Asssembly - it can simply contain one. So you could have

'Contents of Site
Public SiteAsm As clsAssembly

which would give you
SiteAsm.AssemblyAssemblies
and
SiteAsm.AssemblyMaterials

and avoid duplication of code.

Remember that your user does not have to know that the entire material structure of a site is being treated as a single assembly; it is just a programming
convenience. Object design should serve the programmer's purposes, not the end user's. You could then put TotalPriceOfAllMaterials in the Assembly class, as described in the post from Dikkie Dik.
---
There is nothing wrong with adding an object reference to two collections (you only need to make sure that you don't create a cross reference, where two
objects have references to each other).

So, if you are adding a clsMaterial object to some assembly, you can also add it to a separate MasterList:
Dim MasterList As clsMaterials
Dim Site As clsSite
Dim Mat As clsMaterial

Set Mat = New clsMaterial
Site.SiteAssemblies(2).AssemblyAssemblies(4).Assem blyMaterials.Add Mat
MasterList.Add Mat

This means you can find a material by drilling down through the Site structure, or by looking in the MasterList. Both collections contain a reference to the same object, so any changes to it would be seen from either vantage point. You could even make MasterList a property of the Site class.
---

"Iain Bishop" <ie******@yahoo.co.uk> wrote in message
news:lK**************@news-server.bigpond.net.au...

I'm trying to model objects for the following problem:

A building site contains assemblies, each of which can contain other
assemblies and/or materials.

I have modelled this using a Site class, Assembly class, and Material class as follows...

Site Class (clsSite):
Option Explicit
'General Details
Public ProjectNumber As String
Public SiteWBSCode As String
Public SiteType As String
Public LastUpdateFirstName As String
Public LastUpdateSurname As String
Public LastUpdateDate As Date
Public TotalPriceOfAllMaterials As Double
Public GeneralNotes As String
'Contents of Site
Public SiteAssemblies As clsAssemblies 'a collection class of clsAssembly Public SiteMaterials As clsMaterials 'a collection class of clsMaterial

Assembly Class (clsAssembly):
Option Explicit
'General Details
Public ID As Long
Public QuantityRequested As Integer
Public QuantityIssued As Integer
'Contents
Public AssemblyAssemblies As clsAssemblies 'a collection class of
clsAssembly
Public AssemblyMaterials As clsMaterials 'a collection class of clsMaterial
Material Class (clsMaterial)
Option Explicit
'General Details
Public InventoryCode As String
Public Rate As Double
Public QuantityRequested As Integer
Public QuantityIssued As Integer
Public TotalPrice As Double
Public DateAmended As Date

My questions are:
1. Have I modelled the problem correctly?
2. There is the potential for an infinite hierarchy of Assemblies - is this ok?
3. If so, I am having problems imagining how I would iterate through say all the materials to update their 'Rate' for example.
4. I'm also having problems imagining how I would go and find a material if its 'Quantity Requested' has changed, given that it could be any number of levels down.

I've spent hours without making much progress. Please help if you know the answer to any of these questions.

Many Thanks
Iain

[Steve Gerrard]

"Iain Bishop" <ie******@yahoo.co.uk> wrote in message
news:Ct**************@news-server.bigpond.net.au...

Thanks a lot Dikkie and Steve for your responses. Its very much appreciated.
I'm beginning to get an idea of what I need to do now.

Steve, you mentioned interfaces. I know of them but have never used them, so
am not familiar with them or how they could help. Would you mind explaining
how I could use them?

Thanks
Iain

I have to admit, I was kind of hoping you would ask :)

Here is a demo, which hopefully illustrates the concept. I have tried to keep it
as brief as possible, there are lots of things you might want to add if you
decided to pursue it.

You need one form with a command button, and three classes, named as given.
Paste the code into each one, then run it and click the button. You may want to
trace the execution to see what is happening, especially the GetTotal call at
the end of the button click. Note that clicking the button again will add more
stuff.

'----
' first, the interface, a class called IMaterial

Public Property Get MaterialTotal() As Double
End Property

'----
' that was easy enough. Now the first instance class.
' this is CMaterial, for an individual material item.
' Instead of the usual property Get/Let, I just initialize to
' random values to keep it short.

Implements IMaterial

Private mQuantity As Double
Private mCostPer As Double

' class
Private Sub Class_Initialize()
mQuantity = Fix(Rnd * 10)
mCostPer = Fix(Rnd * 100)
End Sub

' IMaterial implementation
Private Property Get IMaterial_MaterialTotal() As Double
IMaterial_MaterialTotal = mQuantity * mCostPer
End Property

'---
' This is the other instance class, called CAssembly.
' It contains a collection of IMaterial items.

Implements IMaterial

Private mCol As Collection

' class
Private Sub Class_Initialize()
Set mCol = New Collection
End Sub

' public methods
Public Function Add(Item As IMaterial) As IMaterial
mCol.Add Item
' return Item, so New can be used in the call...
Set Add = Item
End Function

Public Function GetTotal() As Double
Dim dSum As Double
Dim iMat As IMaterial
' Note how the GetTotal function just retrieves the
' MaterialTotal of each item, regardless of whether
' it is a material or another assembly...

For Each iMat In mCol
dSum = dSum + iMat.MaterialTotal
Next iMat

GetTotal = dSum

End Function

' IMaterial implementation
Private Property Get IMaterial_MaterialTotal() As Double
IMaterial_MaterialTotal = GetTotal()
End Property

'---
' And last, a Form to test it.

Private mSite As CAssembly

Private Sub Form_Load()
Set mSite = New CAssembly
End Sub

Private Sub Command1_Click()
Dim oAsm As CAssembly
Dim oMat As CMaterial
Dim oAsm2 As CAssembly

' oMat is not actually used here, but you
' would need it to set other properties

'add a mat to the site
Set oMat = mSite.Add(New CMaterial)

'add an assembly to the site
Set oAsm = mSite.Add(New CAssembly)
'add some mats to the assembly
Set oMat = oAsm.Add(New CMaterial)
Set oMat = oAsm.Add(New CMaterial)

'add an assembly to the assembly
Set oAsm2 = oAsm.Add(New CAssembly)
'add some mats to the nested assembly
Set oMat = oAsm2.Add(New CMaterial)
Set oMat = oAsm2.Add(New CMaterial)

'see what we've got
MsgBox "Total is " & mSite.GetTotal

End Sub

[Iain Bishop]

Thanks for that Steve.
I don't think I will use an IMaterial interface for this because I had
already coded up some of the Data Services Layer which assumes an assembly
contains two collections - one for other assemblies and one for materials.
I'll consider it next time though!

Iain

"Steve Gerrard" <my********@comcast.net> wrote in message
news:85********************@comcast.com...

"Iain Bishop" <ie******@yahoo.co.uk> wrote in message
news:Ct**************@news-server.bigpond.net.au...

Thanks a lot Dikkie and Steve for your responses. Its very much appreciated. I'm beginning to get an idea of what I need to do now.

Steve, you mentioned interfaces. I know of them but have never used them, so am not familiar with them or how they could help. Would you mind explaining how I could use them?

Thanks
Iain

I have to admit, I was kind of hoping you would ask :)

Here is a demo, which hopefully illustrates the concept. I have tried to

keep it as brief as possible, there are lots of things you might want to add if you decided to pursue it.

You need one form with a command button, and three classes, named as given. Paste the code into each one, then run it and click the button. You may want to trace the execution to see what is happening, especially the GetTotal call at the end of the button click. Note that clicking the button again will add more stuff.

'----
' first, the interface, a class called IMaterial

Public Property Get MaterialTotal() As Double
End Property

'----
' that was easy enough. Now the first instance class.
' this is CMaterial, for an individual material item.
' Instead of the usual property Get/Let, I just initialize to
' random values to keep it short.

Implements IMaterial

Private mQuantity As Double
Private mCostPer As Double

' class
Private Sub Class_Initialize()
mQuantity = Fix(Rnd * 10)
mCostPer = Fix(Rnd * 100)
End Sub

' IMaterial implementation
Private Property Get IMaterial_MaterialTotal() As Double
IMaterial_MaterialTotal = mQuantity * mCostPer
End Property

'---
' This is the other instance class, called CAssembly.
' It contains a collection of IMaterial items.

Implements IMaterial

Private mCol As Collection

' class
Private Sub Class_Initialize()
Set mCol = New Collection
End Sub

' public methods
Public Function Add(Item As IMaterial) As IMaterial
mCol.Add Item
' return Item, so New can be used in the call...
Set Add = Item
End Function

Public Function GetTotal() As Double
Dim dSum As Double
Dim iMat As IMaterial
' Note how the GetTotal function just retrieves the
' MaterialTotal of each item, regardless of whether
' it is a material or another assembly...

For Each iMat In mCol
dSum = dSum + iMat.MaterialTotal
Next iMat

GetTotal = dSum

End Function

' IMaterial implementation
Private Property Get IMaterial_MaterialTotal() As Double
IMaterial_MaterialTotal = GetTotal()
End Property

'---
' And last, a Form to test it.

Private mSite As CAssembly

Private Sub Form_Load()
Set mSite = New CAssembly
End Sub

Private Sub Command1_Click()
Dim oAsm As CAssembly
Dim oMat As CMaterial
Dim oAsm2 As CAssembly

' oMat is not actually used here, but you
' would need it to set other properties

'add a mat to the site
Set oMat = mSite.Add(New CMaterial)

'add an assembly to the site
Set oAsm = mSite.Add(New CAssembly)
'add some mats to the assembly
Set oMat = oAsm.Add(New CMaterial)
Set oMat = oAsm.Add(New CMaterial)

'add an assembly to the assembly
Set oAsm2 = oAsm.Add(New CAssembly)
'add some mats to the nested assembly
Set oMat = oAsm2.Add(New CMaterial)
Set oMat = oAsm2.Add(New CMaterial)

'see what we've got
MsgBox "Total is " & mSite.GetTotal

End Sub

[Jew]

On Mon, 02 May 2005 23:30:25 GMT, "Iain Bishop" <ie******@yahoo.co.uk> wrote:

I'm trying to model objects for the following problem:

A building site contains assemblies, each of which can contain other
assemblies and/or materials.

I have modelled this using a Site class, Assembly class, and Material class
as follows...

Site Class (clsSite):
Option Explicit
'General Details
Public ProjectNumber As String
Public SiteWBSCode As String
Public SiteType As String
Public LastUpdateFirstName As String
Public LastUpdateSurname As String
Public LastUpdateDate As Date
Public TotalPriceOfAllMaterials As Double
Public GeneralNotes As String
'Contents of Site
Public SiteAssemblies As clsAssemblies 'a collection class of clsAssembly
Public SiteMaterials As clsMaterials 'a collection class of clsMaterial

Assembly Class (clsAssembly):
Option Explicit
'General Details
Public ID As Long
Public QuantityRequested As Integer
Public QuantityIssued As Integer
'Contents
Public AssemblyAssemblies As clsAssemblies 'a collection class of
clsAssembly
Public AssemblyMaterials As clsMaterials 'a collection class of clsMaterial

Material Class (clsMaterial)
Option Explicit
'General Details
Public InventoryCode As String
Public Rate As Double
Public QuantityRequested As Integer
Public QuantityIssued As Integer
Public TotalPrice As Double
Public DateAmended As Date

My questions are:
1. Have I modelled the problem correctly?
The replies you receievd so far sound very good. An exploded Bill
Of Material / Product Structure generally has the financial standard
costs and labor hours regenerated at the end of every month, so I
see no reason why you cannot save some CPU time by *NOT* making your
subtotals methods instead of properties: the End Of Month Closing
routines will perform all the necessary calculations and update the
properties.

For example: the PRMS mid-frame application that runs on the AS/400
has standard labor, standard labor costs, material costs, and the
like as properties (values). At the end of every financial month the
Month End Close / Cost Build programs are run, and new values for
components and sub-assemblies are generated. This generally means
the financial module includes in the database the next/future costing
values, and historical values. The database also contains actual labor
hours, actual labor costs, actual material costs, etc., so that one
may forecast future costs.
2. There is the potential for an infinite hierarchy of Assemblies -
is this ok?
Infinite is bad; finite is good. :-)

You must add a check that looks to see if an assembly's newly-added
component part is not the assembly itself, and (if the component
is a sub-assembly) check to see if any of the newly-added component
parts include the parent part. This is actually very easy to do.

A section of code from my Product Structure modual (changing my
news reader's word-wrap to 700 characters):

Private Sub txtParent_LostFocus()
Dim FindPN As String, sSQL As String, nodX As Node, TheDesc As String, TheUM As String

FindPN = UCase$(Trim(txtParent.Text))
txtParent.Text = FindPN: ' A fuction that looks at the Product Master to see if a part number exists

If FindPN = "" Then Exit Sub

MousePointer = 11
DoEvents

Dim de As Boolean
If ServerLocation = "" Then
de = DoesPNExist(FindPN, TheDesc, TheUM)
Else
de = DF.DoesPNExist(FindPN, TheDesc, TheUM): ' Internet server, use Business Object
End If

If de = True Then
lblDesc.Caption = TheDesc

TreeView1.Nodes.Clear
Set nodX = TreeView1.Nodes.Add()
nodX.Expanded = True
nodX.Text = FindPN
nodX.Image = 1
nodX.SelectedImage = 3
nodX.Key = FindPN
nodX.Tag = 0
List1.AddItem nodX.Text
List2.AddItem nodX.Text

If ServerLocation = "" Then
Set Adodc1.Recordset = GetSMP("SELECT * FROM ProductStructure WHERE Parent = '" & FindPN & "';")
Else
Set Adodc1.Recordset = DF.GetSMP("SELECT * FROM ProductStructure WHERE Parent = '" & FindPN & "';"): ' database is on web server
End If

Do Until Adodc1.Recordset.EOF
Set nodX = TreeView1.Nodes.Add(1, tvwChild)
nodX.Text = Adodc1.Recordset!Child
nodX.Image = 2
nodX.Key = Adodc1.Recordset!Child
nodX.Parent = Adodc1.Recordset!Parent
nodX.SelectedImage = 3
nodX.Tag = 1
List1.AddItem " " & nodX.Text
List2.AddItem "SELECT * FROM ProductStructure WHERE Parent = '" & FindPN & "' AND Child = '" & nodX.Text & "';"
Adodc1.Recordset.MoveNext
Loop
DoEvents
PopulateSubAssemblies
Else
lblDesc.Caption = "The parent assembly does not exist in the product master."
End If
MousePointer = 0
DoEvents
End Sub

Private Sub PopulateSubAssemblies()
Dim nodX As Node, Child As String, Parent As String, Level As Long
Dim LookIndex As Long, LastKey As String, tl As Long, i As Long
Dim lBox As Long

LookIndex = 1

Do
LookIndex = LookIndex + 1
Parent = TreeView1.Nodes(LookIndex).Text
Level = TreeView1.Nodes(LookIndex).Tag
For i = 0 To List1.ListCount - 1
If Trim(List1.List(i)) = Parent Then
lBox = i
Exit For
End If
Next

If ServerLocation = "" Then
Set Adodc1.Recordset = GetSMP("SELECT * FROM ProductStructure WHERE Parent = '" & Parent & "' ORDER BY Child;")
Else
Set Adodc1.Recordset = DF.GetSMP("SELECT * FROM ProductStructure WHERE Parent = '" & Parent & "' ORDER BY Child;"): ' SMPBO
End If

If Adodc1.Recordset.EOF = False Then

TreeView1.Nodes(LookIndex).Image = 1
TreeView1.Nodes(LookIndex).Expanded = True

Adodc1.Recordset.MoveFirst

Do While Adodc1.Recordset.EOF = False
Child = Adodc1.Recordset!Child
Set nodX = TreeView1.Nodes.Add(Parent, tvwChild, , Child, 2, 3)
nodX.Tag = Level + 1
List1.AddItem String$((Level + 1) * 3, " ") & nodX.Text, lBox + 1
List2.AddItem "SELECT * FROM ProductStructure WHERE Parent = '" & Parent & "' AND Child = '" & nodX.Text & "';", lBox + 1

Adodc1.Recordset.MoveNext
Loop
End If
If LookIndex = TreeView1.Nodes.Count Then Exit Do
Loop
btnReport.Enabled = True
End Sub

What my program does is first check to see if the database is
local, or if it is on a web server. It then checks to see if
the part number is in the Product Master; if it is, it checks
to see if it is an assembly: if it is not in the Product Structure,
it is assumed to be a component and not an assembly.

If the part number is in the Product Structure, it is an assembly.
Every component of that assembly is filled into the TreeView, and
then the TreeView is stepped through. No matter how many new
assemblies are added, the TreeView is filled in below what ever
assembly and component it is currently examining, so one can make
a exploded Bill of Material as large as one's computer has RAM.
3. If so, I am having problems imagining how I would iterate through
say all the materials to update their 'Rate' for example.
What I did was to step through each sub-assembly and build a list
of SQL statements that will later retrieve the Product Structures
for those sub-assemblies. If a sub-assembly has sub-assemblies
within them, I just create new SQL statements and add them to
the end of my list (in my program I used a list box, but an
array would also work).
4. I'm also having problems imagining how I would go and find a material if
its 'Quantity Requested' has changed, given that it could be any number of
levels down.
The quantity required would be the sum of each unique component in an
exploded Bill Of Material. You may have an assembly with fifty
sub-assemblies, and a dozen of those sub-assemblies may have
sub-sub-assemblies. To print a "Pick Sheet" you have to iterate
through the assembly and sub-assemblies and sub-sub-assemblies and
sum up each unique component. This is very easy to do.
I've spent hours without making much progress. Please help if you
know the answer to any of these questions.
Shall I post on a web page a PKZip example for you?
Many Thanks
Iain