Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 9 votes, 5.00 average. Display Modes
Old 04-02-2012, 02:32 PM   #1
yammers4
Newly Registered User
 
Join Date: Feb 2012
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
yammers4 is on a distinguished road
cascade combo box

Hi I wounder if anyone can help with this. a friend of mine manage to create a cascade combo box on the data entry form, it works when you select leage type smethwick or south staffs it then shows in the our team combo box the teams linked to that division, however I have tried to copy the design and use the same setup in the oposition drop down box but it does not work, could someone have a look for me please to get it to work.

i dealy I only want to selet the teams from the table labled leage teams but do not mind if two table for our teams and opposition teams have to be used. Its just this one thing then the databse is ready to go ready for the league that starts next week.
Attached Files
File Type: zip Langley B Player Stats and Averages.zip (475.6 KB, 551 views)

yammers4 is offline   Reply With Quote
Old 04-03-2012, 08:04 AM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: cascade combo box

For cascading combos to work, each combo uses a query as its RowSource and that query refers to the previous combo to filter the selected records. Then you use the AfterUpdate events of the combos to force subsequent combos to requery so their selection lists are updated.
AfterUpdate event for combo1:
Me.cboSecondCombo.Requery
Me.cboThirdcombo = null
Me.cboFourthcombo = null
AfterUpdate event for combo2:
Me.cboThirdCombo.Requery
Me.cboFourthCombo = Null
AfterUpdate event for combo3:
Me.cboFourthCombo.Requery

qry2:
Select ... From ... Where SomeField = Forms!yourform!cboFirstCombo;

qry3:
Select ... From ... Where SomeField = Forms!yourform!cboSecondCombo;

qry4:
Select ... From ... Where SomeField = Forms!yourform!cboThirdCombo;

The solution becomes more complex when you have a continuous form. I created a sample database a few years ago that explains the problem and demonstrates the solution that I use.
Attached Files
File Type: zip FixCascadingCombos.zip (80.8 KB, 1476 views)
__________________
Bridge Players Still Know All the Tricks

Last edited by Pat Hartman; 10-05-2012 at 08:56 AM. Reason: Change RecordSource to RowSource in first sentence.
Pat Hartman is offline   Reply With Quote
The Following 2 Users Say Thank You to Pat Hartman For This Useful Post:
Bdaviskar (11-24-2013), kelvin2088 (10-12-2014)
Old 09-24-2012, 12:48 AM   #3
YNWA
Newly Registered User
 
Join Date: Jun 2009
Location: Liverpool
Posts: 905
Thanks: 27
Thanked 10 Times in 10 Posts
YNWA is on a distinguished road
Re: cascade combo box

Quote:
Originally Posted by Pat Hartman View Post
For cascading combos to work, each combo uses a query as its RecordSource and that query refers to the previous combo to filter the selected records. Then you use the AfterUpdate events of the combos to force subsequent combos to requery so their selection lists are updated.
AfterUpdate event for combo1:
Me.cboSecondCombo.Requery
Me.cboThirdcombo = null
Me.cboFourthcombo = null
AfterUpdate event for combo2:
Me.cboThirdCombo.Requery
Me.cboFourthCombo = Null
AfterUpdate event for combo3:
Me.cboFourthCombo.Requery

qry2:
Select ... From ... Where SomeField = Forms!yourform!cboFirstCombo;

qry3:
Select ... From ... Where SomeField = Forms!yourform!cboSecondCombo;

qry4:
Select ... From ... Where SomeField = Forms!yourform!cboThirdCombo;

The solution becomes more complex when you have a continuous form. I created a sample database a few years ago that explains the problem and demonstrates the solution that I use.
Hi pat,

Are you familiar with the Northwind database?

I am looking at editing the Order form and putting in a combo to select a Company.

Then i want to do the cascade after update on this field, but then options in my second combo (Products) to filter based on company.

The Northwind db does not use a query on the products combo, is this a major problem?

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
from Designer Boutique
- Official UK Stockists of Armani Jeans, Creative Recreation Trainers, MA.STRUM Jackets, True Religion Jeans, Vivienne Westwood T-Shirts and more!
YNWA is offline   Reply With Quote
Old 09-25-2012, 06:27 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: cascade combo box

No. Just make your own query with the necessary selection criteria.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-04-2012, 01:49 PM   #5
mpicco-RCS
Newly Registered User
 
Join Date: Oct 2012
Location: Sierra Nevada foothills
Posts: 9
Thanks: 1
Thanked 0 Times in 0 Posts
mpicco-RCS is on a distinguished road
Re: cascade combo box

Under the Properties for one of my combo boxes, I see Row Source and Control Source. You refer to RecordSource in your post. Is this the same as the Row Source?
mpicco-RCS is offline   Reply With Quote
Old 10-04-2012, 02:05 PM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: cascade combo box

