Bill Of Materials

[HealthyB1]

G'day
I am not sure where to log this request.
I have 3 tables.

• a table of customer orders
• A table of preconfigured Assemblies with a bill of materials (or Components)
• A table of components which can be ordered individually and they may also be part of an preconfigured assembly

What I want to do is when a client places an order I want to be able to select a preconfigured assembly and modify the contents and store it as an new order with the appropriate components and the new total cost for the modified assembly.
I do not want to modify the table containing the preconfigured list of assemblies. As 99% of the time I will use the initial preconfigured assembly without any modifications.

Finally from the customer order I want to produce a list of all components required to fill the order. (i.e. a total BOM at a component level).

Think of it as a new car dealer ordering 5 stock standard new cars fitted with a standard steering wheel. But one of the five cars requires a wooden steering wheel.
If I was at the factory I would need to order 5 sets of everything for these cars except for the steering wheel where I would need 4 standard steering wheels and one wooden steering wheel

Can someone please point me to an example of where this is done.
Thanks in advance.

 

[vbaInet]

Basically your table of preconfigured Assemblies is like a template from which you can tweak to meet the customer's specific needs?

That would be a matter of making a copy of that specific Assembly and making the necessary changes for that client. You would make a copy by firing an APPEND query which will use the preconfigured Assemblies table as it's source filtered down by the specific assembly.

Perhaps someone else has got an example that they could share.

 

[jdraw]

You may get some ideas from this example.
Let us know your progress.
Good luck with your project.

 

[vbaInet]

You may get some ideas from this example.
Let us know your progress.
Good luck with your project.

This would be useful in another thread too.

 

[TimW]

Hi
I deal with BOMs and this is how I handle them.....
Basically a BOM can contain other BOMs and a recursive method is required..i.e. calls itself.
This method enters data into a temp table that contains a parent material and its children

Sub BOMMethod(rst As DAO.Recordset, strAssembly As String, Optional Total As Integer)

' delare variables
    Dim tempTable As String
    Dim strCriteria As String
    Dim bk As String
    Dim tempRst As Recordset
    
    Dim SubTotal As Integer, check As Variant
On Error GoTo err_BOMMethod

strCriteria = BuildCriteria("Assembly", dbText, "'" & strAssembly & "'")


'do not accept a zero as a value

If Total = 0 Then
    Total = 1
ElseIf Total = Null Then
    Total = 1
End If


With rst
' find first record of dataset
    .FindFirst strCriteria
    
' traverse all records
    Do Until .NoMatch


    Set tempRst = dbs.OpenRecordset("tblTemp", dbOpenDynaset)
        With tempRst
                .AddNew
                !Assembly = rst![Assembly]
                !subAssembly = rst![subAssembly]
                !Quantity = rst![Quantity]
                !u_m = rst![u_m]
                !units = rst![units]
                !LevelTotal = rst![Quantity] * Total
                .update
        End With
    tempRst.Close
    Set tempRst = Nothing

'
        check = Nz(rst![Quantity], 0)
      
        SubTotal = check * Total
      
    ' save place in recordset
        bk = rst.Bookmark
       
    'Loops method to get a subassembly of a subassembly
        BOMMethod rst, rst!subAssembly, SubTotal
        
              
        ' return to last place to continue search
        rst.Bookmark = bk
        ' finds next record
        .FindNext strCriteria
        
    Loop ' do until .no match

    Exit Sub
End With ' .rst
Exit Sub
err_BOMMethod:
MsgBox "BOM error, Circular reference", vbOKOnly, "BOM error"
Call LogError(err.Number, err.Description, "BOMMethod()")
Exit Sub
End Sub

Perhaps this can help?

T

 

[HealthyB1]

Hi TimW,

Is it possible to give me an idea of what the fields are in your initial BOM file with some dummy data.
I am confused as what is the difference between the fields "Quantity" and "Units".

Also what is a "LevelTotal" I assume it is the number of records at this assembly or subassembly level.

Tracking through your code I believe that if the BOM file had a list for 10 identical but different coloured cars and each car had a BOM with a list of unique parts plus subassemblies with a BOM
Then your function would allow me to extract to a tmpTable all the components and subassemblies for say a RED car and ignore the rest of the other coloured cars. Is this correct?

I have attached a copy of the BOM file I am currently developing as a test. I'd like to try and map your function to it if possible.

Cheers,

 

[HealthyB1]

Basically your table of preconfigured Assemblies is like a template from which you can tweak to meet the customer's specific needs? Yes

That would be a matter of making a copy of that specific Assembly and making the necessary changes for that client. You would make a copy by firing an APPEND query which will use the preconfigured Assemblies table as it's source filtered down by the specific assembly.
Do I append this to the customer order file? At the present moment I add the preconfigured assembly name and its part number details to the customer order file to make for easier reading. See attachment 1 where I have selected an Assembly (Boiler Lazer) from the master assembly template file

Using VBA I can open the selected assembly details (Boiler Lazer) and edit same but I am opening the original assembly configuration from my master Assembly Template file. See Attachment 2. If I edit this then I will ruin my Master Assembly details
I really want to open a copy of the assembly which has be stored in the customer order file and modify as required for the job. i.e. Increase labour hours or hourly rate. I am unsure if I can store this as an assembly or need to break it down when it gets entered into the customer order file

Sorry for the questions but I am getting stuck on this and would appreciate some assistance if possible

 

[vbaInet]

It's not very easy reading underlined text HealthyB

I think TimW (or someone else who's done BOM) is best placed to give you better direction. The process of duplicating a template record is easy, but one has to understand the business logic and business model (i.e. table structures etc).

In my view, the basic idea is that the template will be a separate entity, customer order details will link to the template table (to enforce referential integrity as per the assembly line) and you copy all the relevant fields from the template to the order details table. This is only a basic view.

 

[HealthyB1]

It's not very easy reading underlined text HealthyB

I think TimW (or someone else who's done BOM) is best placed to give you better direction. The process of duplicating a template record is easy, but one has to understand the business logic and business model (i.e. table structures etc).

In my view, the basic idea is that the template will be a separate entity, customer order details will link to the template table (to enforce referential integrity as per the assembly line) and you copy all the relevant fields from the template to the order details table. This is only a basic view.

Apologies about the underlining.

Hmm sounds like you are right. To maintain referential integrity perhaps all Assemblies should be in the same file even if they are copies of a primary assembly that has some component like eg quantity, changed.
So perhaps I should make a copy of the primary assembly, modify it, append it to the master assembly file with a new Assembly number. I expect the number of master assemblies modified will be low anyway in the scheme of things.
Cheers,

 

[jdraw]

Can you post a copy of your current relationships window (jpg with tables expanded)?