Results 1 to 14 of 14
  1. #1
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,781

    Ultimate Dynamic Multiple Cascading Comboboxes, synchronized in any order

    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:	58 
Size:	35.4 KB 
ID:	45449

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,923
    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 online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,781
    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 Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,928
    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 (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,781
    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 Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,928
    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:	53 
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 (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,781
    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
    Welshgasman is online now Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    994
    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, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  9. #9
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,928
    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:	47 
Size:	24.1 KB 
ID:	45457
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  10. #10
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,140
    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 online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,781
    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 Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,928
    Ha! Fair enough ��

    On an unrelated topic, is the Denver Access User Group (DAUG) still active?
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

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

  14. #14
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,928
    Thanks. Lots of videos there for me to watch!
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

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 - Senior Forums