cascade combo box (1 Viewer)

yammers4 · Apr 2, 2012

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.

Pat Hartman · Apr 3, 2012

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.

YNWA · Sep 24, 2012

Pat Hartman said:
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?

Pat Hartman · Sep 26, 2012

No. Just make your own query with the necessary selection criteria.

mpicco-RCS · Oct 4, 2012

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?

boblarson · Oct 4, 2012

mpicco-RCS said:
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.

Pat Hartman · Oct 5, 2012

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

accessuser67 · Jan 13, 2013

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.

Pat Hartman · Jan 14, 2013

Welcome aboard

Did you try to use what I posted? Please post your own code including the procedure headers.

accessuser67 · Jan 14, 2013

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.

Alansidman · Jan 14, 2013

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

accessuser67 · Jan 15, 2013

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.

Alansidman · Jan 15, 2013

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.

accessuser67 · Jan 15, 2013

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.

Alansidman · Jan 15, 2013

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.

Pat Hartman · Jan 15, 2013

If the combo box is locked, the most likely reason (assuming you didn't do it deliberately) is the columns setting is incorrect. Open the Rowsource query and count the columns. Make sure you have accounted for all of them in the columns and column headings properties.

accessuser67 · Jan 15, 2013

I think maybe there is a glitch in the Microsoft Access 2010 program. I tried just about every feature of the combo box CategoryCombo and still it sticks on the first record of the drop down box.

Any suggestions would be greatly appreciated.

Regards.

boblarson · Jan 16, 2013

Okay

1. StoreID should be the PRIMARY KEY of your Stores table.

2. StoreID should be the field in the CategoryAisle table which ties it to the store, not the name.

3. Looks like the tables need a bit of work (redesign). I'm working on it to show you how but I have to go shopping now so it may be a little bit. I suggest not trying to "band-aid" this and actually structure the tables in a normalized fashion.

4. To go with the first couple of items - you should store TEXT in ONE place and store ID numbers for those values in the rest of the tables. For example, Store Name should be in one table ONLY and StoreID should be the identifier in other tables.

Pat Hartman · Jan 16, 2013

Did you actually count the columns in the RowSource and compare that to the number in the columns property?

accessuser67 · Jan 16, 2013

I have the second combo box CategoryCombo that sticks only on the first record on the drop down list. It doesnt allow me to select another record from the drop down.

I think it is a Microsoft Access glitch. Your suggestions will be greatly appreciated.

Regards.

cascade combo box (1 Viewer)

Registered User.

Attachments

Super Moderator

Attachments

Registered User.

Super Moderator

New member

Smeghead

Super Moderator

Registered User.

Super Moderator

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Super Moderator

Registered User.

Attachments

Smeghead

Super Moderator

Registered User.

Attachments

Similar threads

Users who are viewing this thread