Combo Box filter

[Hermione]

HI

I have 4 combo box in my form and I want to filter each combo box depending
what the user choose in the others combo box

My case is very compilcated, I hope you can help me:

Example:

Combo1
Combo2
Combo3
Combo4

If the user choose a value in Combo1 I have to filter combo2, combo3 and
combo4 and taking the value in combo 1 as criteria

When the user choose the combo 2 I have to filter combo3 and combo4 and
taking the value in combo 1 and combo2 as criteria

When the user choose the combo 3 I have to filter combo4 and taking the
value in combo 1 and combo2and combo3 as criteria

my problem is when the user choose the value of the 4 combo box, and after
he wants to change the value in one combo box, I loose all the information

Please help how can I do it:

For the combo box I use combo1.rowsource = MyQuery

Thanks

 

[Guest]

hi,
this is what i know as cascading combos.
http://www.mvps.org/access/forms/frm0028.htm
this site will explain it better. even has sample code.
good luck

 

[Hermione]

Hi

I know I already use the same code, but the problem is when I use to filter
with more than 2 combo box in my application I have more than 4 combo box, I
have to refine the values of each combo box depending on what the user choose
as value in one of the combo box

I know that it's complicated but the BOSS want it like that


thanks again

 

[Guest]

sorry,
I never had to do more than 2. repost and maybe a MVP will
pick it up.

 

[Marshall Barton]

I have 4 combo box in my form and I want to filter each combo box depending
what the user choose in the others combo box

My case is very compilcated, I hope you can help me:

Example:

Combo1
Combo2
Combo3
Combo4

If the user choose a value in Combo1 I have to filter combo2, combo3 and
combo4 and taking the value in combo 1 as criteria

When the user choose the combo 2 I have to filter combo3 and combo4 and
taking the value in combo 1 and combo2 as criteria

When the user choose the combo 3 I have to filter combo4 and taking the
value in combo 1 and combo2and combo3 as criteria

my problem is when the user choose the value of the 4 combo box, and after
he wants to change the value in one combo box, I loose all the information

Please help how can I do it:

For the combo box I use combo1.rowsource = MyQuery

In general, the standard approach for two combo boxes can be
extended to deal with many more.

With the usual table structure, the basic idea is to use a
query for each combo box with the foreign key field's
criteria set to the previous combo box. E.g. combo4's row
source query's criteria would be Forms!theform.combo3

The AfterUpdate event of each combo box then is used to set
all the succeeding combo to Null and then Requery the next
one. E.g. combo2's AfterUpdate event would look something
like:
Me.combo3 = Null
Me.combo4 = Null
Me.combo5 = Null
Me.combo3.Requery

As long as the bound column of each combo box is the primary
key in its row source's base table, that should be all you
need.

If you don't understand that logic or if your table
structure is not a chain of one-to-many relationships, then
you'll have to explain what you do have before we can begin
to straighten things out.

 

[Hermione]

Hi Marshall

Thanks for your answer;

I have the following tables:

Tb_Capacity: in relation (1 to many with (Tb_Dealer, Tb_Type, Tb_Dimension,
Tb_Form, Tb_Mesure)

The fields are:
ID_Capacity: Primary Key (PK)
ID_Dealer: Foreign Key (FK)
ID_Type: FK
ID_Dimension: FK
ID_Mesure: FK
ID_Form: FK
MAX_Capacity: a text value that I have to display in a text value

Tb_Dealer: ID_Dealer: PK ; Dealer: Text
Tb_Type: ID_Type: PK ; Type: Text
Tb_Dimension:ID_Dimension: PK ; Dimension: Text
Tb_Form: ID_Form: PK ; Form: Text
Tb_Mesure: ID_Mesure: PK ; Mesure: Text

In my form Frm_Capacity: I have 5 combo Box and one Text box

cbo_Dealer
cbo_Type
cbo_Dimension
cbo_Form
cbo_Mesure
txt_MAX_Capacity


What I want:
When the user choose a value in cbo_Dealer the
cbo_Type;cbo_Dimension;cbo_Form;cbo_Mesure will be filtered depending of the
value

after the user can choose a value in cbo_Dimension : so I have to filter the
following combo: cbo_Type;cbo_Form;cbo_Mesure with the value of
(cbo_Dimension and cbo_Dealer)

And so on...

until I'll display the right MAC_Capacity


I know that it's complicated, especcaly that the user can begin filterring
with any combo box

Thank you very much for your help, I'm working on that since one week I
think I'm not setting up correctly the combo box RowSource in the AfterUpdate
properties

Thanks again

 

[Marshall Barton]

I have the following tables:

Tb_Capacity: in relation (1 to many with (Tb_Dealer, Tb_Type, Tb_Dimension,
Tb_Form, Tb_Mesure)

The fields are:
ID_Capacity: Primary Key (PK)
ID_Dealer: Foreign Key (FK)
ID_Type: FK
ID_Dimension: FK
ID_Mesure: FK
ID_Form: FK
MAX_Capacity: a text value that I have to display in a text value

Tb_Dealer: ID_Dealer: PK ; Dealer: Text
Tb_Type: ID_Type: PK ; Type: Text
Tb_Dimension:ID_Dimension: PK ; Dimension: Text
Tb_Form: ID_Form: PK ; Form: Text
Tb_Mesure: ID_Mesure: PK ; Mesure: Text

In my form Frm_Capacity: I have 5 combo Box and one Text box

cbo_Dealer
cbo_Type
cbo_Dimension
cbo_Form
cbo_Mesure
txt_MAX_Capacity


What I want:
When the user choose a value in cbo_Dealer the
cbo_Type;cbo_Dimension;cbo_Form;cbo_Mesure will be filtered depending of the
value

after the user can choose a value in cbo_Dimension : so I have to filter the
following combo: cbo_Type;cbo_Form;cbo_Mesure with the value of
(cbo_Dimension and cbo_Dealer)

And so on...
until I'll display the right MAC_Capacity

I know that it's complicated, especcaly that the user can begin filterring
with any combo box

Gee, that's totally backwards from what I thought you
wanted. Here, you are trying to determine the value
associated with a record in a five way junction table, not,
as I first thought, trying to drill down through five layers
of one-to-many relations.

I ***think*** you need to set each combo's RowSource query
to something like this example for the Dealer combo box:

SELECT ID_Dealer, Dealer, MAX_Capacity
FROM Tb_Dealer
WHERE (ID_Type = Forms!Frm_Capacity.cbo_Type
OR Forms!Frm_Capacity.cbo_Type Is Null)
AND (ID_Dimension = Forms!Frm_Capacity.cbo_Dimension
OR Forms!Frm_Capacity.cbo_Dimension Is Null)
AND (ID_Form = Forms!Frm_Capacity.cbo_Form
OR Forms!Frm_Capacity.cbo_Form Is Null)
AND (ID_Mesure = Forms!Frm_Capacity.cbo_Mesure
OR Forms!Frm_Capacity.cbo_Mesure Is Null)

Then, the AfterUpdate events would have to requery any of
the combo boxes that haven't had a value selected yet and
get the value of the capacity when they're all filled in.

If IsNull(Me.cbo_Type) Then Me.cbo_Type.Requery
If IsNull(Me.cbo_Dimension) Then Me.cbo_Dimension.Requery
If IsNull(Me.cbo_Form) Then Me.cbo_Form.Requery
If IsNull(Me.cbo_Mesure) Then Me.cbo_Mesure.Requery

If Not (IsNull(Me.cbo_Type) Or IsNull(Me.cbo_Dimension) _
Or IsNull(Me.cbo_Form) Or IsNull(Me.cbo_Mesure)) Then
Me.txt_MAX_Capacity = Me.cbo_Dealer.Column(2)

This kind of combo box dependency can sometimes get caught
in a catch22 where a user made a mistake or want's to select
a value that isn't there. To help them restart the whole
process over again, I usually provide a button that clears
all the combo boxes instead of making them go back and clear
each one individully. The button's click event would just
be:

Me.cbo_Dealer = Null
Me.cbo_Type = Null
Me.cbo_Dimension = Null
Me.cbo_Form = Null
Me.cbo_Mesure = Null
Me.txt_MAX_Capacity = Null

It's kind of tedious to code and I hope you can keep all
that straight across all of them.

Good luck,

 

[Hermione]

Hi Marsh


Thanks for your help

As you said it'a a tedious code but I have to tryed my best

Thank you very much and I'll try your code

Hermione