 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / January 2008Cascading Combo box
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... 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. >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 [quoted text clipped - 10 lines] > >Thanks for any advice... 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!! > 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 [quoted text clipped - 27 lines] > > > >Thanks for any advice... 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. >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 [quoted text clipped - 70 lines] >> > >> >Thanks for any advice... Yes - that is correct. Thanks. > Originally you wrote: > Depending on what the user selects in the first combo box [quoted text clipped - 87 lines] > >> > > >> >Thanks for any advice... 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. >Yes - that is correct. Thanks. > [quoted text clipped - 89 lines] >> >> > >> >> >Thanks for any advice... 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. > 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 [quoted text clipped - 111 lines] > >> >> > > >> >> >Thanks for any advice... Tom - thanks so very much for your assistance. I followed your instructions and got it to work. > 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 [quoted text clipped - 111 lines] > >> >> > > >> >> >Thanks for any advice... Yippee, that's great! -Tom. >Tom - thanks so very much for your assistance. I followed your instructions >and got it to work. <clip> |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.