Cascading Combo Box Questions

[jerryb123]

Hello, all:

I am trying to create a series of combo boxes in a table so that, when a
user selects a value in the first combo box, it limits the values that appear
in subsequent combo boxes.

The table where I am trying to limit user input is called "MA Table". In
that table, there are three text fields named "Sales Rep", "Agency" and
"Subagent". I want to create combo boxes to restrict those fields to values
that are stored in another table called "SALESREP_AGENT_SUBAGENT". In that
table, there are three text fields, again named "Sales Rep", "Agency" and
"Subagent". Each Sales Rep has multiple Agencies, and each Agency has
multiple Subagents. So when the user selects a Sales Rep, I want the "Agency"
combo box to only display the Agencies that belong to that Sales Rep. I have
tried placing various criteria in the Row Source section of the Agency combo
box, but it generally still displays all the "Agency" values from the
SALESREP_AGENT_SUBAGENT table, and not just the "Agency" values that belong
to the Sales Rep that was already selected. I have been beating my head
against the wall for a while on this, and I would really appreciate any help
I could get. Thanks!

 

[Jeff Boyce]

In MS Access, tables are "buckets o' data", and are intended to be used to
store data.

Comboboxes are controls that are best used on forms. Forms, in Access, are
used to display data (and for data entry).

Not only is what you are trying to do (if I understand it, you want
cascading comboboxes in tables) not possible, it's not a good idea.

Use forms.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Klatuu]

It takes two things to do this.
First, the second combo has to be filtered on the value of the first combo
and the third combo has to be filter on the value of the second combo.
Second, in the After Update event of the first combo, you have to requery
the second combo and in the After Update event of the second combo you have
to requery the third combo.

So the Agency combo row source need to be:
SELECT Agency FROM SALESREP_AGENT_SUBAGENT WHERE [Sales Rep] = cboSalesRep;

Then in the After Update event of cboSalesRep:
Me.cboAgency.Requery

Do the same with the sub agent combo.

 

[jerryb123]

First off, thanks for the post. I think you understand what I'm trying to do.

I changed the Agency Row Source to what you suggested, but I do not know how
to add the Requery in the After Update event of the Sales Rep Combo Box. So
right now the Agency Combo Box is blank. I'm assuming it will populate
correctly once I add the Requery, but I don't know where to add that code. (I
am just getting back into Access after a long stretch waiting tables. Some of
my skills are a little rusty!)

It takes two things to do this.
First, the second combo has to be filtered on the value of the first combo
and the third combo has to be filter on the value of the second combo.
Second, in the After Update event of the first combo, you have to requery
the second combo and in the After Update event of the second combo you have
to requery the third combo.

So the Agency combo row source need to be:
SELECT Agency FROM SALESREP_AGENT_SUBAGENT WHERE [Sales Rep] = cboSalesRep;

Then in the After Update event of cboSalesRep:
Me.cboAgency.Requery

Do the same with the sub agent combo.
--
Dave Hargis, Microsoft Access MVP

Hello, all:

I am trying to create a series of combo boxes in a table so that, when a
user selects a value in the first combo box, it limits the values that appear
in subsequent combo boxes.

The table where I am trying to limit user input is called "MA Table". In
that table, there are three text fields named "Sales Rep", "Agency" and
"Subagent". I want to create combo boxes to restrict those fields to values
that are stored in another table called "SALESREP_AGENT_SUBAGENT". In that
table, there are three text fields, again named "Sales Rep", "Agency" and
"Subagent". Each Sales Rep has multiple Agencies, and each Agency has
multiple Subagents. So when the user selects a Sales Rep, I want the "Agency"
combo box to only display the Agencies that belong to that Sales Rep. I have
tried placing various criteria in the Row Source section of the Agency combo
box, but it generally still displays all the "Agency" values from the
SALESREP_AGENT_SUBAGENT table, and not just the "Agency" values that belong
to the Sales Rep that was already selected. I have been beating my head
against the wall for a while on this, and I would really appreciate any help
I could get. Thanks!

 

[Klatuu]

Open your form in design view
Select the Sales Rep combo box
Open the Properties Dialog and select the Events tab
Click the small command button with the 3 dots just to the right of the text
box labeled After Update
Select CodeBuilder from the popup
The VB Editor will open
In the VB Editor enter
Me.cboAgent.Requery

cboAgent is just a name I made up. Use the actual name of the agency combo
box.
--
Dave Hargis, Microsoft Access MVP

First off, thanks for the post. I think you understand what I'm trying to do.

I changed the Agency Row Source to what you suggested, but I do not know how
to add the Requery in the After Update event of the Sales Rep Combo Box. So
right now the Agency Combo Box is blank. I'm assuming it will populate
correctly once I add the Requery, but I don't know where to add that code. (I
am just getting back into Access after a long stretch waiting tables. Some of
my skills are a little rusty!)

It takes two things to do this.
First, the second combo has to be filtered on the value of the first combo
and the third combo has to be filter on the value of the second combo.
Second, in the After Update event of the first combo, you have to requery
the second combo and in the After Update event of the second combo you have
to requery the third combo.

So the Agency combo row source need to be:
SELECT Agency FROM SALESREP_AGENT_SUBAGENT WHERE [Sales Rep] = cboSalesRep;

Then in the After Update event of cboSalesRep:
Me.cboAgency.Requery

Do the same with the sub agent combo.
--
Dave Hargis, Microsoft Access MVP

Hello, all:

I am trying to create a series of combo boxes in a table so that, when a
user selects a value in the first combo box, it limits the values that appear
in subsequent combo boxes.

The table where I am trying to limit user input is called "MA Table". In
that table, there are three text fields named "Sales Rep", "Agency" and
"Subagent". I want to create combo boxes to restrict those fields to values
that are stored in another table called "SALESREP_AGENT_SUBAGENT". In that
table, there are three text fields, again named "Sales Rep", "Agency" and
"Subagent". Each Sales Rep has multiple Agencies, and each Agency has
multiple Subagents. So when the user selects a Sales Rep, I want the "Agency"
combo box to only display the Agencies that belong to that Sales Rep. I have
tried placing various criteria in the Row Source section of the Agency combo
box, but it generally still displays all the "Agency" values from the
SALESREP_AGENT_SUBAGENT table, and not just the "Agency" values that belong
to the Sales Rep that was already selected. I have been beating my head
against the wall for a while on this, and I would really appreciate any help
I could get. Thanks!

 

[jerryb123]

Thanks for the pointers. I'm heading in the right direction, but I'm not
there yet.

I am sandboxing this out using a form which draws from a table with Address,
City and State fields. But my combo boxes are not refreshing themselves
correctly. When I select a State in my first combo box (cboState), then my
second combo box (cboCity) initially displays only the cities in that state,
which is what I want--so I will select "NY" in the state and it will give me
"NEW YORK" in the second combo box, but not "CHICAGO". However, if I go back
up to the first combo box and select "IL", then the second combo box still
gives me "NEW YORK" for a city instead of "CHICAGO".

Open your form in design view
Select the Sales Rep combo box
Open the Properties Dialog and select the Events tab
Click the small command button with the 3 dots just to the right of the text
box labeled After Update
Select CodeBuilder from the popup
The VB Editor will open
In the VB Editor enter
Me.cboAgent.Requery

cboAgent is just a name I made up. Use the actual name of the agency combo
box.
--
Dave Hargis, Microsoft Access MVP

First off, thanks for the post. I think you understand what I'm trying to do.

I changed the Agency Row Source to what you suggested, but I do not know how
to add the Requery in the After Update event of the Sales Rep Combo Box. So
right now the Agency Combo Box is blank. I'm assuming it will populate
correctly once I add the Requery, but I don't know where to add that code. (I
am just getting back into Access after a long stretch waiting tables. Some of
my skills are a little rusty!)

It takes two things to do this.
First, the second combo has to be filtered on the value of the first combo
and the third combo has to be filter on the value of the second combo.
Second, in the After Update event of the first combo, you have to requery
the second combo and in the After Update event of the second combo you have
to requery the third combo.

