Results 1 to 7 of 7
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185

    Combobox Lists Cascading From Previous Comboboxes

    Hi All



    I have a form, with 4 comboboxes across the top running left to right:

    POL Combo - POD Combo - Carrier Combo - Contract Type Combo

    Currently they have the below in their Row Source:

    POL Combo = SELECT DISTINCT FRT_Table.[POL Name] FROM FRT_Table ORDER BY FRT_Table.[POL Name];
    POD Combo = SELECT DISTINCT FRT_Table.[POD Name] FROM FRT_Table ORDER BY FRT_Table.[POD Name];
    Carrier Combo = SELECT DISTINCT FRT_Table.Carrier FROM FRT_Table ORDER BY FRT_Table.[Carrier];
    Contract Type Combo = SELECT DISTINCT FRT_Table.[Contract Type] FROM FRT_Table ORDER BY FRT_Table.[Contract Type];

    As you can see this just pulls unique values from the relevant fields in FRT_Table

    This works great at the moment no issues, but just to make the form a little better for users I would like to have the comboboxes further filter their value lists based on the other comboboxes?

    So if a user selects a value in POL Combo, then only the unique values left after filtered by POL are now listed in the other 3 comboboxes.

    Ideally this would work no matter which box is entered first, would work with multiple boxes getting values so the remaining box would get less and less values as the other boxes filter it down?


    I assume VBA would be needed in the AfterUpdate events?

    Any help would be greatly appreciated.

    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see how you can have it both ways; i.e. you cannot pick from a child combo if there's no value in a parent because the values in a child combo are filtered by a choice in the parent. If you could, there would be no point as the child would have to show a complete list regardless of whether or not a parent value was chosen. Each subsequent combo row source needs a WHERE clause that restricts its records to those where there is a matching value in the field that the parent combo gets its list from. If you research this you'll find tons of examples from which to pick. Assuming cmbPOD is the control name, its value is text and this is in vba code, perhaps something like

    Code:
    "SELECT DISTINCT FRT_Table.[POD Name] FROM FRT_Table WHERE FRT_Table.[POL Name] = '" & Me.cmbPOD & "' ORDER BY FRT_Table.[POD Name]"
    If not code, you'll have to modify to suit.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It might be helpful if you could tell us what exactly the business process is that your form and combo boxes support.
    Also helpful for context would be a graphic of your tables and relationships.

    As micron has advised, typical use of multiple comboboxes is "cascading comboboxes".

    For more tutorials on ComboBoxes

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the attached sample. You will need to implement something similar for each of the four combo boxes (for each row source add three calculated fields referencing the three other combos: POL: IIf(IsNull([Forms]![frmYOURFORM]![POL Combo]),True,InStr([POL Name],[Forms]![frmYOURFORM]![POL Combo])>0) and set the criteria to True.

    Then for each combo in the Enter or GotFocus event do a requery of that combo. So for cboPOL you enter Me.cboPOL.Requery in its GotFocus event.


    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Have a look at this. The textboxes showing the SQL combobox rowsources and form recordsource are for debugging help and to display how things work. You can watch the SQL change as you make various selections in the combo boxes. They can be deleted and code eliminated that display them.

    stildawn-davegri-v01.zip


    Click image for larger version. 

Name:	stil.png 
Views:	40 
Size:	40.2 KB 
ID:	45172
    Last edited by davegri; 05-06-2021 at 10:45 PM. Reason: clarif

  6. #6
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Wow thanks heaps guys, above should be enough for me to figure it out.

    Thanks

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome, good luck with your project!
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. cascading comboboxes
    By Ultimateneo in forum Queries
    Replies: 3
    Last Post: 01-01-2020, 04:25 PM
  2. Cascading comboboxes
    By hinchi1 in forum Access
    Replies: 1
    Last Post: 07-04-2018, 04:52 AM
  3. Cascading Comboboxes
    By JennyL in forum Access
    Replies: 3
    Last Post: 03-13-2017, 11:26 AM
  4. Replies: 3
    Last Post: 03-31-2016, 12:16 PM
  5. Cascading ComboBoxes
    By GAccess in forum Forms
    Replies: 1
    Last Post: 03-06-2012, 05:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums