3 linked drop down boxes

[penfold1992]

Is there a way in which i can link three drop down boxes together?
Ill create an example of what I mean...
Ill attach a Excel file which contains two identical filters, one of them where the filter is turned on.

If I select the "Serviced" filter, I am left with three options for "Make" and two options for "color".
If I select the "color" as "White" I am left with just one "Make" - "MG".

I understand that some sort of "hierarchy" needs to be in place so I will Make it so that "Serviced" Must be entered first, then "Color" then "Make" so that if you select a different "Service", the "Color" and "Make" will repopulate with the appropriate values.

This is certainly possible with a REDICULOUS amount of coding that would map each value to its corresponding values however is this possible some other (easier) way?

 

[pr2-eugin]

What you are looking for is Cascading ComboBoxes.. There are several examples available on here and the internet.. Just Google with these keywords, you should get several hits..

Does not involve a "lot" of coding..

 

[penfold1992]

so using my previous example I would want the "Serviced" result to filter the "Color"
they are in a table called: "Services"
so in the vba i would put:

Me.Colorcmb.RowSource = "SELECT [Services].Color, [Services].Serviced FROM [Services]  WHERE Serviced = " & Nz(Me.Servicedcmb) & " ORDER BY Color"

however this isnt working for me...

 

[pr2-eugin]

So you have put this is the AfterUpdate event of the Servicedcmd?

What do you exactly mean by "this isint working" - Error or Wrong Data or No data or WHAT is not working..

 

[penfold1992]

when I select "Color" I get a message saying "Enter Parameter Value" and it wants me to enter a value for "Yes"

 

[pr2-eugin]

I am confused, are you not trying to get the Color combo box filled after selecting the serviced combo? So why are you changing the Color?

What is the code associated with the Colocrcmb?

What are the values/row source of the Servicedcmb?

 

[penfold1992]

The values are either "No", "Planned" or "Completed"
so when I select "No" the "Color" combo box should contain all of the colors that also have the "Serviced" filled in as "No".

the source of this is in a table called "Services" and the column name is "Serviced"

the actual fields are called Servicedcmb and Colorcmb

 

[pr2-eugin]

Try this..

Me.Colorcmb.RowSource = "SELECT [Services].Color, [Services].Serviced FROM [Services]  WHERE Serviced = '" & Me.Servicedcmb & "' ORDER BY Color"

 

[penfold1992]

this worked as I wanted it to, thank you.

one query though... the "Color" is not a unique value, hence sometimes (if i select "no") I see two "Blacks" in the drop down.

is there any way in which I can remove the duplicates?

EDIT: replace "Select" with "Select DISTINCT"

 

[pr2-eugin]

Try GROUP BY.. It will eliminate duplicates.. Something like..

Me.Colorcmb.RowSource = "SELECT [Services].Color FROM [Services] WHERE Serviced = '" & Me.Servicedcmb & _
                        "' GROUP BY [Services].Color ORDER BY Color;"

 

[In Nomine Noctis]

Hi guys,

facing same problem, but there is a slight difference, I dont know to code in VBA.
Been watching all topics here about cascading combo boxes, but I am not able to do that in my dbase. :banghead:

Explanation:
Main form 01 tabMain with 2 subforms: 02 tabBarcode subform and 03 tabAnalysis subform

I need to: choose from Issue (02 tabBarcode subform) and afterwards automatically filter records in third subform in row Analysis Code by ID Issue from 05 tabIssue Matrix

ID Issue (05 tabIssue Matrix) = ID (07 tabList of Issues)

In words: just filter Analysis Code based on Issues

Attaching my dbase
Thanks in advance for any help with this !
INN