Cascading Combo Boxes (1 Viewer)

bellemmar · Dec 28, 2011

I'm really having trouble with these. I have them working in Excel; is it not possible to just import the formatting from Excel to have this work in Access tables/forms?

Alansidman · Dec 28, 2011

Here is a tutorial on how cascading combo boxes work in Access

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

bellemmar · Dec 29, 2011

Thank you. I have them working but there's just one thing I can't figure out. When I change the primary box (let's call it State) I want the subsequent box (let's call it City) to go blank. Can this be done?

Alansidman · Dec 29, 2011

bellemmar said:
Thank you. I have them working but there's just one thing I can't figure out. When I change the primary box (let's call it State) I want the subsequent box (let's call it City) to go blank. Can this be done?

In the afterupdate event for the State combo box put:

Code:

me.city.value=""
Me.requery

Alan

bellemmar · Apr 16, 2012

I'm trying to add a 3rd combo box but I can't seem to make it work. I have:
Box 1: Country
Box 2: Province
Box 3: City

Box 1 & 2 work with a code on box 1 after update requery box 2. But if i add that same code to box 2 to requery box 3, it doesn't work.

Alansidman · Apr 16, 2012

See if this helps

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

bellemmar · Apr 17, 2012

Still can only get 2 working, not all 3.

Pat Hartman · Apr 17, 2012

I don't like the example Alan referred you to. It separates the dependent lists into multiple tables which is just plain wrong in a relational database and makes the process more complicated than it needs to be. In summary:
The RowSource for each dependent combo should be a query with criteria that points to the "previous" combo for criteria.
Combo2
Select ... From ... Where something = Forms!yourform!combo1;
Combo3
Select ... From ... Where something = Forms!yourform!combo2;
etc.

Then in the AfterUpdate event of each "controlling" combo, clear and requery "dependent" combos.
AfterUpdate for combo1
Me.combo2 = Null
Me.combo3 = Null
...
Me.combo2.Requery
AfterUpdate for combo2
Me.combo3 = Null
..
Me.combo3.Requery

If you have more combos, just continue the pattern.

Alansidman · Apr 17, 2012

Pat;
I've always used the procedure I referred and not seen yours previously. Do you have a live example that you could post. I'm trying to get my head around your example and maybe its just the time of day or my senior mind not working, but I would like to try this out. Seems at first glance simpler. I will try out when I get home.

This is what I love about forums. Someone always has a better idea to learn from.

Thanks,
Alan

bellemmar · Apr 17, 2012

Okay now here's something bizarre. I have them working on the form. BUT when I try to make the form a subform in another form, they stop working and prompt me to enter a parameter value. I've checked & triple checked that the parameter value in the category query is referencing the right field in the right table. Frustrated!

Alansidman · Apr 17, 2012

perhaps it is time to post a copy of your database so that someone can look at it and see what is happening. Make sure to sanitize it for confidential material and run a compact and repair. Also suggest you save it as an .mdb file as many users still have not upgraded. Try to keep the volume of data limited so that it is not to big.

bellemmar · Apr 17, 2012

Thanks, I figured it out! My category query only referenced the main form & not the subform. It works now. Appreciate the help!

Pat Hartman · Apr 18, 2012

Here's a recent thread where I posted a sample.
http://www.access-programmers.co.uk/forums/showthread.php?t=223980&highlight=cascade

Alansidman · Apr 18, 2012

Thanks again Pat. Very nice presentation. You've convinced me to change.

Alan

Cascading Combo Boxes (1 Viewer)

bellemmar

Registered User.

Alansidman

AWF VIP

bellemmar

Registered User.

Alansidman

AWF VIP

bellemmar

Registered User.

Alansidman

AWF VIP

bellemmar

Registered User.

Pat Hartman

Super Moderator

Alansidman

AWF VIP

bellemmar

Registered User.

Alansidman

AWF VIP

bellemmar

Registered User.

Pat Hartman

Super Moderator

Alansidman

AWF VIP

Similar threads

Users who are viewing this thread