none
Cascading Combo Boxes RRS feed

  • Question

  • Hi

    I'm struggling to get a complicated set of cascading combo boxes to work.  I've managed to do simple ones but this is a little more difficult.

    I have a table called tblVariants which establishes the vehicle variant availability but when setting the criteria in my query to relate to another field on the form as I have done with previous cascading combo boxes, it is not fetching the available data.

    Can cascading combo boxes work when using a table whose fields allow multiple entries from other tables?

    Thanks

    Chris

    Monday, November 23, 2015 11:34 AM

Answers

  • Now I'm stuck!!

    If you're willing to email me a copy of your db, I could take a look. Just a thought...
    • Marked as answer by ChrisParkin Monday, November 30, 2015 10:56 AM
    Wednesday, November 25, 2015 6:13 PM
  • I think you may have misunderstood the nature of the relationship type.  There is not a set of one-to-many relationship types from each table to the next as there would be in a purely hierarchical model, my ComboDemo file being an example, where the model is:

    Counties---<Districts----<Parishes

    What you have is an octonary relationship type, an 8-way many-to-many relationship type.  In effect this is what your current table models, but using multi-valued fields rather than multiple rows. I referred in my earlier reply to a many-to-many relationship type being resolved into two one-to-many relationship types, which is the most common, but it can be any number greater than one.  So what you need to model the relationship type is a single table with much the same columns as your current tblVariants, but with each as a straightforward text column.

    To fill this table you create an append query which appends all columns from tblVariants, but in the case of the multi-valued fields the column name is followed with .Value.  If a column position in tblVariants can be Null append Nz(TheColumName,"N/A") or Nz(TheColumName.Value,"N/A") as appropriate.  As the table to which rows are being appended  is 'all key' no columns can be Null, so where a column is not appropriate to the relationship, N/A is inserted.  You don't even need to design this table beforehand, you can if you wish use a 'make table' query and then set its primary key to all columns, and index each column non-uniquely after it's been filled.

    Once you've filled this table you can then fill each of the referenced tables, ConvModels, Makes, Models etc by executing a series of 8 simple append queries, using the DISTINCT predicate to suppress duplication.  So to fill ConvModels for instance, if we assume the table which models the relationship type is named VehicleOptions the query would be:

    INSERT INTO ConvModels(ConvModel)
    SELECT DISTINCT ConvModel
    FROM VehicleOptions;

    Do similarly for the other seven referenced tables, and, once you've filled them all, create and enforce relations between VehicleOptions and each of the eight referenced tables.  Normally you'd give each an autonumber 'surrogate' foreign key, but in this case don't.  Using the table's single text column as its 'natural' primary key, and having text foreign keys in VehicleOptions makes the creation of correlated combo boxes very much easier.  You don't need to use 'hybrid' controls as in my demo, and you don't need to include the referenced tables in the combo boxes' RowSource properties, just the VehicleOptions table.  Make sure you use the DISTINCT predicate in the RowSource queries to suppress duplicate values in the drop-down lists.

    When correlating the combo boxes remember that you reference all of the combo boxes higher in the hierarchy as parameters in the RowSource query, not just the one immediately above it in the hierarchy.  In the AfterUpdate event procedure of each bar the final one, set all the combo boxes below it in the hierarchy to Null and Requery them all.
     

    Ken Sheridan, Stafford, England

    • Marked as answer by ChrisParkin Monday, November 30, 2015 10:56 AM
    Thursday, November 26, 2015 10:23 PM

