Results 1 to 14 of 14
  1. #1
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388

    Ultimate Dynamic Multiple Cascading Comboboxes, synchronized in any order


    Views: 42 Size: 62.8 KB">DynamicSynchronizingComboBoxes-davegri-v01.zip


    No requirement that forces a certain combobox order be used.
    Any user combobox choice will update the rowsource for all the others.
    Concept will work for 2 or more comboboxes acting as search critera for a form or report.

    Click image for larger version. 

Name:	Form.png 
Views:	388 
Size:	35.4 KB 
ID:	45449

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    just make a form that shows ALL records (continuous form), then user can pick from unbound combo boxes to filter:

    build the 'where' clause by cycling thru all the controls....
    it executes after a find button CLICK event
    if null, ignore.
    if not, apply.


    Code:
    '----------------
    sub btnFilter_click()
    '----------------
    dim sWhere as string 
    
    
    sWhere = "1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    
    
    If sWhere = "1=1" Then
      Me.FilterOn = False
    Else
      Me.Filter = sWhere
      Me.FilterOn = True
    End If
    end sub
    then user can click a DETAIL button to open a single rec form.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    build the 'where' clause by cycling thru all the controls....
    it executes after a find button CLICK event
    if null, ignore.
    if not, apply.
    That's a simplistic solution that leaves a problem for the user since it doesn't cascade at all, showing all combobox rows all the time.

    The whole point of my DB is to cascade any number of comboboxes in any order, showing only rows dependent on the other comboboxes. You can even go back and blank out a combobox after making selections in other comboboxes.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Dave

    I see Ranman gave EXACTLY the same answer in another thread: https://www.accessforums.net/showthread.php?t=83795

    Anyway, perhaps somewhat similar to your example, please see these examples on my website http://www.mendipdatasystems.co.uk/m...ter/4594454290
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Colin, had a look at your DBs and took notes. Both are excellent examples of filtering data via multiple comboboxes, but the comboboxes do not filter each other, i.e.; do not cascade. That functionality is the main thrust of my example.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Dave
    Sorry I meant to also provide a second link showing an example of 5 cascading combos for use with UK postcodes.
    http://www.mendipdatasystems.co.uk/c...xes/4594455723

    The form used comes from one of my commercial apps & is used to filter down all 2.6 million UK postcodes to a single postcode.
    For that reason, unlike yours, this example has to be done in order:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	382 
Size:	206.5 KB 
ID:	45451

    Your version is a very good example of using cascading combos.
    However, I think there could be issues with handling individual combos such as last name & first name if you had a large dataset of more than say 200 records.
    Allen Browne uses a different approach to that issue where data is only fed into the combo after the user has entered 3 letters: http://allenbrowne.com/ser-32.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Your version is a very good example of using cascading combos.
    However, I think there could be issues with handling individual combos such as last name & first name if you had a large dataset of more than say 200 records.
    I hadn't thought of volume data problems, so I gave it a test. Here's a copy with 70,000 records. Delay in updating related comboboxes is virtually imperceptible on my machine, which is very acceptable.


    DynamicSynchronizingComboBoxes-davegri-v02.zip

    Note: This version also fixes a bug with the ID combobox criteria when the ID is only one digit.
    Last edited by davegri; 06-11-2021 at 07:06 AM. Reason: added note

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Works fast on my laptop as well, which is quite a few years old now, only having a Duo CPU T5550 @ 1.83GHz with only 4GB Ram and Windows Pro 32bit.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Dave
    Can I just stress that I wasn't intending to be critical in any way
    There were two points related to my comment about using a large dataset in a combo ....

    1. I agree that there is only a brief delay whilst the combos load / update with 70K records though that might be very different with several million records in a linked BE table.

    2. My comment also related to the time needed for users to scroll through all records beginning with say 'B'
    Of course, this is significantly reduced this by having the combo respond/filter to each key stroke e.g. BI, BIG, ... which is great.

    I do suggest you try this again with say 2 million records and see how well it responds to each of the above

    FWIW, another method I use for filtering large datasets using combos is to have a listbox that updates to the combobox entry e.g.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	376 
Size:	24.1 KB 
ID:	45457
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    for large datasets, a) I require the user to type 2 or 3 characters before populating the recordset and b) I do not preprogramme the initial * for a like criteria, instead I train the users to use it if they need to

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Well, in regards to large recordsets, I'll just leave it to the user to decide how large is too large.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ha! Fair enough ��

    On an unrelated topic, is the Denver Access User Group (DAUG) still active?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Oh, yes. DAAUG https://www.daaug.org/

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks. Lots of videos there for me to watch!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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. Synchronized Comboboxes
    By IncidentalProgrammer in forum Programming
    Replies: 9
    Last Post: 11-13-2014, 02:05 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