This may give you a starting point. It is a slower method but you don't
have to worry about how deep your BOMs go. All the information is in a
table called tblBom and you can use that as a basis for a query to pull
in costs or whatever you're trying to associate with the BOM.
First create a table called tblBOM
db.Execute "CREATE TABLE tblBOM " _
" (idsBomID Long, strTopLevelPart Text(20), lngLevel Long,
strParentPartNum Text(20), " & _
"strParentRev Text (10), strMtlPartNum Text(20), strMtlRev
Text (10), dblQuantity Double, " & _
"dblParentQuantity Double, dblTotal Double)"
Then create a query called qryPartMaxRevDate
SELECT PUB_PartRev.PartNum, PUB_PartRev.RevisionNum,
Max(PUB_PartRev.EffectiveDate) AS MaxOfEffectiveDate,
PUB_PartRev.Approved, PUB_PartRev.Method
FROM PUB_PartRev
GROUP BY PUB_PartRev.PartNum, PUB_PartRev.RevisionNum,
PUB_PartRev.Approved, PUB_PartRev.Method
HAVING (((PUB_PartRev.Approved)=True));
This is the code that fills tblBOM with data
Public Sub FindBom(TopLevelPartNum As String, Level As Long,
ParentPartNum As String, _
ParentRev As String, ParentQuantity As Double, InTablename As
String)
On Error GoTo ErrProc
Dim db As Database
Dim rs As Recordset
Dim rsLog As Recordset
Set db = CurrentDb()
Set rsLog = db.OpenRecordset(InTablename)
UpdateStatus ("Expanding " & ParentPartNum)
If Level = 0 Then
rsLog.AddNew
rsLog![idsBomID] = GetNextID(InTablename, "idsBomId")
rsLog!strTopLevelPart = TopLevelPartNum
rsLog![lngLevel] = Level
rsLog![strParentPartNum] = Null
rsLog![strParentRev] = Null
rsLog![strMtlPartNum] = ParentPartNum
rsLog![strMtlRev] = ParentRev
rsLog![dblQuantity] = 1
rsLog![dblParentQuantity] = ParentQuantity
rsLog![dblTotal] = ParentQuantity
rsLog.Update
FindBom ParentPartNum, 1, ParentPartNum, ParentRev,
ParentQuantity, InTablename
Else
Set rs = db.OpenRecordset("SELECT qryPartMaxRevDate.PartNum,
qryPartMaxRevDate.RevisionNum AS ParentRev, " & _
"qryPartMaxRevDate.Method, PUB_PartMtl.MtlSeq,
PUB_PartMtl.QtyPer, PUB_PartMtl.MtlPartNum, " & _
"qryPartMaxRevDate_1.RevisionNum AS MtlRev,
qryPartMaxRevDate_1.Method AS MtlMethod " & _
"FROM ((qryPartMaxRevDate INNER JOIN PUB_PartMtl ON
(qryPartMaxRevDate.RevisionNum = " & _
"PUB_PartMtl.RevisionNum) AND (qryPartMaxRevDate.PartNum =
PUB_PartMtl.PartNum)) INNER JOIN " & _
"qryPartMaxRevDate AS qryPartMaxRevDate_1 ON
PUB_PartMtl.MtlPartNum = qryPartMaxRevDate_1.PartNum) " & _
"INNER JOIN qryPartLatestRevDate ON
(qryPartMaxRevDate_1.MaxOfEffectiveDate = " & _
"qryPartLatestRevDate.MaxOfEffectiveDate) AND
(qryPartMaxRevDate_1.PartNum = qryPartLatestRevDate.PartNum) " & _
"WHERE (qryPartMaxRevDate.PartNum = '" & ParentPartNum & "')
And " & _
"(qryPartMaxRevDate.RevisionNum = '" & ParentRev & "') " & _
"ORDER BY PUB_PartMtl.MtlSeq;")
While Not rs.EOF
rsLog.AddNew
rsLog![idsBomID] = GetNextID(InTablename, "idsBomId")
rsLog![lngLevel] = Level
rsLog![strTopLevelPart] = TopLevelPartNum
rsLog![strParentPartNum] = ParentPartNum
rsLog![strParentRev] = ParentRev
rsLog![strMtlPartNum] = rs![MtlPartNum]
rsLog![strMtlRev] = rs![MtlRev]
rsLog![dblQuantity] = rs![QtyPer]
rsLog![dblParentQuantity] = ParentQuantity
rsLog![dblTotal] = ParentQuantity * rs![QtyPer]
rsLog.Update
If rs!MtlMethod Then FindBom TopLevelPartNum, Level + 1,
rs!MtlPartNum, rs!MtlRev, ParentQuantity * rs!QtyPer, InTablename
rs.MoveNext
Wend
rs.Close
End If
rsLog.Close
db.Close
ExitProc:
Exit Sub
ErrProc:
MsgBox Err.Description
Resume ExitProc
End Sub
This is the main routine that will be called to start a BOM
Public Sub BomByPartNumber(InPartNumber As String, InRev As String,
InQty As Double, _
InTablename As String, InResetTable As Boolean)
On Error GoTo ErrProc
Dim db As Database
Dim rs As Recordset
If InResetTable Then ResetBomTable InTablename
Set db = CurrentDb()
'Verify the part number
Set rs = db.OpenRecordset("SELECT PartNum FROM Pub_PartRev " & _
"WHERE (PartNum ='" & InPartNumber & "') and (RevisionNum = '" &
InRev & "') and (Approved = True)")
If Not rs.EOF Then
FindBom rs![PartNum], 0, rs![PartNum], InRev, InQty, InTablename
Else
' MsgBox "Invalid part number, invalid revision or unapproved
revision."
Debug.Print InPartNumber & " - Invalid part"
End If
UpdateStatus ("")
ExitProc:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrProc:
MsgBox Err.Description
Resume ExitProc
End Sub
Hope this gets you started,
Butch
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jasper Recto
Sent: Wednesday, July 23, 2008 5:53 AM
To: Vantage Groups (vantage@yahoogroups.com)
Subject: [Vantage] Creating a BOM in MS access
I'm trying to create a BOM Costing report in Microsoft access so we can
do some 'What if' analysis.
I can generate the cost for any part at it's own level but I would like
to be able to drill down the BOM.
How can I create a BOM query in access so I can reference it from other
queries? In report builder, you have to link the part and partmtl tables
to each other a bunch of times to create a BOM but takes a lot to run
AND if I can't create the number of links to drill down the number of
levels our BOM's are.
Any suggestions on how I can do this in Access?
Thanks,
Jasper
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
have to worry about how deep your BOMs go. All the information is in a
table called tblBom and you can use that as a basis for a query to pull
in costs or whatever you're trying to associate with the BOM.
First create a table called tblBOM
db.Execute "CREATE TABLE tblBOM " _
" (idsBomID Long, strTopLevelPart Text(20), lngLevel Long,
strParentPartNum Text(20), " & _
"strParentRev Text (10), strMtlPartNum Text(20), strMtlRev
Text (10), dblQuantity Double, " & _
"dblParentQuantity Double, dblTotal Double)"
Then create a query called qryPartMaxRevDate
SELECT PUB_PartRev.PartNum, PUB_PartRev.RevisionNum,
Max(PUB_PartRev.EffectiveDate) AS MaxOfEffectiveDate,
PUB_PartRev.Approved, PUB_PartRev.Method
FROM PUB_PartRev
GROUP BY PUB_PartRev.PartNum, PUB_PartRev.RevisionNum,
PUB_PartRev.Approved, PUB_PartRev.Method
HAVING (((PUB_PartRev.Approved)=True));
This is the code that fills tblBOM with data
Public Sub FindBom(TopLevelPartNum As String, Level As Long,
ParentPartNum As String, _
ParentRev As String, ParentQuantity As Double, InTablename As
String)
On Error GoTo ErrProc
Dim db As Database
Dim rs As Recordset
Dim rsLog As Recordset
Set db = CurrentDb()
Set rsLog = db.OpenRecordset(InTablename)
UpdateStatus ("Expanding " & ParentPartNum)
If Level = 0 Then
rsLog.AddNew
rsLog![idsBomID] = GetNextID(InTablename, "idsBomId")
rsLog!strTopLevelPart = TopLevelPartNum
rsLog![lngLevel] = Level
rsLog![strParentPartNum] = Null
rsLog![strParentRev] = Null
rsLog![strMtlPartNum] = ParentPartNum
rsLog![strMtlRev] = ParentRev
rsLog![dblQuantity] = 1
rsLog![dblParentQuantity] = ParentQuantity
rsLog![dblTotal] = ParentQuantity
rsLog.Update
FindBom ParentPartNum, 1, ParentPartNum, ParentRev,
ParentQuantity, InTablename
Else
Set rs = db.OpenRecordset("SELECT qryPartMaxRevDate.PartNum,
qryPartMaxRevDate.RevisionNum AS ParentRev, " & _
"qryPartMaxRevDate.Method, PUB_PartMtl.MtlSeq,
PUB_PartMtl.QtyPer, PUB_PartMtl.MtlPartNum, " & _
"qryPartMaxRevDate_1.RevisionNum AS MtlRev,
qryPartMaxRevDate_1.Method AS MtlMethod " & _
"FROM ((qryPartMaxRevDate INNER JOIN PUB_PartMtl ON
(qryPartMaxRevDate.RevisionNum = " & _
"PUB_PartMtl.RevisionNum) AND (qryPartMaxRevDate.PartNum =
PUB_PartMtl.PartNum)) INNER JOIN " & _
"qryPartMaxRevDate AS qryPartMaxRevDate_1 ON
PUB_PartMtl.MtlPartNum = qryPartMaxRevDate_1.PartNum) " & _
"INNER JOIN qryPartLatestRevDate ON
(qryPartMaxRevDate_1.MaxOfEffectiveDate = " & _
"qryPartLatestRevDate.MaxOfEffectiveDate) AND
(qryPartMaxRevDate_1.PartNum = qryPartLatestRevDate.PartNum) " & _
"WHERE (qryPartMaxRevDate.PartNum = '" & ParentPartNum & "')
And " & _
"(qryPartMaxRevDate.RevisionNum = '" & ParentRev & "') " & _
"ORDER BY PUB_PartMtl.MtlSeq;")
While Not rs.EOF
rsLog.AddNew
rsLog![idsBomID] = GetNextID(InTablename, "idsBomId")
rsLog![lngLevel] = Level
rsLog![strTopLevelPart] = TopLevelPartNum
rsLog![strParentPartNum] = ParentPartNum
rsLog![strParentRev] = ParentRev
rsLog![strMtlPartNum] = rs![MtlPartNum]
rsLog![strMtlRev] = rs![MtlRev]
rsLog![dblQuantity] = rs![QtyPer]
rsLog![dblParentQuantity] = ParentQuantity
rsLog![dblTotal] = ParentQuantity * rs![QtyPer]
rsLog.Update
If rs!MtlMethod Then FindBom TopLevelPartNum, Level + 1,
rs!MtlPartNum, rs!MtlRev, ParentQuantity * rs!QtyPer, InTablename
rs.MoveNext
Wend
rs.Close
End If
rsLog.Close
db.Close
ExitProc:
Exit Sub
ErrProc:
MsgBox Err.Description
Resume ExitProc
End Sub
This is the main routine that will be called to start a BOM
Public Sub BomByPartNumber(InPartNumber As String, InRev As String,
InQty As Double, _
InTablename As String, InResetTable As Boolean)
On Error GoTo ErrProc
Dim db As Database
Dim rs As Recordset
If InResetTable Then ResetBomTable InTablename
Set db = CurrentDb()
'Verify the part number
Set rs = db.OpenRecordset("SELECT PartNum FROM Pub_PartRev " & _
"WHERE (PartNum ='" & InPartNumber & "') and (RevisionNum = '" &
InRev & "') and (Approved = True)")
If Not rs.EOF Then
FindBom rs![PartNum], 0, rs![PartNum], InRev, InQty, InTablename
Else
' MsgBox "Invalid part number, invalid revision or unapproved
revision."
Debug.Print InPartNumber & " - Invalid part"
End If
UpdateStatus ("")
ExitProc:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrProc:
MsgBox Err.Description
Resume ExitProc
End Sub
Hope this gets you started,
Butch
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jasper Recto
Sent: Wednesday, July 23, 2008 5:53 AM
To: Vantage Groups (vantage@yahoogroups.com)
Subject: [Vantage] Creating a BOM in MS access
I'm trying to create a BOM Costing report in Microsoft access so we can
do some 'What if' analysis.
I can generate the cost for any part at it's own level but I would like
to be able to drill down the BOM.
How can I create a BOM query in access so I can reference it from other
queries? In report builder, you have to link the part and partmtl tables
to each other a bunch of times to create a BOM but takes a lot to run
AND if I can't create the number of links to drill down the number of
levels our BOM's are.
Any suggestions on how I can do this in Access?
Thanks,
Jasper
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]