All replies

  • Hi Chris,
    Rather than use a bunch of combo boxes to filter data, why not display the data in a form as a datasheet and let the user use column header filtering?  Take a look at this 3min. video that shows an example of what I mean.

    https://www.youtube.com/watch?v=n8tHGFyt0NM&feature=iv&src_vid=jfdzT3lXk7E&annotation_id=annotation_3121751963



    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.


    • Edited by DriveEV Monday, November 23, 2015 2:21 PM
    Monday, November 23, 2015 2:21 PM
  • Hi Chris,

    To answer your question if you can use cascading comboboxes with multi-value fields, I believe you can; although, I have not tried it. Try using the Value field of the MVF in your RowSource.

    Just a thought...

    Monday, November 23, 2015 4:06 PM
  • Hi h2

    The reason I wanted to do it with the cascading combo boxes was for data entry of new vehicle orders and ensuring the operator cannot enter data that is not applicable to a particular model.  For example, if you look at the top 2 in my screenshot, whilst predominantly the same, if you choose the 4031mm wheelbase you can select either 3.5t and 4.5t, but if you choose the 4300mm wheelbase you can only select 4.5t.

    Maybe I'm going about this all wrong and there is a much better solution to this.

    Chris

    Monday, November 23, 2015 5:33 PM
  • Thanks DB, I'll give it a go.
    Monday, November 23, 2015 5:34 PM
  • Thanks DB, I'll give it a go.

    Let us know how it goes... Good luck!
    Monday, November 23, 2015 6:12 PM
  • Well, I've researched to the nth degree, tried all the potential solutions I found and yet I cannot get the cascading combo boxes to work with a tables with MVF.

    There are 6 combo boxes; cbo2 values should be determined by cbo1.  cbo3 values should be determined by a combination of cbo1 and 2.  cbo 4 values should be determined by a combination of cbo1, 2 and 3... and so on.

    Now I'm stuck!!

    Wednesday, November 25, 2015 4:27 PM
  • Now I'm stuck!!

    If you're willing to email me a copy of your db, I could take a look. Just a thought...
    • Marked as answer by ChrisParkin Monday, November 30, 2015 10:56 AM
    Wednesday, November 25, 2015 6:13 PM
  • OK DB, will do

    Chris

    Thursday, November 26, 2015 11:03 AM
  • Is there an insurmountable reason why you are using a multi-valued field rather than modelling the many-to-many relationship type in the time-honoured way by means of a table which resolves it into two one-to-many relationship types?  If not, changing your design to use the conventional model, and, for data input, rather than a multi-valued combo box, using a subform based on the table which models the relationship type should solve your problem.

    BTW, for examples of the use of correlated combo boxes in various contexts you might like to take a look at ComboDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The primary purpose of this little demo file is to illustrate the use of correlated combo boxes in the context of a correctly normalized set of tables in which only a column referencing the lowest level of a hierarchy is included in a referencing table, unlike some others you'll find online where the referencing table is not normalized to Third Normal Form.

    Ken Sheridan, Stafford, England

    Thursday, November 26, 2015 2:01 PM
  • Maybe I'm going about this all wrong and there is a much better solution to this.

    Yes Ken, there is...

    I am a novice who, on this accasion, thought the idea of using MVF for creating the extensive amount of model variations would make the job of data entry very simple - which actually, it does.

    What I hope to learn from forums such as this, and from time served Access programmers such as yourself, is whether the path I have chosen is the ideal one or not (as I stated in an earlier posting).

    My question earlier regarding cascading combo boxes and tables with MVF was believed (but not confirmed) to be an acceptable route which is why I continued along that path.

    Your response is now suggesting that this is maybe not the ideal route to follow.  But 'time-honoured' is not really a cliché that I think should be applied in all circumstances.  In all walks, IT or other, people change things, people improve things and 'time-honoured' suggests I should have complete confidence in accepting the conclusions of a post from 1995.  I'm guessing, in 1995 it may not have been possible to have MVF??

    I appreciate your response Ken and I certainly will put your proposal into practice.

    Thanks again and hope to speak with you again some time.

    Chris

    Thursday, November 26, 2015 3:30 PM
  • But 'time-honoured' is not really a cliché that I think should be applied in all circumstances.

    It's not only 'time-honoured', it respects the basic principles of the database relational model, which a multi-valued field does not.  Use them at your peril!

    Ken Sheridan, Stafford, England

    Thursday, November 26, 2015 4:12 PM
  • OK Ken, I've now got all of the tables correctly ID'd with one to many relationships set up correctly.  What is the best way to populate all of the variants (>1000) into this set up?

    Thanks

    Thursday, November 26, 2015 5:50 PM
  • I think you may have misunderstood the nature of the relationship type.  There is not a set of one-to-many relationship types from each table to the next as there would be in a purely hierarchical model, my ComboDemo file being an example, where the model is:

    Counties---<Districts----<Parishes

    What you have is an octonary relationship type, an 8-way many-to-many relationship type.  In effect this is what your current table models, but using multi-valued fields rather than multiple rows. I referred in my earlier reply to a many-to-many relationship type being resolved into two one-to-many relationship types, which is the most common, but it can be any number greater than one.  So what you need to model the relationship type is a single table with much the same columns as your current tblVariants, but with each as a straightforward text column.

    To fill this table you create an append query which appends all columns from tblVariants, but in the case of the multi-valued fields the column name is followed with .Value.  If a column position in tblVariants can be Null append Nz(TheColumName,"N/A") or Nz(TheColumName.Value,"N/A") as appropriate.  As the table to which rows are being appended  is 'all key' no columns can be Null, so where a column is not appropriate to the relationship, N/A is inserted.  You don't even need to design this table beforehand, you can if you wish use a 'make table' query and then set its primary key to all columns, and index each column non-uniquely after it's been filled.

    Once you've filled this table you can then fill each of the referenced tables, ConvModels, Makes, Models etc by executing a series of 8 simple append queries, using the DISTINCT predicate to suppress duplication.  So to fill ConvModels for instance, if we assume the table which models the relationship type is named VehicleOptions the query would be:

    INSERT INTO ConvModels(ConvModel)
    SELECT DISTINCT ConvModel
    FROM VehicleOptions;

    Do similarly for the other seven referenced tables, and, once you've filled them all, create and enforce relations between VehicleOptions and each of the eight referenced tables.  Normally you'd give each an autonumber 'surrogate' foreign key, but in this case don't.  Using the table's single text column as its 'natural' primary key, and having text foreign keys in VehicleOptions makes the creation of correlated combo boxes very much easier.  You don't need to use 'hybrid' controls as in my demo, and you don't need to include the referenced tables in the combo boxes' RowSource properties, just the VehicleOptions table.  Make sure you use the DISTINCT predicate in the RowSource queries to suppress duplicate values in the drop-down lists.

    When correlating the combo boxes remember that you reference all of the combo boxes higher in the hierarchy as parameters in the RowSource query, not just the one immediately above it in the hierarchy.  In the AfterUpdate event procedure of each bar the final one, set all the combo boxes below it in the hierarchy to Null and Requery them all.
     

    Ken Sheridan, Stafford, England

    • Marked as answer by ChrisParkin Monday, November 30, 2015 10:56 AM
    Thursday, November 26, 2015 10:23 PM
  • Wow... Blimey Ken

    Thanks very much for the detailed explanation.  I will do my best to absorb and understand what you have explained and see if I can actually put it into practice.

    I'll let you know how I get on.


    Chris | UK

    Friday, November 27, 2015 9:09 AM
  • Good luck.  It's a relatively straightforward process, and a lot simpler in reality than it sounds when described, so you shouldn't have too much trouble implementing it.  It will make the construction of the correlated combo boxes easy, and you don't need to do any more to get a working solution.   A fully robust model would really require a little more to be done, but whether you want to go the extra mile is up to you.  I don't want to confuse matters at this stage, but once you've got things working we can always come back to enhancing the model to provide additional integrity if you wish.  It's not difficult, and won't change how users interface with the database.

    Ken Sheridan, Stafford, England

    Friday, November 27, 2015 11:12 AM
  • Hi Ken

    I did get your method to work over the weekend so thanks very much for that.

    Not only that, but I did receive a sample back from the DB guy which also worked and enabled me to use my query based upon the table with MVF.

    Thanks very much to you both; I now have two potential solutions for the future.


    Chris | UK

    Monday, November 30, 2015 10:56 AM
  • Hi Chris. Good luck with your project.
    Tuesday, December 1, 2015 11:16 PM