So the Agency combo row source need to be:
SELECT Agency FROM SALESREP_AGENT_SUBAGENT WHERE [Sales Rep] = cboSalesRep;

Then in the After Update event of cboSalesRep:
Me.cboAgency.Requery

Do the same with the sub agent combo.
--
Dave Hargis, Microsoft Access MVP


:

Hello, all:

I am trying to create a series of combo boxes in a table so that, when a
user selects a value in the first combo box, it limits the values that appear
in subsequent combo boxes.

The table where I am trying to limit user input is called "MA Table". In
that table, there are three text fields named "Sales Rep", "Agency" and
"Subagent". I want to create combo boxes to restrict those fields to values
that are stored in another table called "SALESREP_AGENT_SUBAGENT". In that
table, there are three text fields, again named "Sales Rep", "Agency" and
"Subagent". Each Sales Rep has multiple Agencies, and each Agency has
multiple Subagents. So when the user selects a Sales Rep, I want the "Agency"
combo box to only display the Agencies that belong to that Sales Rep. I have
tried placing various criteria in the Row Source section of the Agency combo
box, but it generally still displays all the "Agency" values from the
SALESREP_AGENT_SUBAGENT table, and not just the "Agency" values that belong
to the Sales Rep that was already selected. I have been beating my head
against the wall for a while on this, and I would really appreciate any help
I could get. Thanks!

 

[Ken Sheridan]

As well as requerying the second combo box in the AfterUpdate event procedure
of the first you also need to set the value of the second (and any combo
boxes below this in the hierarchy) to Null.

For a demo of correlated combo boxes using three tiers of geographical data
see:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

Thanks for the pointers. I'm heading in the right direction, but I'm not
there yet.

I am sandboxing this out using a form which draws from a table with Address,
City and State fields. But my combo boxes are not refreshing themselves
correctly. When I select a State in my first combo box (cboState), then my
second combo box (cboCity) initially displays only the cities in that state,
which is what I want--so I will select "NY" in the state and it will give me
"NEW YORK" in the second combo box, but not "CHICAGO". However, if I go back
up to the first combo box and select "IL", then the second combo box still
gives me "NEW YORK" for a city instead of "CHICAGO".

Open your form in design view
Select the Sales Rep combo box
Open the Properties Dialog and select the Events tab
Click the small command button with the 3 dots just to the right of the text
box labeled After Update
Select CodeBuilder from the popup
The VB Editor will open
In the VB Editor enter
Me.cboAgent.Requery

cboAgent is just a name I made up. Use the actual name of the agency combo
box.
--
Dave Hargis, Microsoft Access MVP

First off, thanks for the post. I think you understand what I'm trying to do.

I changed the Agency Row Source to what you suggested, but I do not know how
to add the Requery in the After Update event of the Sales Rep Combo Box. So
right now the Agency Combo Box is blank. I'm assuming it will populate
correctly once I add the Requery, but I don't know where to add that code. (I
am just getting back into Access after a long stretch waiting tables. Some of
my skills are a little rusty!)

:

It takes two things to do this.
First, the second combo has to be filtered on the value of the first combo
and the third combo has to be filter on the value of the second combo.
Second, in the After Update event of the first combo, you have to requery
the second combo and in the After Update event of the second combo you have
to requery the third combo.

So the Agency combo row source need to be:
SELECT Agency FROM SALESREP_AGENT_SUBAGENT WHERE [Sales Rep] = cboSalesRep;

Then in the After Update event of cboSalesRep:
Me.cboAgency.Requery

Do the same with the sub agent combo.
--
Dave Hargis, Microsoft Access MVP


:

Hello, all:

I am trying to create a series of combo boxes in a table so that, when a
user selects a value in the first combo box, it limits the values that appear
in subsequent combo boxes.

