Bill of materials in Access

[Guest]

Has anyone samples of Bills of Materials in Access. I am trying to build one
and do not know how to do that.

Thank you in advance,

Regards,
Elena

 

[Guest]

Has anyone samples of Bills of Materials in Access. I am trying to build one
and do not know how to do that.

Thank you in advance,

Regards,
Elena

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

(david)

 

[Bob Quintal]

Has anyone samples of Bills of Materials in Access. I am trying to
build one and do not know how to do that.

Thank you in advance,

Regards,
Elena

http://groups.google.com/group/comp.databases.ms-
access/browse_frm/thread/297c01f1d5257aef/818b9d061280179e?
lnk=gst&q=explosion+Quintal&rnum=2#818b9d061280179e

Is code that explodes a BOM from a Product Structure table
Parent_item, ChildItem, QtyPer, etc

And an Items Table
Item, Item_Name, UnitOfMeasure, etc.

 

[Guest]

Hi David,

Thank you for the link. However I have trouble in opening it as it is Access
97 and I am using Access 2002. I copied it to my files and converted it but I
wonder if it survived conversion because all I get is the 3 tables and one
form named Form1. When I open the form and select the assembly of choice, it
asks me if I want to delete the rows from the specified table. But it does
not show me the Bill of Material as I expect it to do...If I say now I get a
runtime error and it tells me that the Run SQL action is cancelled.

Not sure what I am doing wrong...?

Thank you again,
Regards,
Elena

 

[Guest]

mHi Bob,

The link that you send me has a link to a BOM.mdb attached but it does not
work. I think the owner removed it as the link dates from May 2006. Would you
still have it somewhere and could I retrieve it somehow?

Thank you in advance,
Kind regards,
Elena

 

[Bob Quintal]

mHi Bob,

The link that you send me has a link to a BOM.mdb attached but it
does not work. I think the owner removed it as the link dates from
May 2006. Would you still have it somewhere and could I retrieve
it somehow?

Thank you in advance,
Kind regards,
Elena

The link I posted is to a message with code, The one david posted is
to a .mdb, it's http://www.mvps.org/access/modules/mdl0027.htm

The code is still on Google, I just copied it and pasted here for
you.

Paste into a module. (watch for line wrapping)

Option Compare Database
Option Explicit
Dim strSQL As String
Dim strSQL2 As String
Dim db As Database
Dim rsSource(99) As Recordset
Dim rsTarget As Recordset
Dim tblname As String
Dim Seqno As Long

'============================
Public Sub Explode(ByVal RootItem As String)
'============================
On Error GoTo Explode_Error
padded.
Seqno = 0
'--------------------------------------------
'Create a structure to receive the data
'--------------------------------------------
tblname = "XL" & RootItem

strSQL = "CREATE TABLE [" & tblname & "] (" _
& "Seqno long," & vbNewLine _
& "LLno integer," & vbNewLine _
& "Item text(38)," & vbNewLine _
& "Item_Name TEXT(64)," & vbNewLine _
& "Qty double," & vbNewLine _
& "UM text(6)," & vbNewLine _
& "Qty_Expl Double," & vbNewLine _
& "SeqNHA long," & vbNewLine _
& "CONSTRAINT seqno PRIMARY KEY (seqno)" & vbNewLine _
& ");"

DoCmd.RunSQL strSQL

Set db = CurrentDb
Set rsTarget = db.OpenRecordset(tblname)
'-------------------------------------------
' Set up source query
strSQL = "SELECT ProductStructure.Parent_ITEM, " & vbNewLine _
& "child_Items.ITEM_KEY," & vbNewLine _
& "child_Items.ITEM_NAME," & vbNewLine _
& "ProductStructure.Quantity," & vbNewLine _
& "ProductStructure.UM," & vbNewLine

strSQL = strSQL & "FROM ProductStructure INNER JOIN ITEM_Master
AS Child_Items " & vbNewLine _
& "ON (ProductStructure.child_ITEM = child_Items.ITEM_KEY)"
& vbNewLine

strSQL = strSQL & "WHERE (ProductStructure.Parent_ITEM) = '"

