BOM Question

[yhengataccessmonster]

I used an example of BOM (Bill of Materials), the link of which is provided
in this website:

http://www.mvps.org/access/modules/mdl0027.htm

The example looks like the one I need to be able to start the BOM database I
am constructing. I modified the table “Assemblies†to include the field
“ComponentDescriptionâ€.

I would like to show the “ComponentDescription†in the OutputTable in
addition to the fields “ComponentID†and “NumberRequired†already there. How
would I do it?

Thanks for any help.

Yheng Pooh

PS. Here is the shortcut of codes used in the above link.

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


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