REFERENCING Form's instances controls

[LjushaMisha]

Here is an explanation of what the OP is attempting to do with ACCESS. It appears they are trying to track garment parts to be used to make garments, although I do not speak the language used in construction the tables and fields:
View attachment 120579
The OP has no relationships in the database between the two tables, so basically, they have two tables they are using like spreadsheets. The OP believes that some code they got off the internet will somehow make this work. It will not.

@LjushaMisha:
Given the description in the Readme table, you need to construct this project as a relational database which is what ACCESS was designed to be, based upon the requirements outlined in your Readme table included in the project. No code will help you do this.

Dear LarryE,
I have tried to make relations, but the only way was to make table for each LEVEL but I didn't find it "user friendly". That's why I have in "tblSestav" two fields (Ses_Nivo and Ses_Nadrejen_Id) - they actually work as connection between two tables. "Ses_Nivo" could be also called LEVEL and "Ses_Nadrejen_Id" could be called SUPERIOR PRODUCT ID.
From attached file to June7, you can see IT WORKS also in that way. Although it is maybe wrong from Access master view.

 

[MajP]

I would look at using a treeview for this. This is a self referencing form with multiple levels.
Click on the node you want and see it s details on the right, and details for its immediate children.

I discuss in detail working with hierarchical data and self referencing tables.
I would look at my BOM database to see how this works.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

To make this work I had to change the relationships as already mentioned.

So you need you table of Possible items which you have.
Then in your assembly table you need a unique ID for each item in the assembly, not just a product (artID) key.

I have tried to make relations, but the only way was to make table for each LEVEL but I didn't find it "user friendly". That's why I have in "tblSestav" two fields (Ses_Nivo and Ses_Nadrejen_Id) - they actually work as connection between two tables. "Ses_Nivo" could be also called LEVEL and "Ses_Nadrejen_Id" could be called SUPERIOR PRODUCT ID.

Using a properly designed self referencing table the table structure is extremely easy. Using a tree view the UI becomes extremely user friendly. If interested I can talk you through doing this. My code makes this extremely easy. If you want a detailed course.

Access Europe - Wed 7 Aug: Using Treeviews with Access Applications (Pete Poppe)

The next Access Europe User Group meeting will be on Wednesday 7 Aug 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome Pete Poppe who is very well known to Access World Forum members as MajP In this session, Pete will...

www.access-programmers.co.uk

at 24:29 I demo the bill of materials (systems and assemblies)

 

[LarryE]

Dear LarryE,
I have tried to make relations, but the only way was to make table for each LEVEL but I didn't find it "user friendly". That's why I have in "tblSestav" two fields (Ses_Nivo and Ses_Nadrejen_Id) - they actually work as connection between two tables. "Ses_Nivo" could be also called LEVEL and "Ses_Nadrejen_Id" could be called SUPERIOR PRODUCT ID.
From attached file to June7, you can see IT WORKS also in that way. Although it is maybe wrong from Access master view.

You still need to construct your project using the relational model.

1. Each product consists of multiple wardrobe parts
2. Each wardrobe part consists of multiple components

 

[MajP]

ou still need to construct your project using the relational model.

1. Each product consists of multiple wardrobe parts
2. Each wardrobe part consists of multiple component

I do not. Everything is a component and goes into a single self referencing table. If that component is an assembly then it has related children. But I never build a separate table for components. You can but it only complicates the data structure.

 

[Gasman]

I have tried to make relations, but the only way was to make table for each LEVEL

That is just one way. Another is to have a field that shows who the parent is and what level they are at.

 

[June7]

Assembly/manufacturing type database is probably most difficult to structure and manage (along with family tree) because of recursive nature of data relationships - common topic in forums.
I agree with other comments, you need a better understanding of relational database concepts before continuing your efforts.

 

[Pat Hartman]

That is just one way. Another is to have a field that shows who the parent is and what level they are at.

Technically parts in a part table have three types.
End Items (no parent part, may or may not have child parts)
Components (both parent and child parts)
Details (parent part but no child parts)

Because components can have more than one parent, I always use two tables for this schema. One to hold each unique part and the second holds the relationships.

 

[Mark_]

Depending on industry, you may have an end item that is also a component. Think "We make a bolt and nut for sale, plus a pair are included with a bracket as an item we sell".

 

[LjushaMisha]

Depending on industry, you may have an end item that is also a component. Think "We make a bolt and nut for sale, plus a pair are included with a bracket as an item we sell".

I think now a have made a change in this way.
I have a table like "Products" and "Relations".
On "Relations" I have made single referencing query consists of 3 tables - "original" plus one for Level and other for "superior product Id".
On searh level works great (no problem "drilling" level by level)
Now need to make stuff like ADD, EDIT, DELETE,...
Thanks

 

[LjushaMisha]

I think now a have made a change in this way.
I have a table like "Products" and "Relations".
On "Relations" I have made single referencing query consists of 3 tables - "original" plus one for Level and other for "superior product Id".
On searh level works great (no problem "drilling" level by level)
Now need to make stuff like ADD, EDIT, DELETE,...
Thanks

... SELF REFERENCING (not single)

 

[Mark_]

I think now a have made a change in this way.
I have a table like "Products" and "Relations".
On "Relations" I have made single referencing query consists of 3 tables - "original" plus one for Level and other for "superior product Id".
On searh level works great (no problem "drilling" level by level)
Now need to make stuff like ADD, EDIT, DELETE,...
Thanks

Only time I had to set this up I had to start with base items.
Relationship table then linked the base items into "assembly" units.
You could also use assemblies as base items to go up and make bigger items.

Only difference between a base item and an assembly is an assembly has child records that link it to child assemblies or base items. If no child records, it was the bottom item. Linking record also contained quantity.

This was about 30 years ago so I don't have an example handy.

 

[LjushaMisha]

Only time I had to set this up I had to start with base items.
Relationship table then linked the base items into "assembly" units.
You could also use assemblies as base items to go up and make bigger items.

Only difference between a base item and an assembly is an assembly has child records that link it to child assemblies or base items. If no child records, it was the bottom item. Linking record also contained quantity.

This was about 30 years ago so I don't have an example handy.

I hope my approach is the same as your was. Furniture maker starts with product "Wardrobe" (that is the product he well be doing, main product). He knows this product will consist of 2 "products" - upper or shelf part and lower or drawer part. (relation between this 3 products must be established). Than each part of the wardrobe will consist of other different products (entities). For example lower part will consist of 4 equal drawers. And each drawer will consist of front panel, back panel, left side, right side and bottom panel. And this same drawer can be entity of another, different wardrobe.
I think it is not in furniture maker mind to start with "drawer bottom panel" and construct the relation till the wardrobe. What do you think?

 

[Pat Hartman]

Depending on industry, you may have an end item that is also a component. Think "We make a bolt and nut for sale, plus a pair are included with a bracket as an item we sell".

An end item may also be a detail. Managing SKU's is yet another complication.

 

[LjushaMisha]

An end item may also be a detail. Managing SKU's is yet another complication.

Couldn't agree more with you.
One wardrobe has several SKU's, with end units and/or "part products".
Much harder than following "buy-stock-sell" oriented business
Anyway, that makes our life interesting.

 

[Mark_]

An end item may also be a detail. Managing SKU's is yet another complication.

Gets worse when you have serial numbers. Components 123-456-7890, 123-654-9876, and 987-654-3210 in cabinet 245-678-9956 gets to be great fun. As it had to meet Nevada gaming regulations, each component needed a complete history from manufacturer to assembly. Sad to say it required more oversite and documentation than tracking federally allocated funds!

 

[LjushaMisha]

Gets worse when you have serial numbers. Components 123-456-7890, 123-654-9876, and 987-654-3210 in cabinet 245-678-9956 gets to be great fun. As it had to meet Nevada gaming regulations, each component needed a complete history from manufacturer to assembly. Sad to say it required more oversite and documentation than tracking federally allocated funds!

.

 

[LarryE]

Gets worse when you have serial numbers. Components 123-456-7890, 123-654-9876, and 987-654-3210 in cabinet 245-678-9956 gets to be great fun. As it had to meet Nevada gaming regulations, each component needed a complete history from manufacturer to assembly. Sad to say it required more oversite and documentation than tracking federally allocated funds!

When in-stock items need to have unique serial numbers, in the past, I have just appended the Finished Inventory Primary Key number to the end of the product number as it was added to Finished Goods Inventory. It is always unique and makes each unit a separate product even if it was a duplicate product name and the component parts were the same. You can also append an underline symbol if you like.

 

[MajP]

Here is a pretty functional Wardrobe Builder.
Allows you to pull from the parts list and make any wardrobe composed of those parts. Basically I just added a couple of details to the generic Assemblies Sub Assembiles (Bill of Materials DB)

1. Create a Wardrobe by hitting the Create New BOM button. This just gives it a name for a specific wardrobe.
2. Start adding parts to create a wardrobe. choosing from the Parts list. If it is a top level part add it as a root node. If it is a sub assembly/part then add it as a child to the existing part .
3. Once you add a specific part to a specific BOM you can serialize the part in the parts details. If you click on a part the details appear in the part information table.
4. You can sort parts within levels using the up down arrows

You can add more functionality such as once you create a sub assembly from parts you could duplicate that sub assembly in the current Wardrobe bringing all the child parts and relations. You likely also want to save those assemblies so instead of adding parts and pieces you can add a complete assembly. Regardless this simple data structure supports those capabilities, they would just need to be added.

Think of the parts list or assembly list as a library that you pull from to make a specific Wardrobe (BOM). In the specific "wardrobe" you can serialize unique parts.

IMO there are very few real world scenarios where you can define multiple tables with multiple subforms for different levels. To say that this assembly will always be level 2 or level 3 and this part is always at level X is not realistic for any complex assembly. But furniture is simple, so maybe with a piece of furniture you can do it. This design supports any amount of sublevels and a part / sub assembly can be any viable levels.

Now there is probably another table needed to ensure that parts assemblies can only be allocated to a viable parent.

 

[Pat Hartman]

Gets worse when you have serial numbers. Components 123-456-7890, 123-654-9876, and 987-654-3210 in cabinet 245-678-9956 gets to be great fun. As it had to meet Nevada gaming regulations, each component needed a complete history from manufacturer to assembly. Sad to say it required more oversite and documentation than tracking federally allocated funds!

I designed an application for Boeing that managed the as designed, as planned, and as built configurations for the 747. Lots of interesting twists and turns.

 

[Mark_]

I designed an application for Boeing that managed the as designed, as planned, and as built configurations for the 747. Lots of interesting twists and turns.

I hope that included "How much each component should LEAK per flight hour" field!
I've worked with enough aviation mechanics to realize "If it doesn't leak, that's because its out of some vital fluid!"