Question Dropdown list dependent on another dropdown list

[Bloues]

I have seen numerous tutorials on this issue on the net, but none of the really covers what I want to do.

I must create a database for the company that I work for that covers the maintenance history of our stone crusher plant. In this database I have two dropdown lists. The first one is for the equipment and the second one is for the different types of parts that has to be replaced or fixed.

Not all of the equipment uses all of the listed parts, but some parts are used on more than one type of equipment.

I have already created a database that lists all the equipment and another one that lists all the parts.

What I want to do now is create a Yes/No box for each type of equipment so I can mark which parts is used by which equipment.

Then you must be able to select the type of equipment from a dropdown list and then select from a dropdown list that only has the parts that is used by the piece of equipment.

I hope that my explanation is clear and that someone is able to help me.

I am using Access 2007

Regards

 

[Pat Hartman]

What you need is a junction table. This table will be used to define which parts are used on which piece of equipment.

tblEquipmentPartsXREF:
EquipmentID (PK field 1, FK to tblEquipment)
PartID (PK field 2, FK to tblParts)
Qty
ReplaceInterval

The above table simply defines the structure. It doesn't represent an instance of a machine with its parts. That is a different table. Each time you put a new piece of equipment into service, run an append query that copies the relevant rows from the tblEquipmentPartsXREF to the tblInstalledParts. The maintenance form should not allow the users to add or delete rows from this subform.

 

[Bloues]

Thanks

Please have a look at the following:

Date Machine Part Fault Description
03-Jun Jaw Crusher Coil Spring Worn out
03-Jun Screener 1 38mm Screen Replace nuts
04-Jun Screener 1 V-Belt Worn out
04-Jun Grizzely Breaker Coil Spring Worn out


In the example that I've posted above, the user can select the equipment from a list, then select the part name from a list. As I said, only the parts that is used by the equipment the user selected must appear on this list.

Obviously, I manage the parts list myself. I just want to know how to create this dropdown list. (The second one)

Will the junction table you suggested help with this?

 

[Bloues]

What I have done now is create a parts database where each part is listed in PartsID field.

Then I added a new field for each piece of equipment. The data type in these fields are Yes/No

I checked the boxes where the part occurs in the equipment. I then created a query for each type of equipment with two fields. PartsID & EqID. EqID is filtered so that only the rows where the box is ticked will be displayed.

I now want to know if there is a way that I can create a dropdown list that will be populated by the items in the query that relates to a specific piece of equipment after the equipment is selected from a dropdown list.

I hope that I am clear enough.

 

[Pat Hartman]

That is not the correct way to define which parts are used on each piece of equipment. You need the cross reference table I described where you will end up with a separate row for each part. That is the table you would use to populate the combo's RowSource.

What you are doing can't be used for anything without massive amounts of VBA coding.

Cascading combos review:
Current event of form:
Me.combo2.Requery
Me.combo3.Requery

AfterUpdate event of combo1
Me.Combo2.Requery
Me.Combo3.Requery

AfterUpdate event of combo2
Me.Combo3.Requery

RowSource query for combo2:
Select ... From ... Where somefield = Forms!yourformname!combo1
RowSource query for combo3:
Select ... From ... Where somefield = Forms!yourformname!combo2