Multi-level subform problem (1 Viewer)

[cursedeye]

I created a form that contains 1 subform ( lets call it lvl 1 subform)
in which I created another subform( lvl 2 subform).
lvl 1 subform acts like a child to the main form, works well, it
returns each equipment's corresponding component type.

lvl2 does the same, returns all component IDs that have the same
Component type, but with different equipment Id, (well,as I expected)
which is really annoying. What I want is, when I pick a equipment, it
only returns the components that are linked to both component type
(lvl1) and equipment id(main form), so it automatically tells me what
equipment type it is and what components the equipment has, (not what
other equipments have the same kind component).

My question is how do I link both(lvl1 and main) at the same time?





Table1:Equipment for Main form
Equipment id (pk)
Equipment
Equipment type id(fk)
......
......

Table2:Equipment Type for Main form
Equipment type id(pk)
Equipment type
......
......





Table for 1st lvl subform ( link to main: Equipment type id -- Equipment type id)

Component type id(pk)
Component type
Equipment type id(fk)
......
......




Table for 2nd lvl subform( link to 1st lvl subform : Component type id -- Component type id)

Component id(pk)
Component type id (fk)
Equipment id(fk)
Component Name
........

 

[jzwp22]

Welcome to AWF!

Before we get into your form issue, I am confused about your table structure:

Table1:Equipment
Equipment id (pk)
Equipment
Equipment type id(fk)

Table2:Equipment Type
Equipment type id(pk)
Equipment type

Table ?
Component type id(pk)
Component type
Equipment type id(fk)

Table ?
Component id(pk)
Component type id (fk)
Equipment id(fk)
Component Name

Regarding this table:
Table ?
Component type id(pk)
Component type
Equipment type id(fk)

Why would you define a component type with an equipment type?

Just from what you have provided it sounds like you have various components of various types and these components are used in equipment. The equipment can be of various types. I assume that there are multiple components associated with a piece of equipment which describes a one(equipment) to many (components) relationship. If the same component can be used for many pieces of equipment then that describes a many-to-many relationship. In order to model that you need a junction table.

First your base tables:

A table to hold the equipment:

tblEquipment
-pkEquipmentID primary key, autonumber
-txtEquipmentName
-fkEquipmentTypeID foreign key to tblEquipmentType

A table to hold the equipment types:

tblEquipmentType
-pkEquipmentTypeID primary key, autonumber
-txtEquipmentType


A table to hold the components:

tblComponents
-pkComponentID primary key, autonumber
-txtCommponentName
-fkComponentTypeID foreign key to tblComponentTypes



A table to hold the component types:
tblComponentTypes
-pkComponentTypeID primary key, autonumber
-txtComponentType

Now the junction table

tblEquipmentComponents
-pkEquipCompID primary key, autonumber
-fkEquipmentID foreign key to tblEquipment
-fkComponentID foreign key to tblComponents

If my assumptions about what you are trying to do are incorrect, please provide some more details about what you are trying to do with your database then will be better able to help you with it.

 

[cursedeye]

Thanks for the greeting and quick response!

What I'm trying to do here is to predefine a model. Equipment type and component type need to be built first, so after that when I defining a equipment as soon as I assign it's equipment type it knows associated components ( through equipment--equipment type-- component type--component--equipment) , so I don't have to enter components every time or missing any component.

For example, if I tell the system: in general a car ( "car" would be equipment type) has these component " tires, engine, seat, windshield...etc" (those would be my component type), so next time when I want to create a specific car, it doesn't matter what kind of car I want to create, Toyota Camry or Chvy Camaro or BMW z4 or whatever, when I assign "equipment type"(car) to it, it automatically know it has those component, although they have different manufacturer, size,etc.

The beauty is I only need to enter these information once, after that all I have to do is like verifying a checklist, and add some other info. such as manufacturer, size,etc.

Relationship please see http://www.mypicx.com/uploadimg/205928594_10072009_1.bmp

I'm not sure if my idea is correct, I'm open for any suggestions!

Thanks

 

[jzwp22]

OK, that helps but in one sense you are talking general components (a windshield, a engine etc.) but then you mention specific components

...those component, although they have different manufacturer, size,etc

. I'm not sure how that will work. I think at some point you will need to assign specific components to specific equipment and the structure I provided will allow you to do that. Now that does not say that you cannot have another group of tables that describe the "general" components for equipment. You can use the "general" build and compare your "specific" build to it to make sure the "specific" build has all of the necessary components called out in the "general" build. The general build will be essentially a template.

 

[cursedeye]

If you take a look at the screenshot I uploaded. Please

table "equipment" and "component" are connected with 1 to many relationship. thats for "specific"

Table "equipment type" and " component type" are connect with 1 to many as well, thats for "general"

Table "equipment typ" and "equipment" are also connect with 1to many relationship.

 

[jzwp22]

OK, my interpretation of type was different from yours. That being the case, then there should be no join between component type (general) to equipment (specific) since the equipment type in the equipment table already establishes that relationship.

Then for your form issue, the main form if based on the equipment table (specific) would link to a subform (lvl1) of specific components. But, your lvl2 form would not be a subform in the lvl1 subform (subsubform) but rather it would be a subform at the same level as the lvl1 form but just joined to the main form via the general equipment type ID. In other words you should have lvl1a and lvl1b subforms. To have a subsubform as the second level, it would be the many side of a one-to-many relationship with a subform record. In other words if a specific component is made up of several materials. THe materials would be in the subsubform.

 

[cursedeye]

OK, my interpretation of type was different from yours. That being the case, then there should be no join between component type (general) to equipment (specific) since the equipment type in the equipment table already establishes that relationship.

There was no join between component type (general) to equipment (specific).

so I guess my structure was correct.

This is what my form looks like

as you can see I put 2 tables "equipment" and "equipment type" in my main form. it contains all the info about each "specific" equipment, and plus one textbox "equpment type" so I can use it to link to my lvl1 subform" component type" (equipment type and component type are linked)

Datasheet view is applied in lvl1 subform.

My question was how do I set it up so it only returns the components that belong to the equipment in main form.

Actaully I like my layout, its very logical and clear, like I said before this is like a checklist, you just need to pick up a node fill out the rest info.

 

[jzwp22]

I've never seen it done that way before, pretty interesting. You might be able to link the main & subforms using both ID's (equipment and component type). Right click the frame of the subform & go to properties-->data tab & look for the Link Master Fields line and click to activate the ... Access should give you the option to link with multiple fields (see attachment). This will work only if both the ID's are in controls on the subform. Alternatively, you may be able to use a query instead of the table and use a reference to the main form's ID in the WHERE clause of the query

WHERE equipmentID=forms!mainformname!equipmentID

 

[cursedeye]

problem solved! works perfectly!

it's easy.
just set the level2 subform control's
LinkMasterFields to:
Forms![main form].[equipment id];[Component type id]
and the LinkChildFields property to:
[Equipment id];[Component type id]

I didn't know I can do this.

 

[cursedeye]

I've never seen it done that way before, pretty interesting. You might be able to link the main & subforms using both ID's (equipment and component type). Right click the frame of the subform & go to properties-->data tab & look for the Link Master Fields line and click to activate the ... Access should give you the option to link with multiple fields (see attachment). This will work only if both the ID's are in controls on the subform. Alternatively, you may be able to use a query instead of the table and use a reference to the main form's ID in the WHERE clause of the query

WHERE equipmentID=forms!mainformname!equipmentID

yep, got it done already. thanks for your help!

 

[jzwp22]

Glad you got it worked out. Good luck on your project.