Update Combo Box

[Leo]

Hi -

I have 3 combo boxes that reads 3 separate fields from one
table.

I am trying to figure out how to 'filter' the other 2
combo boxes to show only data relevant to what was
selected in the 1st box.

Please advise.
Thanks

 

[Ken Snell [MVP]]

See The ACCESS Web:
http://www.mvps.org/access/forms/frm0028.htm

 

[Leo]

Hi Ken -

Thanks.
However, I have 6 combo boxes in total and I want to be
able to pull all fields when any 1 combo box is left blank.

I know a Is NULL statement might work. But when I put more
than one 'Is NULL' statement in the 'OR' row of my design
query, my combo boxes go blank.

Please advise.

Thanks!

 

[Ken Snell [MVP]]

Excuse me for my confusion, but your original post said nothing about using
the combo boxes as query criteria? Can you provide more details about what
you're doing so that I can be sure I'm on track with you?

 

[Leo]

Hi Ken -

Ok. Here goes.

I have 6 combo boxes that read 6 different fields from 1
table (e.g. Region, Country, Department etc).
I am trying to 'filter' all other combo boxes whenever an
item is selected from any 1 (or 2 or 3 or 4) combo box. So
for instance, if 'Asia Pacific' Region is selected, I want
to see all countries and departments etc. in that region.
And if ONLY department and country is selected, I want to
see the region associated to that and so on. As you can
see there is an enormous number of possibilities since we
have 6 combo boxes.

So far, here is how I have it configured. For EACH COMBO
BOX I have:
- A 'AfterUpdate' code that runs 'Requery' on all the
other combo boxes (i.e. on the other 5 combo boxes)
whenever an item is picked from the drop-down menu
- Under 'Row Source' I have an SQL statement that Selects
a list of items based on fields (NULL or not) in the other
5 combo boxes.

This configuration is fine when I am just using 2 combo
boxes. But when I do the same thing for 3, my combo boxes
become blank unless an item is selected (NOT NULL) in the
other 2 combo boxes.

Have I confused you already?

Thanks!

 

[Ken Snell [MVP]]

I have puzzled over your description for a while (which is why I didn't post
a reply right away) and am not grasping why you would want to do this? I'm
trying to come to grips with this question because what you're doing appears
to be a circular process, and if I can figure out the use of these combo
boxes, then I think we can suggest an alternative and more useful approach.

Here's what I "seeing" in my mind right now:
-- you have a table that has a number of fields in it (region, company,
etc.).
-- you want the ability to select various combinations of the fields' values
in the different combo boxes.
-- you want each combo box to be filtered based on which values have been
selected in the other combo boxes (if any value has been selected at all).

Generically, the SQL statement for such a row source setup would look
something like this:

SELECT TableName.Field1, TableName.Field2, TableName.Field3,
TableName.Field4, TableName.Field5, TableName.Field6
FROM TableName
WHERE (((TableName.Field1)=[Forms]![FormName]![cboBox1] Or
[Forms]![FormName]![cboBox1] Is Null) AND
((TableName.Field2)=[Forms]![FormName]![cboBox2] Or
[Forms]![FormName]![cboBox2] Is Null) AND
((TableName.Field3)=[Forms]![FormName]![cboBox3] Or
[Forms]![FormName]![cboBox3] Is Null) AND
((TableName.Field4)=[Forms]![FormName]![cboBox4] Or
[Forms]![FormName]![cboBox4] Is Null) AND
((TableName.Field5)=[Forms]![FormName]![cboBox5] Or
[Forms]![FormName]![cboBox5] Is Null) AND
((TableName.Field6)=[Forms]![FormName]![cboBox6] Or
[Forms]![FormName]![cboBox6] Is Null));

I am hesitant to post this, as I'm not sure it's what you're seeking nor am
I sure it's really going to help your database become better. But, post back
with more details and findings.
--

Ken Snell
<MS ACCESS MVP>

 

[Leo]

Hi Ken -

Thanks for the details!
I think what you are 'seeing' is correct. I want the
combo boxes to list various field values that are
filtered based on which values have been selected in the
other combo boxes.

I tested the code you wrote below and it seems to work.
However, I think I may not be 'refreshing the combo
boxes' because when I try selecting various values in all
combo boxes, some of them do not get filtered.

Here is code i use for refreshing my combo boxes:

Private Sub cmb_Training_Req_Country_AfterUpdate()

Me!cmb_Training_Req_Region.Requery
Me!cmb_Training_Req_Division.Requery
Me!cmb_Training_Req_Director.Requery
Me!cmb_Training_Req_Function.Requery
Me!cmb_Training_Req_Student.Requery

End Sub

Is this right you think?

Thanks so much!

-----Original Message-----
I have puzzled over your description for a while (which is why I didn't post
a reply right away) and am not grasping why you would want to do this? I'm
trying to come to grips with this question because what you're doing appears
to be a circular process, and if I can figure out the use of these combo
boxes, then I think we can suggest an alternative and more useful approach.

Here's what I "seeing" in my mind right now:
-- you have a table that has a number of fields in it (region, company,
etc.).
-- you want the ability to select various combinations of the fields' values
in the different combo boxes.
-- you want each combo box to be filtered based on which values have been
selected in the other combo boxes (if any value has been selected at all).

Generically, the SQL statement for such a row source setup would look
something like this:

SELECT TableName.Field1, TableName.Field2, TableName.Field3,
TableName.Field4, TableName.Field5, TableName.Field6
FROM TableName
WHERE (((TableName.Field1)=[Forms]![FormName]![cboBox1] Or
[Forms]![FormName]![cboBox1] Is Null) AND
((TableName.Field2)=[Forms]![FormName]![cboBox2] Or
[Forms]![FormName]![cboBox2] Is Null) AND
((TableName.Field3)=[Forms]![FormName]![cboBox3] Or
[Forms]![FormName]![cboBox3] Is Null) AND
((TableName.Field4)=[Forms]![FormName]![cboBox4] Or
[Forms]![FormName]![cboBox4] Is Null) AND
((TableName.Field5)=[Forms]![FormName]![cboBox5] Or
[Forms]![FormName]![cboBox5] Is Null) AND
((TableName.Field6)=[Forms]![FormName]![cboBox6] Or
[Forms]![FormName]![cboBox6] Is Null));

I am hesitant to post this, as I'm not sure it's what you're seeking nor am
I sure it's really going to help your database become better. But, post back
with more details and findings.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken -

Ok. Here goes.

I have 6 combo boxes that read 6 different fields from 1
table (e.g. Region, Country, Department etc).
I am trying to 'filter' all other combo boxes whenever an
item is selected from any 1 (or 2 or 3 or 4) combo box. So
for instance, if 'Asia Pacific' Region is selected, I want
to see all countries and departments etc. in that region.
And if ONLY department and country is selected, I want to
see the region associated to that and so on. As you can
see there is an enormous number of possibilities since we
have 6 combo boxes.

So far, here is how I have it configured. For EACH COMBO
BOX I have:
- A 'AfterUpdate' code that runs 'Requery' on all the
other combo boxes (i.e. on the other 5 combo boxes)
whenever an item is picked from the drop-down menu
- Under 'Row Source' I have an SQL statement that Selects
a list of items based on fields (NULL or not) in the other
5 combo boxes.

This configuration is fine when I am just using 2 combo
boxes. But when I do the same thing for 3, my combo boxes
become blank unless an item is selected (NOT NULL) in the
other 2 combo boxes.

Have I confused you already?

Thanks!



said
nothing about using more
details about what put
more

.

 

[Leo]

Ken! Ignore below message. It's working now!!!!

Thanks so much!!!! have a good weekend!

-----Original Message-----
Hi Ken -

Thanks for the details!
I think what you are 'seeing' is correct. I want the
combo boxes to list various field values that are
filtered based on which values have been selected in the
other combo boxes.

I tested the code you wrote below and it seems to work.
However, I think I may not be 'refreshing the combo
boxes' because when I try selecting various values in all
combo boxes, some of them do not get filtered.

Here is code i use for refreshing my combo boxes:

Private Sub cmb_Training_Req_Country_AfterUpdate()

Me!cmb_Training_Req_Region.Requery
Me!cmb_Training_Req_Division.Requery
Me!cmb_Training_Req_Director.Requery
Me!cmb_Training_Req_Function.Requery
Me!cmb_Training_Req_Student.Requery

End Sub

Is this right you think?

Thanks so much!

-----Original Message-----
I have puzzled over your description for a while (which is why I didn't post
a reply right away) and am not grasping why you would want to do this? I'm
trying to come to grips with this question because what you're doing appears
to be a circular process, and if I can figure out the use of these combo
boxes, then I think we can suggest an alternative and more useful approach.

Here's what I "seeing" in my mind right now:
-- you have a table that has a number of fields in it (region, company,
etc.).
-- you want the ability to select various combinations of the fields' values
in the different combo boxes.
-- you want each combo box to be filtered based on

which
values have been

selected in the other combo boxes (if any value has

been
selected at all).

Generically, the SQL statement for such a row source setup would look
something like this:

SELECT TableName.Field1, TableName.Field2, TableName.Field3,
TableName.Field4, TableName.Field5, TableName.Field6
FROM TableName
WHERE (((TableName.Field1)=[Forms]![FormName]![cboBox1] Or
[Forms]![FormName]![cboBox1] Is Null) AND
((TableName.Field2)=[Forms]![FormName]![cboBox2] Or
[Forms]![FormName]![cboBox2] Is Null) AND
((TableName.Field3)=[Forms]![FormName]![cboBox3] Or
[Forms]![FormName]![cboBox3] Is Null) AND
((TableName.Field4)=[Forms]![FormName]![cboBox4] Or
[Forms]![FormName]![cboBox4] Is Null) AND
((TableName.Field5)=[Forms]![FormName]![cboBox5] Or
[Forms]![FormName]![cboBox5] Is Null) AND
((TableName.Field6)=[Forms]![FormName]![cboBox6] Or
[Forms]![FormName]![cboBox6] Is Null));

I am hesitant to post this, as I'm not sure it's what you're seeking nor am
I sure it's really going to help your database become better. But, post back
with more details and findings.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken -

Ok. Here goes.

I have 6 combo boxes that read 6 different fields

from

1

whenever

an

want

to

since

we


.

.