Quote:
Originally Posted by mpicco-RCS View Post
Under the Properties for one of my combo boxes, I see Row Source and Control Source. You refer to RecordSource in your post. Is this the same as the Row Source?
Record Source = The property of a form which is either a table name, query name, or Select statement to which that form is then considered "bound."

Row Source = The property of a combo box, list box and some other controls like chart, etc. which is a table name, query name, or Select statement. This is what lets it display the selections.

Control Source = The property of a control (including combo boxes, listboxes, text boxes, and more) which can be a field name from the form's record source or an expression like =Avg([FieldName]) for one example.

Hope that helps.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 10-05-2012, 08:57 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: cascade combo box

Thanks Bob. I edited my post to correct the mis-statement.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-13-2013, 12:42 PM   #8
accessuser67
Newly Registered User
 
Join Date: Dec 2012
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
accessuser67 is on a distinguished road
Re: cascade combo box

I have 2 combo box also and but when I put the second combo box to filter based on the first combo box I get a blank second combo box, no records to select. Do I have to use the request and got focus code to make it work.

It's just a simple 2 combo box where after I select record from the first combo I want that selection to filter my second combo box.

Please help.

Regards.
accessuser67 is offline   Reply With Quote
Old 01-14-2013, 11:46 AM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: cascade combo box

Welcome aboard
Did you try to use what I posted? Please post your own code including the procedure headers.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-14-2013, 02:41 PM   #10
accessuser67
Newly Registered User
 
Join Date: Dec 2012
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
accessuser67 is on a distinguished road
Re: cascade combo box

Somehow I got the combo boxes to work but now the second combo box is malfunctioning. Not sure if I'm suppose to write more code.

I have a form with two combo box cascading as it is known. The second combo box uses selection from the first combo box to filter the drop down list in the second combo box. It worked fine but now the second combo box just sticks on the first record in the drop down list when I try to select from the drop down list. Do I need to write more code for refreshingly the box. It refreshes it just doesn't allow me to select another record from the list.

Any suggestions on this would be appreciated.

Regards.
accessuser67 is offline   Reply With Quote
Old 01-14-2013, 03:23 PM   #11
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: cascade combo box

Suggest you post your code as Pat has asked. We are not mind readers here. We don't know what your code looks like unless you show us. Without that information how can we determine if you are missing anything in your code?

Alan
__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman is offline   Reply With Quote
Old 01-14-2013, 05:27 PM   #12
accessuser67
Newly Registered User
 
Join Date: Dec 2012
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
accessuser67 is on a distinguished road
Re: cascade combo box

SELECT CategoryAisle.Store, CategoryAisle.Category, CategoryAisle.Aisle FROM CategoryAisle WHERE (((CategoryAisle.Store)=[Forms]![GroceryF]![StoreCombo])) ORDER BY CategoryAisle.Category;

That is the code from the second combo box.

The first combo box code is SELECT [Stores].[StoreID], [Stores].[Store] FROM Stores ORDER BY [Store];

And a text box control source that relies on the second combo box is
=[CategoryCombo].[column](2)


Regards.
accessuser67 is offline   Reply With Quote
Old 01-14-2013, 05:55 PM   #13
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: cascade combo box

Did you write the afterupdate code as indicated in the earlier threads? You didn't include that in your last thread. Also, have you set the width of the combo box in the format to something other than zero width? It is possible that it is just not being displayed if you have not set the width in the properties for the combo box.
__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman is offline   Reply With Quote
Old 01-15-2013, 08:26 AM   #14
accessuser67
Newly Registered User
 
Join Date: Dec 2012
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
accessuser67 is on a distinguished road
Re: cascade combo box

Yes I have the after update me.categorycombo.requery.

But now the categorycombo box just sticks on the first record in the drop down list and it would not move to another selection in the drop down list.

This seems like a microsoft access glitch.

Please help.

Regards.
accessuser67 is offline   Reply With Quote
Old 01-15-2013, 10:15 AM   #15
Alansidman
AWF VIP
 
Alansidman's Avatar
 
Join Date: Jul 2008
Location: Lake County, IL
Posts: 1,493
Thanks: 40
Thanked 244 Times in 243 Posts
Alansidman will become famous soon enough
Send a message via Skype™ to Alansidman
Re: cascade combo box

I think it is time for you to provide a copy of your db for analysis. Dummy up any confidential information. Limit the size to a representative sample of data. Run a compact and repair before uploading. Click on the Go Advanced button and follow the wizard to upload.

__________________
Alan


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Win 7 Office XP, 2007, 2010
Alansidman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Bound cascade combo? cdoyle Forms 27 12-28-2007 10:34 AM
Cascade Combo - 3 tiers? fraser_lindsay Forms 3 10-14-2005 04:59 AM
Cascade Combo box mikevds Forms 0 04-29-2005 10:58 AM
[SOLVED] Cascade Combo Conundrum wizcow2 General 5 09-30-2003 07:10 PM
Cascade Combo Boxes ebartlett Forms 5 05-23-2002 05:00 AM




All times are GMT -8. The time now is 07:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World