Multiple Drop Down Boxes that filter Answers in Access 2010

Question

In Access 2010, I have a SubForm that I have created a Table that has Drop Down Boxes that when the user choses from one box, it lists items in the second box that are only associated with the answer from the first Drop Down Box. Then I'm trying to get it to list from a 3rd Drop Down Box that are only associated with the answer from the second Drop Down Box. I got the second Drop Down to work, but can't get the 3rd to. It gives me incorrect information or it won't clear from the second Box if I change the first Drop Down Box to something different. Here is what I have so far:

The main table that the Drop Down Boxes gets their answers from is called tbl_BudgetPrograms. Here are some sample data:

Program: Administrative
SubPrograms: Office, Furniture, First Step Laborer, Records
Account Codes: If Office is chosen, the Drop Down Box would show: 53010001, 53010007, 52890001, 53890022

Another one is:
Program: Goat
SubProgram: * (only one to choose from in this SubProgram)
Account Codes: 53890022, 52890001

Here are the fields (Field Title - Properties Name) in the SubForm that I have created:
Program Title - Combo459
SubProgram - Combo461
Account Code - Account Code

Here is the code I have for the Program Title:
On the Data Tab on the Program Title Properties:
Control Source: Program Title
Row Source: SELECT DISTINCT tbl_BudgetPrograms.[Program Title] FROM tbl_BudgetPrograms;
Row Source Type: Table/Query

Here is the code I have for the SubProgram:
Control Source: SubProgram
Row Source: SELECT DISTINCT tbl_BudgetPrograms.SubProgram, tbl_BudgetPrograms.[Program Title] FROM tbl_BudgetPrograms WHERE (((tbl_BudgetPrograms.[Program Title])=[Forms]![Order Form]![Request Details].[Form]![Combo459]));
Row Source Type: Table/Query

Here is the code I have for the Account Code:
Control Source: SELECT DISTINCT tbl_BudgetPrograms.[Account Code], tbl_BudgetPrograms.[Account Description] FROM tbl_BudgetPrograms WHERE (((tbl_BudgetPrograms.[SubProgram])=[Forms]![Order Form]![Request Details].[Form]![Combo461]));
Row Source Type: Table/Query

So whatever is chosen in Program Title, it then gives the answers tied to the SubProgram, and then whatever is chosen in the SubProgram is shown in the Account Codes to choose from.

It shouldn't be a problem to take the answers from one Table and not 3 Tables, correct? I just can't get this to work. It should be easy. LOL!

Any help would be greatly appreciated!

Answer 1

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the section on 'entering data via a form/subforms' includes a contacts form in which there are three correlated combo boxes which allow the user to select a country, then region, then city. Note that only the city combo box is bound to a column in the Contacts table. This is because city determines region, which determines country, so to have columns for region and country would introduce redundancy and the table would not be normalised to Third Normal Form (3NF) with the consequent risk of update anomalies.

Note that the technique used to correlate the combo boxes will only work in single form view. In continuous forms view some controls will appear empty. No data is lost, merely hidden. The solution is illustrated in the next form in the demo, in which 'hybrid' controls are used, carefully superimposing a text box on a combo box to give the appearance of a single combo box control.

Answer 2

My Table has got all the fields in it. It's not split up between 3 Tables. Does it need to be? Isn't there a code I can put in it that will do it from one Table instead of breaking it all up into 3? I don't see that in your Access Sample.

Answer 3

My Table has got all the fields in it. It's not split up between 3 Tables. Does it need to be?

Yes, a fundamental principle of the database relational model is that a database models each entity type as a separate table, so your single referenced table should be decomposed into three referenced tables in a tree structured hierarchical structure:

BudgetPrograms
….BudgetProgram (PK)

SubPrograms
….SubProgram (PK)
….BudgetProgram (FK)

AccountCodes
….AccountCode (PK)
….SubProgram (FK)

PK = Primary key; FK = Foreign Key. Each table might have other non-key columns which represent other attributes of the entity type modelled by the table. Note that I've used 'natural' keys rather than autonumber 'surrogate' keys here. This makes the construction of correlated combo boxes easier, particularly in continuous forms view.

Your referencing table should then have only an AccountCode foreign key. The table is then normalized to Third Normal Form and contains no redundancies, thus protecting its integrity.

Your data entry form for the referencing table should contain one bound combo box, bound to AccountCode, and two unbound combo boxes for selecting the program and sub-program. These would be correlated as in my demo. The combo boxes would have code in their AfterUpdate event procedures, as in my demo. When navigating to a n existing record, code in the form's Current event procedure populates the unbound combo boxes. In my demo this calls GetRegion and GetCountry functions which you can replicate in your form's module.