Cascading Combo box
Zoe
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
<Z...@discussions.microsoft.com> wrote:
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
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
<Z...@discussions.microsoft.com> wrote:
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
Tom van Stiphout
<Z...@discussions.microsoft.com> wrote:
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
appreciate your advice and patience. Thanks so much.
Zoe
and got it to work.
Tom van Stiphout
<Z...@discussions.microsoft.com> wrote:
Yippee, that's great!
-Tom.
>Tom - thanks so very much for your assistance. I followed your instructions
>and got it to work.
>
<clip>