Parallel? forked? combos

[bhammer]

Three combos. I want the selection in either of the first two (one or both)
to limit the list in not only the third, but also in each other.

what I have:
tblDivisions
DivID AutoNumber
Division text

tblCategories
CatID AutoNumber
Category text

tblIssues
IssueID AutoNumber
Div_ID foreign key
Cat_ID foreign key
IssueName

tblDivisions and tblCategories are indirectly related through the join
table, tblIssues. And tblIssues has the 'many' side of both relationships
with the other two tables.

The behavior I'm trying for is this:
User sees three combos on MainForm: cboDivison, cboCategory, cboIssue which
he can use in any order or combination to filter the subform datasheet of
AllIssues. Even with the 'forked' or parallel relationship of the tables, I
have this part working just fine. Amazing.

The problem is that I want the combos to communicate with each other to
limit their very long lists depending on the values of the other two show, or
not show (null).

So if two are blank, the other one displays a full list (show all). But if
one has a selection, the other two limit their lists to matching records
(ignoring the blank combo). And if two combos show a value, the third is
limited even more, accordingly.

Is this possible, given the relationships?

 

[Banana]

Have you already googled for "Cascading Comboboxes"? There are plenty of
examples of how to set up such thing, and though it's usually just two
comboboxes, it should be easy enough to expand to include the 3rd.

 

[bhammer]

I think I have a different problem. My three combos do not cascade. Instead
two are linked to the third. Division and Category are not related. An Issue
can have any Division and any Category--they are two separate properties if
Issue.

So cascading does not apply, I believe.

 

[Jeff Boyce]

"How" depends on "what" -- "what" data are you starting with?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bhammer]

My table defs are in the first post. Each Issue has an IssueDescription, a
Division and a Category. So with a huge list of Issues, the idea is for the
combo boxes to be used to filter for any of the three: Division, Category or
IssueDescription. The subform filters-out just fine to show the records that
have that Issue, or that Catagory, or that Division, or all three, or any two.

I'm looking for a way to "filter" the three combos using the typical Requery
method for cascading combos with cascading relationships, but this is not a
cascading relationship, but a forked one, and I'm having trouble.

 

[bhammer]

Well, I managed to get it to work. Ended up saving 12 verions of queries
(4-per combo) that handle the either-or case of blank or not blank for each
combo. Then the AfterUpdate event sets the RowSource in each combo to the
corresponding query after and If statement that checks each of the three
combos for null or not null.

Not pretty, but works.

 

[Guest]

hey

Well, I managed to get it to work. Ended up saving 12 verions of queries
(4-per combo) that handle the either-or case of blank or not blank for
each
combo. Then the AfterUpdate event sets the RowSource in each combo to the
corresponding query after and If statement that checks each of the three
combos for null or not null.

Not pretty, but works.