Cascading Combo box

[Zoe]

I have 2 lookup tables - one for Cost Codes and the other for Expense Account
Numbers. I have two combo boxes on the same form - cbxCostCodes and
cbxExpenseAccountNumber. Depending on what the user selects in the first
combo box (cbxCostCodes), then I want the second combo box
(cbxExpenseAccountNumber) to list only those Expense Account Numbers that are
related to the Cost Code selected.

In addition - some of the Expense Account Numbers are associated with more
than one Cost Code. Right now - all I have set up is the lookup table for
Cost Codes and the lookup table for Expense Account Numbers. I am not sure
what steps to take next? Do I have to create another table that identifies
which expense accounts belong to which cost codes?

Thanks for any advice...

 

[Tom van Stiphout]

On Sat, 19 Jan 2008 15:23:01 -0800, Zoe

No. Rather you need to realize that there is a one-to-many relation
between your two tables. It is expressed by putting the CostCodeID in
the ExpenseAccountNumbers (EAN) table. Then in the Relationships
window create a relationship between these two tables, and enforce it.
The above assumes your CostCodes table (CC) has CostCodeID, CostCode
fields, and your EAN table has EANID, EAN, CostCodeID.

Once you have this in place, you create a query for cboEAN, that
"looks back" on cboCC, something like:
select * from EAN
where CostCodeID=Forms!YourFormName!cboCC

Last step is one line of VBA in the cboCC.AfterUpdate event:
cboEAN.Requery

-Tom.

 

[Zoe]

Thanks for your quick esponse. I totally follow what you are saying but my
one question is - there is more than one CostCodeID associated with each
ExpenseAccountID. The user is selecting the CostCode first. Then is selecting
the ExpenseAccountNumber.

I got the combo boxes to work correctly if I just place one CostCodeID in
the tlkpExpenseAcctNumber/CostCodeID field. But - for example - for
ExpenseAcctNumberID 1 there are 3 CostCodeIDs related to this (237, 239,
242). I can't place more than one CostCodeID in the ExpAcctNumber table? So
if the user selects either costcodeID 237 or 239 or 242 then 7021000 needs to
be a selection in the second dropdown.

This is what I have:
tlkpCostCode which contains the following fields:
CostCodeID, CostCode, CostCodeDescrpt

tlkpExpenseAcctNumber which contains the following fields:
ExpenseAcctNumberID, ExpenseAcctNumber, ExpenseAcctDescrpt, CostCodeID

So it looks something like this:
ExpenseAcctNumberID ExpenseAcctNumber ExpenseAcctDescrpt CostCodeID
1 7021000 Purchased
Services 75250

If I put one CostCodeID in the CostCodeID field in the tlkpExpenseAcctNumber
- I can get the two combo boxes to work correctly. But since some
ExpenseAcctNumbers have 2,3 sometimes 4 CostCodes associated with them - then
how do I handle that?

For example:
ExpAcctNumberID ExpenseAcctNumber ExpenseAcctDescrpt CostCodeID
1 7021000 Purchased Services
75250, 75610, 75950
2 7058000 Licences, Permits
75250, 75610
3 7260000 Supplies
75610, 75950
and so on.....

Bear with me here...I am not very good at this. Hope I explained my
situation ok.
Thanks!!

 

[Tom van Stiphout]

On Sat, 19 Jan 2008 16:57:00 -0800, Zoe

Originally you wrote:
Depending on what the user selects in the first combo box
(cbxCostCodes), then I want the second combo box
(cbxExpenseAccountNumber) to list only those Expense Account Numbers
that are related to the Cost Code selected.

Now you write:
there is more than one CostCodeID associated with each
ExpenseAccountID

If we combine these two statements, we can say two things:
Each CC can have several EANs
Each EAN can have several CCs
Can you confirm this? Then we can take the next step.

-Tom.

 

[Zoe]

Yes - that is correct. Thanks.

On Sat, 19 Jan 2008 16:57:00 -0800, Zoe

Originally you wrote:
Depending on what the user selects in the first combo box
(cbxCostCodes), then I want the second combo box
(cbxExpenseAccountNumber) to list only those Expense Account Numbers
that are related to the Cost Code selected.

Now you write:
there is more than one CostCodeID associated with each
ExpenseAccountID

If we combine these two statements, we can say two things:
Each CC can have several EANs
Each EAN can have several CCs
Can you confirm this? Then we can take the next step.

-Tom.

 

[Tom van Stiphout]

On Sat, 19 Jan 2008 19:56:01 -0800, Zoe

OK, so we have a classic many-to-many relationship. In the database it
is expressed with three tables: standard CC and EAN tables (without
CCID in the EAN table as I suggested earlier), and a third table, lets
call it CCforEAN. It has CCID and EANID, and its primary key is over
both fields. In the relationships window you draw relations between
all ID fields and enforce them.

Once the all-important correct db design is in place, we can focus on
a user interface. In your case you have a form with two dropdowns:
cboCC and cboEAN. Rowsources for each should be something like:
select CCID, CCDescripion
from CC
select EANID, EANDescription
from EAN inner join CCforEAN on EAN.EANID = CCforEAN.EANID
where CCforEAN.CCID = Forms!YourFormName!cboCC
In cboCC.AfterUpdate event write one line of VBA:
cboEAN.Requery

Later you'll have to build a UI to update the CCforEAN table as well.

-Tom.

 

[Zoe]

Got it. I will work on this later today and then let you know how it goes. I
appreciate your advice and patience. Thanks so much.

 

[Zoe]

Tom - thanks so very much for your assistance. I followed your instructions
and got it to work.

 

[Tom van Stiphout]

On Sun, 20 Jan 2008 13:26:01 -0800, Zoe

Yippee, that's great!
-Tom.

Tom - thanks so very much for your assistance. I followed your instructions
and got it to work.

<clip>