strSQL2 = "' ORDER BY Child_Items.ITEM_KEY;"

doOneRow RootItem, 0, 0, 1

Explode_exit:
rsTarget.Close
Set rsSource(9) = Nothing
Set rsTarget = Nothing
Set db = Nothing
Exit Sub

Explode_Error:
Select Case Err.Number
Case 3010 ' Table name exists
DoCmd.DeleteObject acTable, tblname
Resume
Case 3021 'no current record
Resume Next
Case Else
MsgBox "Please report this error to R Quintal" &
vbNewLine _
& Err.Number & " " & Err.Description, vbCritical
resume Explode_Exit
End Select
End Sub
'============================
Private Sub doOneRow(ByVal currentitem As String, ByVal LLno As
Long, ByVal SeqNHA As Variant, ByVal qtyNHA as double)
'============================
Dim vBkMark As Variant
Dim stCurrentRec As String
dim qtyExplode as double
Set rsSource(LLno) = db.OpenRecordset(strSQL & currentitem &
strSQL2, dbOpenDynaset)

Do Until rsSource(LLno).EOF
If rsSource(LLno).NoMatch Then
rsSource(LLno).Close
Exit Sub
Else
Seqno = Seqno + 1
QtyExplode = rsSource(LLno)!quantity * qtyNHA
With rsTarget
.AddNew
!Seqno = Seqno
!LLno = LLno
!Item = rsSource(LLno)!item_key
!item_name = rsSource(LLno)!item_name
!Qty = rsSource(LLno)!quantity
!UM = rsSource(LLno)!UM
!qty_expl = qtyExplode
!SeqNHA = SeqNHA
.Update
End With

stCurrentRec = rsSource(LLno)!item_key
vBkMark = rsSource(LLno).Bookmark

doOneRow stCurrentRec, LLno + 1, Seqno, qtyExplode

rsSource(LLno).Bookmark = vBkMark
rsSource(LLno).MoveNext
End If
Loop

End Sub

'================================== End of code

Modify as needed. Hope I didn't mangle too much while
transcribing.

Run from immediate window or code: Explode "Partno"

http://groups.google.com/group/comp.databases.ms-
access/browse_frm/thread/297c01f1d5257aef/818b9d061280179e?
lnk=gst&q=explosion+Quintal&rnum=2#818b9d061280179e

Is code that explodes a BOM from a Product Structure table
Parent_item, ChildItem, QtyPer, etc

And an Items Table
Item, Item_Name, UnitOfMeasure, etc.

 

[Guest]

Elena:

You might also like to take a look at:


http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=messages&webtag=ws-msdevapps&tid=23133


The file is attached to the second message in the thread (my reply to
Varda). It uses the common 'adjacency list' approach, where all parts and
assemblies are represented as rows in one Parts table and the key of this is
referenced by a PartStructure table as MajorPart and MinorPart columns.
Building a bill of materials from this requires recursive querying, which in
my demo is simulated by creating an external database and populating it at
runtime. The demo also includes another file which shows how the BoM can be
depicted graphically in a report, but this is limited to up to 9 levels,
mainly due to the page size, whereas the main BoM demo is theoretically
unlimited in the number of levels it would cope with; there would of course
be a practical limit, but I've not established what it would be.

You'll sometimes see Joe Celko's 'nested set' method recommended for
modelling a BoM. This is much more efficient in execution than the
'adjacency list' approach, but is only suitable for modelling a 'tree'
structure, which is a graph (in the mathematical sense of the term) where
there is only one path between any two nodes. Bills of materials would
rarely be a tree, however, as the same parts are almost certainly going to be
used in more than one assembly. More usually, therefore the structure is
what is known as a 'generalized directed graph', in which there can be more
than one path between two nodes. I seem to recall Celko suggesting that a
'nested set' approach might be used in such a circumstance by giving each
instance of a part in a structure a separate alias, thus making the structure
a true tree. So if part Widget is used in three separate assemblies they
might be distinguished in the table as Widget_001, Widget_002 and Widget_003.
I've never actually tried this myself, however.

Ken Sheridan
Stafford, England