The table where I am trying to limit user input is called "MA Table". In
that table, there are three text fields named "Sales Rep", "Agency" and
"Subagent". I want to create combo boxes to restrict those fields to values
that are stored in another table called "SALESREP_AGENT_SUBAGENT". In that
table, there are three text fields, again named "Sales Rep", "Agency" and
"Subagent". Each Sales Rep has multiple Agencies, and each Agency has
multiple Subagents. So when the user selects a Sales Rep, I want the "Agency"
combo box to only display the Agencies that belong to that Sales Rep. I have
tried placing various criteria in the Row Source section of the Agency combo
box, but it generally still displays all the "Agency" values from the
SALESREP_AGENT_SUBAGENT table, and not just the "Agency" values that belong
to the Sales Rep that was already selected. I have been beating my head
against the wall for a while on this, and I would really appreciate any help
I could get. Thanks!

 

[jerryb123]

That was a tremendous help! After poking around in your code, I got
everything to work properly--I had been nulling out the previous fields
instead of the later fields in the cascading data.

As well as requerying the second combo box in the AfterUpdate event procedure
of the first you also need to set the value of the second (and any combo
boxes below this in the hierarchy) to Null.

For a demo of correlated combo boxes using three tiers of geographical data
see:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

Thanks for the pointers. I'm heading in the right direction, but I'm not
there yet.

I am sandboxing this out using a form which draws from a table with Address,
City and State fields. But my combo boxes are not refreshing themselves
correctly. When I select a State in my first combo box (cboState), then my
second combo box (cboCity) initially displays only the cities in that state,
which is what I want--so I will select "NY" in the state and it will give me
"NEW YORK" in the second combo box, but not "CHICAGO". However, if I go back
up to the first combo box and select "IL", then the second combo box still
gives me "NEW YORK" for a city instead of "CHICAGO".

Open your form in design view
Select the Sales Rep combo box
Open the Properties Dialog and select the Events tab
Click the small command button with the 3 dots just to the right of the text
box labeled After Update
Select CodeBuilder from the popup
The VB Editor will open
In the VB Editor enter
Me.cboAgent.Requery

cboAgent is just a name I made up. Use the actual name of the agency combo
box.
--
Dave Hargis, Microsoft Access MVP


:

First off, thanks for the post. I think you understand what I'm trying to do.

I changed the Agency Row Source to what you suggested, but I do not know how
to add the Requery in the After Update event of the Sales Rep Combo Box. So
right now the Agency Combo Box is blank. I'm assuming it will populate
correctly once I add the Requery, but I don't know where to add that code. (I
am just getting back into Access after a long stretch waiting tables. Some of
my skills are a little rusty!)

:

It takes two things to do this.
First, the second combo has to be filtered on the value of the first combo
and the third combo has to be filter on the value of the second combo.
Second, in the After Update event of the first combo, you have to requery
the second combo and in the After Update event of the second combo you have
to requery the third combo.

So the Agency combo row source need to be:
SELECT Agency FROM SALESREP_AGENT_SUBAGENT WHERE [Sales Rep] = cboSalesRep;

Then in the After Update event of cboSalesRep:
Me.cboAgency.Requery

Do the same with the sub agent combo.
--
Dave Hargis, Microsoft Access MVP


:

Hello, all:

I am trying to create a series of combo boxes in a table so that, when a
user selects a value in the first combo box, it limits the values that appear
in subsequent combo boxes.

The table where I am trying to limit user input is called "MA Table". In
that table, there are three text fields named "Sales Rep", "Agency" and
"Subagent". I want to create combo boxes to restrict those fields to values
that are stored in another table called "SALESREP_AGENT_SUBAGENT". In that
table, there are three text fields, again named "Sales Rep", "Agency" and
"Subagent". Each Sales Rep has multiple Agencies, and each Agency has
multiple Subagents. So when the user selects a Sales Rep, I want the "Agency"
combo box to only display the Agencies that belong to that Sales Rep. I have
tried placing various criteria in the Row Source section of the Agency combo
box, but it generally still displays all the "Agency" values from the
SALESREP_AGENT_SUBAGENT table, and not just the "Agency" values that belong
to the Sales Rep that was already selected. I have been beating my head
against the wall for a while on this, and I would really appreciate any help
I could get. Thanks!