Multi Layer BOM (1 Viewer)

[tl mike]

I have been looking and have found a couple db but one just shows the structure of how BOMs work and the other is an output db.

What I am trying to do is set up that if there are C x 3 pieces in part B, and 2 x pieces of B in part A what is the ultimate cost of A

Component----------SubAssembly------------Product
C x 3pcs $10 ------> B x 2pcs $30 ----------> A $60

I need to be able to look at a report and have it be able to show all three products and thier costs.

I have a Parts table that shows all the parts
tblPart

and a BOM table that shows the components the assembly and qty needed
tblBOM

and a qry that shows our current cost on each of components or if it is an assembly the total cost of all the components used in the assembly.
qryCost

 

[tl mike]

Any body??????????????????????????????????

 

[ByteMyzer]

Using the words Multi-Layer (or Multi-Level) BOM and query in the same sentence? You might as well be using the words honest and salesman in the same sentence.

You have been aquainted with how BOM structures work, but are you familiar with BOM costing models?

You will need a database with at least two tables: a table for the parts (Part Number, Cost, BOM-Level) and a table for the relationships (Parent Part #, Component Part #, Qty Per)

You will then need a function (time for some VBA here) that will enumerate the BOM structure to calculate your BOM-Level codes, then to calculate your cost roll-ups. From there you should have some usable data on which to report.

What is it that you are trying to accomplish? I have experience in BOM/Costing models; you can drop me a PM if you like.

 

[tl mike]

Attached is a sample of the db I amp working on

 

[ByteMyzer]

I've attached an updated copy of the file. I added a Cost field to the table tblPartNumber, and a Sub called BOM_Costroll(), which does the following:
* Loads the costing profile from tblCosting
* Loads the BOM Structure from tblBomDetail
* Calculates the Hierarchy Level Codes and perform the Cost Rollup in memory
* Updates the BOM and Cost in the tblPartNumber table

After running this Sub, you can perform a query on the tblPartNumber table to see the costs.

 

[tl mike]

I want to thank you for the help I have been testing it and see how it all works. The only problem I have is when I enter a new cost but date it before one that is already entered it uses the one that was entered last. Would a qry be a good way to get the most reacent date or would it be best by another line of code?

 

[tl mike]

What would be the best way/ time to execute this function? It would be nice to do when the Cost frm is closed it automatically runs but I am affraid of the system hanging up

 

[ByteMyzer]

I've attached an updated copy of the file that loads the costing profile based on the most recently entered, most currently dated record. See if this works better for you.

There should be no issue with executing the code when closing the Cost form. Give it a try, and if it doesn't work, drop me a line. I'm sure there is a way.

 

[tl mike]

Works like a charm!!!!

But you did burst my bubble I had made the changes to use a qry and it was working correctly. But I would rather not have a qry and do it all my code.

How would you set up the code to attach it on the close of the form??


I havent had to do anything like this before (obviously) I have mostly done small functions etc.

 

[ByteMyzer]

I would make a change to the Form_Close sub in frmPartNumberCosting:

Private Sub Form_Close()
On Error GoTo Err_Form_Close

    'Prompts the user to save the current record if it needs to be saved.
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You Can Not Close This Form Until You SAVE or UNDO Your Changes.", vbExclamation, "Save Required"
[b][i]    Else
        BOM_CostRoll[/i][/b]
    End If

Exit_Form_Close:
    Exit Sub

Err_Form_Close:
    MsgBox Err.Number, Err.Description
    Resume Exit_Form_Close
End If
End Sub

 

[tl mike]

Thanks a BUNCH!!!!!

 

[ByteMyzer]

You're very welcome. I'm glad I could help