Muliple Drop-Down Boxes in Access 2010 (1 Viewer)

[LadyScot101]

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. I'm thinking maybe I should change to VBA on the Account Code (3rd Drop-Down Box), since the usual Drop-Down rules don't seem to be working for it. 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!

 

[June7]

Suggest you provide db for analysis. Follow instructions at bottom of my post.

What do you mean by 'change to VBA'? You aren't already using VBA for any of these?

Cascading combobox is a common topic with many discussions and examples.

 

[MajP]

It gives me incorrect information or it won't clear from the second Box

One problem may be you need a requery in the combos 2 and 3 OnEnter events. If it is not clearing it likely needs to requery. It gets its rowsource on the first time you enter, but the next time there is nothing to force it to go back out in pull the updated rowsource.

 

[Pat Hartman]

You have a problem waiting to happen, You have changed this form so many times that your counter is up to - Combo461. You need to compact your database to clear this up. You might even need to export the form. Delete the existing one and reimport it.
Also, Please give controls rational names. Do you really know what each of those previous 461 combos do?

I've attached a database that shows how cascading combos work. But as MajP suggested, you probably need to simply requery the combos at the appropriate times.

 

[LadyScot101]

One problem may be you need a requery in the combos 2 and 3 OnEnter events. If it is not clearing it likely needs to requery. It gets its rowsource on the first time you enter, but the next time there is nothing to force it to go back out in pull the updated rowsource.

I have macRequery On Entry and that's not clearing it for some reason. It comes up with whatever was used in a previous entry.

You have a problem waiting to happen, You have changed this form so many times that your counter is up to - Combo461. You need to compact your database to clear this up. You might even need to export the form. Delete the existing one and reimport it.
Also, Please give controls rational names. Do you really know what each of those previous 461 combos do?

I've attached a database that shows how cascading combos work. But as MajP suggested, you probably need to simply requery the combos at the appropriate times.

I can get 2 drop-down boxes to work, it's when I add a 3rd drop-down to work off the results of the 2nd in relation to the 1st that I have problems and it won't clear the previous entered entry. I've tried the MacRequery in several places on the Properties and nothing seems to clear it. I'm using one table, but I'm thinking of splitting them into 2 Tables and having the Account Codes in a Table by themselves. I double that is going to make any difference, but I will give it a shot. I inherited this database from my Supervisor, so I apologize for all of the non-technical names. It is what it is. I know it drives some programmers batty. LOL! I'm used to it.

 

[LadyScot101]

Instead of doing it with the Row Source on the Data Tab, do you think I could just rewrite it in VBA to handle it all somehow? Thoughts?

 

[Pat Hartman]

Did you open the sample I posted and look at how it worked? It uses a different method than you are using. Your method should work if you implement it correctly so I'm not sure why it isn't. There is noting magic about a third level. You can have a 100 levels. Once you understand how to make level 2, level 3 and all the rest work EXACTLY the same way. Level 2 references level 1. Level 3 references level 2. Level 4 references level 3, etc.

 

[MajP]

Instead of doing it with the Row Source on the Data Tab, do you think I could just rewrite it in VBA to handle it all somehow?

What you are doing should theoretically work, but we do not know any specifics.
I prefer in VBA to resolve the sql String. So in the OnEnter event of the combobox I would do something like

dim strSql as string
strSql = "SELECT DISTINCT SubProgram, [Program Title] FROM tbl_BudgetPrograms WHERE [Program Title] = " & Me.Combo459
me.CboAccountCode.rowsource = strSql

However, there is no reason the way you did it should not work. I do the above because it is easier to error check.

 

[LadyScot101]

Did you open the sample I posted and look at how it worked? It uses a different method than you are using. Your method should work if you implement it correctly so I'm not sure why it isn't. There is noting magic about a third level. You can have a 100 levels. Once you understand how to make level 2, level 3 and all the rest work EXACTLY the same way. Level 2 references level 1. Level 3 references level 2. Level 4 references level 3, etc.

I tried to figure out the City, State Access that you attached and I can get 2 levels to work on my database, just like yours. It's that 3rd level that refuses to work after the first entry. How do you tie a 3rd level to levels 1 and 2? I tried requery and that didn't work. It didn't show that, since the example you gave only have 2 levels. Please advise. Thanks!

 

[Gasman]

PMFJI, but did you understand what Pat wrote. Level 3 depends on level 2 only.
Level 4 depends on level 3 only?

 

[LadyScot101]

PMFJI, but did you understand what Pat wrote. Level 3 depends on level 2 only.
Level 4 depends on level 3 only?

Yes. As an example, Janitorial in Level 2, should only have 1 answer in the drop-down for Level 3. It does the first time the user enters it and then when he goes to the next record it doesn't clear itself, it's the exact same answer, no matter what the user chooses for Level 2 on the next purchase. Then there is the problem that if you have a Null, which I put an * for the user to choose for Level 2, then technically it goes off of the Level 1 entry that is tied to the * for that Category. Such as Goats as Level 1. There is no SubProgram for it, so it's an *. Level 3 has 2 Account Codes tied to Goats and that's what should appear when the user chose Goats and *. If I can just get it to reset when going from one purchase to another, that would be a start. LOL! Then figure out the *s next. I'm trying not to get overwhelmed, but it's not easy!

 

[Pat Hartman]

Do you have the code to requery the "third" combo in the BeforeUpdate event of the "second" combo? As I said, the third, fourth, fifth, 100th work exactly the same way the second one does. Each depends ONLY on its parent combo.