Dependent Combo Box & Query

[Guest]

I have a form with 3 combo lists on it. I am trying to let someone pick say
- Division, Region & Market. The Region is dependent on the Division. The
Market is dependent on the region. The 3 combo boxes are called -
Combo_Division, Combo_Region, Combo_Market.

The code on the first combo drop down - Combo_Division:
SELECT DivisioinTbl.DIV_NUM AS DIV_NUM, DivisioinTbl.DIVISION FROM
DivisioinTbl;
The code on the second (dependent on Combo_Division ) drop down is
Combo_Region:
SELECT RegionTbl.region, RegionTbl.REG_NUM FROM RegionTbl WHERE
(((RegionTbl.REG_NUM)=Forms![3frm_Market]!Combo_Division));
The code on the third (dependent on Combo_Region) drop down - Combo_Market:
SELECT MarketTbl.market, MarketTbl.MKT_NUM FROM MarketTbl WHERE
(((MarketTbl.MKT_NUM)=Forms![3frm_Market]!Combo_Region));

The first two work great - I select the division - go to the combo box for
Region and the correct regions are listed - when I go to the third (Market)
which is dependent on Region - i get no results -

Secondly - if I can get these 3 combo boxes to work - I want to take the
results from the 3 boxes and use them in a query to generate information for
a report.

Any help would be much appreciated.

 

[Guest]

Hi Allen

I would expect the Bound Column of your combo lists to be 1 (the first).

This is the value of the selection (not necessarily what is displayed) and
it is also what needs to be in your WHERE clauses.

You have REG_NUM & MKT_NUM in your WHERE clauses but these columns are
second in your combo SELECT clauses.

Change the order of the fiields in your SELECT's to...

SELECT RegionTbl.REG_NUM, RegionTbl.region FROM RegionTbl WHERE
(((RegionTbl.REG_NUM)=Forms![3frm_Market]!Combo_Division));

SELECT MarketTbl.MKT_NUM, MarketTbl.market FROM MarketTbl WHERE
(((MarketTbl.MKT_NUM)=Forms![3frm_Market]!Combo_Region));


Regards

Andy Hull