Cascading Combo Boxes

[Lou_Reed]

I am trying to do two cascading combo boxes. The second combo box is dependent on whatever is selected in the first combo box.



The first combo box choice is what classification the employee is say either Microsoft or Apple. Then, if and only if, the first choice is
Apple, here are some other additional choices to be made. Say the choices are corporate and contractor for Apple.

There are no choices for Microsoft. That is just noted that the employee works at Microsoft.

Thus the selection for the second combo box is dependent on the selection that is made in the first combo box.

Now when I created the first combo box, I used the wizard and I typed in the choices in my case I typed Microsoft or Apple. I know this can be done (or should be done) with a query.

I am just unsure where to put in the choices corporate or contractor.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[NTC]

The cascade is linear and the first combobox (C1) can have its record source be either a table/query or manually entered as you have done, it does not matter. In any case the user is going to select a value for C1.

All the remaining comboboxes must be based on a query. The query will call the criteria from the preceding combobox.

The query that is the record set fot C2 is going to have criteria: Forms!FormName.C1 so that its resulting record set only contains records with the C1 value

In the form that holds these comboboxes there is going to be a need to force a requery of the them to accommodate users that change their selections.

[Lou_Reed]

Yes, I understand the need to requery, but I do not see where the choices for the second combo box come from. It seems that they must also be typed in. They cannot be created by the query only selected

by the query. Selected from where?

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[orange]

See these for details
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

[John_G]

The choices for the second box do not have to come from a query or table - they can be a value list as well. You change the row source for combo2 in the after update event of combo1, something like this:

if me!combo1 = value1 then
  me!combo2.rowsource = "choice1;choice2;choice3"
else
  me!combo2.rowsource = "choice4;choice5;choice6;choice7"
endif
me!combo2.requery

Set the row source type to "value list" for the combo boxes.

[alansidman]

Here is another tutorial on cascading combo boxes.

http://www.fontstuff.com/access/acctut10pfv.htm

[Lou_Reed]

I did have one question in your example when one sets up the requery, one must requery which combo box : combo2?

It seems at odds with the videos that you gave me the links to.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[John_G]

Yes, combo2. Since the list in combo2 is the one which will change depending on the choice made in combo1, it is the one that is requeried.

[Lou_Reed]

I used the recommended code with my own personal modifications and strangely the first combo box offers me two choices either Apple or Microsoft.

But oddly after I select the one I want : Apple or Microsoft it does not let that show in the box instead I just get a blank. This then goes on to of course to mess up the responses to the second combo box, the dependent combo box whose response depends on what I put in the first. I am just totally confused. To create the first combo I used the wizard and there is almost no way to screw that up.

What could I possibly be doing wrong?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I think that I have got this one. I just forgot to connect the entry's control source in the form up to that in the table tblPersonnel. When I did that everything was fine.

Now in reference to post # 5 above the name combo1 and combo2 as shown:

if me!combo1 = value1 then
  me!combo2.rowsource = "choice1;choice2;choice3"
else
  me!combo2.rowsource = "choice4;choice5;choice6;choice7"
endif
me!combo2.requery

Are the names of the first and second box respectively. That is important because I must code that up properly.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[Lou_Reed]

Okay, now I have uploaded a zip version of my database. I put the source code which I believe is correct in the
event tab.

The Form in question is frmAddPersonnel. I put in the VBA code in event tab for the After_Update event and I know that there is a location on the data tab for the
row source. I did not use that. Is this where the problem lies?

It just seems that it belongs in the After_Update event on the event tab, not the Data row source.

Any help appreciated. Thanks in advance.

Respectfully,

Lou_Reed

NewZip3a.zip

[NTC]

Combobox 2 and above should have their data sit in tables, not a value list. Because you are going to query that table using combobox 1's criteria.

[John_G]

Your code is OK (almost), but you have it in the wrong place. You want to change the row source of combo2 whenever you change the selection in combo1, so you need to put it in the After Update event of combo1.

You also need to add a bit to the code by putting in what happens if the selection is not "gov" (your code needs to be fixed to put quotes around it). You need an "else" block in there.

A couple of other points -

First - did you ever compile your code? It has a number of compilation errors (most of which are easily fixed)

Second - Always, always, always use Option Explicit in any code module. You would be amazed at how many coding errors that catches.

You can make Option Explicit the default behaviour with an option on the VBA coding module.

From the menu, select Tools - Options. Click the "editor" tab and select the option "Require Variable Declaration"

[Lou_Reed]

Thanks for your reply. I have changed some of the code already. The If statement is not complete. It obviously requires an end if statement at the bottom of the code.
The action to take when the person is not government is no action. It only requires action when the choice is gov.

By simply writing the code out line by line is it not compiled? I thought that it would be. I could be mistaken.

The Option Explicit I thought was in here by default. It is always put in when you add code. I have programed in many other languages so I am familiar that it is best to define the variables instead of letting the software do it.

I am working on someone else's code and I assume (don't assume!) that it was there. I guess that I am wrong.

I am not sure where to put the code now? It seems it should go toevents tab to the After_Update event. I saw the row source and that was confusing.

So should I put the code?

Any help appreciated. Thanks in advance.


Respectfully,

Lou Reed

[Lou_Reed]

Everything works fine now except foe the line in the code:

[code]
me!combo2.rowsource = "choice1;choice2;choice3"
[code]

It hangs on that line stating that the record source does not exist. It was line I took in the example code verbatim, it is in post #5.

It seems to compile it just does not execute when the program gets to it.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed