Hi, thanks for the feedback. David, I did try Joe's nested set and it
works fine, but the left and right labelling of nodes and how this
could be done with code threw me a bit.
I example I am using has been written by Robin Stoddard-Stone and is
available for download from
http://www.mvps.org/access/resources/downloads.htm under "BOM". The
output table is really just included for the sake of the demonstration.
The Components and Assemblies tables are the most important one. The VB
code works recursively. You choose an assembly, it checks the
components to see if any are assemblies themselves, then the components
of the sub assemblies are checked and so on.
I'm including the VB code underneath with the comments to see if it
makes things clearer. I understand how it works. My problem is how to
create forms/subforms which will allow a user amend or add a new
assembly. Phil if your recipe DB was anyway near the same sort of idea,
it would be great to see it.
Thanks
VB code of BOMS
---------------------------
Attribute VB_Name = "Bom1"
Option Compare Database
Option Explicit
'The Bill of materials processor:
' is designed to allow you to decompose an assembly into its component
items.
' operates at multiple levels, where each item at each level can be a
component
' or a sub-assembly. (SubAssemblies are further decomposed by the
breakdown).
' uses 3 tables
' :Table 1, Assemblies contains the list of items needed to assemble
any assembly.
' the items can be subassemblies or components.
' Comprises the following fields.
' : ComponentID, a text field to allow the user to recognise the
item.
' : ParentID, a code field to allow linking.
' : NumberRequired, The number of units required for this level of
this assembly.
' For a given assembly, the items that go into its construction are
entered into the
' ComponentID field, while the parentID contains the code identifying
the given assembly.
'
'Table 2, Components contains the list of items recognised by the
Assemblies Table
' Comprises the following fields
' : ComponentID, a unique key, identifying the component
' : ComponentDescription describes the component
' : AssemblyBoolean to identify whether this is an assembly which
has a breakdown
' entered in the Assemblies Table.
'Table 3, the Output Table is a variable purpose table which, in this
example, identifies the
' the components and the number required to construct any assembly
' Comprises the following fields
' :ComponentID the same format as the Component Table.
' :Number Required. A Count of the number of items required.
'To Start the demonstration, select the assembly of choice from the
input form
To add items to the demonstration,
add the elements to the Components Table, identifying whether they
Assemblies or Components. Note that an assembly is an element which
will have sub components. An Assembly
may be a subcomponent itself of another assembly.
Enter the assembly units into the assemblies table. You only need to
assemble each assembly once. That is,
if an assembly has subcomponents that are in themselves assemblies, you
reference that sub assembly name, not
add all the components again. Note that when entering an assembly, you
do not add the Assembly record for itself.
E.G: if entering the assembly for a wheelHub, you do not add the
wheelhub component itself to the Assembly record
otherwise the program will loop. ( Each wheel hub requires a wheel hub
assembly which requires a wheel hub assembly etc.)
Comments in the code should make its operation clear. Start with the
BOMHost procedure.
Const Qu = """"
Type typBits
Component As String
NumberOF As Integer
End Type
Sub BOMHost(strAssemblyP As String)
Dim db1 As Database
Dim rs1 As Recordset
Dim iArray() As typBits
'Dim strAssembly As String
Dim fDone As Integer ' flag as it is bad practice to
modify loop parameters within loop
Dim iCurrent As Integer ' where you are in the current array
Dim intMultiplier As Integer ' multiplication factor for number
of items
Set db1 = CurrentDb()
ReDim iArray(0)
fDone = False
iCurrent = 0
intMultiplier = 1
DoCmd.RunSQL ("Delete *.* from OutPutTable") ' clear the outputTable
If GetSubAssembly(strAssemblyP, db1, rs1) = 0 Then GoTo BOMHost_End
'Set up the array for the item you wish to decompose.
ParseList iArray(), rs1, UBound(iArray), intMultiplier
Do Until fDone
'Take the next item in the array, (for the first item, the next item is
the
'first unit in the array). Gets the constituent items from the assembly
table referencing
'the parentID. If the item has subcomponents then the item can be
decomposed further.
' if the item is a component (no further decomposition) then it goes to
Output.
'Otherwise it is a subAssembly and you add the parts to the array
using ParseList.
If GetSubAssembly(iArray(iCurrent).Component, db1, rs1) = 0 Then
AddtoOutput db1, iArray, iCurrent
Else
intMultiplier = iArray(iCurrent).NumberOF
ParseList iArray(), rs1, UBound(iArray), intMultiplier
End If
'That has finished the processing for the item in the array, so
increment the pointer to
'your current position and test to see if you have finished.( your
current pointer is now
'equal to the array UBound). ' if not go back and do the next Item,
'if so then Output Table contains the BillOfMaterials list and you are
finished.
iCurrent = iCurrent + 1
If iCurrent = UBound(iArray) Then fDone = True
Loop
MsgBox "Completed"
BOMHost_End:
db1.Close
End Sub
Private Function GetSubAssembly(strParentID As String, db1 As Database,
rs1 As Recordset) As Integer
' returns 0 if no records, else 1 (doesn't move to end of recordset)
Set rs1 = db1.OpenRecordset("select
Assemblies.ComponentID,Assemblies.NumberRequired from Assemblies where
" _
& "(((Assemblies.ParentID)=" & Qu & strParentID & Qu & "))",
DB_OPEN_DYNASET)
GetSubAssembly = rs1.RecordCount
End Function
'Gets the individual records from rs1 (argument recordset) and puts
them into the array
Private Sub ParseList(iArray() As typBits, rs1 As Recordset,
intLastPosition As Integer, intMultiplier As Integer)
Dim intSize As Integer
'iArray() is integer array defined in the host procedure
'rs1 the recordset to get data from
'intLastPosition the last position in the array
' intMultiplier is the multiplying factor based on the number of parent
units required
intSize = intLastPosition + 1
Do Until rs1.EOF
ReDim Preserve iArray(intSize)
iArray(intLastPosition).Component = rs1!ComponentID
iArray(intLastPosition).NumberOF = rs1!NumberRequired *
intMultiplier
rs1.Move 1
intSize = intSize + 1
intLastPosition = intLastPosition + 1
Loop
End Sub
'Components are added to the output table . Modify this module to
modify Output
Private Sub AddtoOutput(db1 As Database, iArray() As typBits, iICurrent
As Integer)
Dim rs1 As Recordset
Dim strAssemblyID As String
Dim intNumberOf As Integer
strAssemblyID = iArray(iICurrent).Component
intNumberOf = iArray(iICurrent).NumberOF
Set rs1 = db1.OpenRecordset("select OutPutTable.* from OutputTable
where " _
& "(( OutPutTable.ComponentID=" & Qu & strAssemblyID & Qu & "))",
DB_OPEN_DYNASET)
If rs1.RecordCount = 0 Then 'the component is new
rs1.AddNew
rs1!ComponentID = strAssemblyID
rs1!NumberRequired = intNumberOf
rs1.Update
Else
rs1.Edit
rs1!NumberRequired = rs1!NumberRequired + intNumberOf
rs1.Update
End If
End Sub
