Cascade of 3 or more ComboBoxes

[tomullus]

Hi,

I'm having difficulty with making a cascade of more than 2 combo boxes. Let's say that my Database has a single table (let's call it TableX) containing the following columns: Category, Group, Subgroup, Information. I want to have 3 combo boxes; the first filtering the second and the second filtering the third.

The first combo box has the following rowsource:

SELECT DISTINCT TableX.Category FROM TableX;

and an after update event:

Private Sub Combo1_AfterUpdate()
Me!Combo2.Requery
End Sub

Now for the second Combobox:
SELECT DISTINCT TableX.Category, TableX.Group FROM TableX WHERE (((TableX.Category)=[Forms]![MyForm]![Combo1]));

Up to this point everything works as expected, the third combo box doesn't.
I've given it the following rowsource (also added an afterupdate event to combo2):

SELECT DISTINCT TableX.Category, TableX.Group, TableX.Subgroup
FROM TableX
WHERE (((TableX.Category)=[Forms]![MyForm]![Combo2]) AND ((TableX.Group)=[Forms]![MyForm]![Combo2]));

I don't know if I've made a mistake but the third combobox is always blank. The "AND ((TableX.Group)=[Forms]![MyForm]![Combo2]))" part seems to be the problem, because when i lebae the rowsource as:

SELECT DISTINCT TableX.Category, TableX.Group, TableX.Subgroup
FROM TableX
WHERE ((TableX.Category)=[Forms]![MyForm]![Combo2]);

the combobox works (but obviously doesn't filter how I need it to).

Any help would be greatly appreciated.

 

[John Big Booty]

First up welcome to the forum.

If your DB only has one table I suspect that your data may not be fully normalised, you will benefit from normalising your data, and this may well simplify your current problem.

Additionally as each combo depends on the one above it for it's data, you will need to force all combo boxes below your current combo box to requery when the current one is changed.

 

[r.harrison]

Hi, Just ran through your code quickly, Check the highlighted value.

Hi,

I'm having difficulty with making a cascade of more than 2 combo boxes. Let's say that my Database has a single table (let's call it TableX) containing the following columns: Category, Group, Subgroup, Information. I want to have 3 combo boxes; the first filtering the second and the second filtering the third.

The first combo box has the following rowsource:

SELECT DISTINCT TableX.Category FROM TableX;

and an after update event:

Private Sub Combo1_AfterUpdate()
Me!Combo2.Requery
End Sub

Now for the second Combobox:
SELECT DISTINCT TableX.Category, TableX.Group FROM TableX WHERE (((TableX.Category)=[Forms]![MyForm]![Combo1]));

Up to this point everything works as expected, the third combo box doesn't.
I've given it the following rowsource (also added an afterupdate event to combo2):

SELECT DISTINCT TableX.Category, TableX.Group, TableX.Subgroup
FROM TableX
WHERE (((TableX.Category)=[Forms]![MyForm]![Combo2]) AND ((TableX.Group)=[Forms]![MyForm]![Combo2]));

I don't know if I've made a mistake but the third combobox is always blank. The "AND ((TableX.Group)=[Forms]![MyForm]![Combo2]))" part seems to be the problem, because when i lebae the rowsource as:

SELECT DISTINCT TableX.Category, TableX.Group, TableX.Subgroup
FROM TableX
WHERE ((TableX.Category)=[Forms]![MyForm]![Combo2]);

the combobox works (but obviously doesn't filter how I need it to).

Any help would be greatly appreciated.

Should the value in red be Combo1?

 

[tomullus]

John Big Booty:
The reason why it's one big table is because the database is linked with an SQL server database. I don't want to create any relationships in the access database as that may cause complications, so my form is based on a view from SQL server with all my needed information.
Also I do requery the comboboxes with an afterupdate event.

r.harrison:
This doesn't cause any problems(except not working how i want it to):

SELECT DISTINCT TableX.Category, TableX.Group, TableX.Subgroup
FROM TableX
WHERE ((TableX.Category)=[Forms]![MyForm]![Combo1])

only when i add the "AND ((TableX.Group)=[Forms]![MyForm]![Combo2]));" part the combobox is blank when i click it.


Thanks for the reply guys



EDIT: Actually r.harrison, you were right. What you said along with fixing some columncount and boundcolumn fields solved the problem. Thanks a lot guys!