Results 1 to 4 of 4
  1. #1
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314

    Filter Combo Box list based on a selection in another Combo Box

    I have two tables, Events and Activities. The child table, Activities, associates members with events. I have a parent form for the event and a sub-form for the activities.



    I have a "Role" combo box, in the sub-form, to select from a rather long list of Roles obtained from a table of Roles. These roles are associated with participation in an event defined in the parent Event Form. The Event is defined by a Combo box called "Event Type". It would be helpful to filter the long list of Roles in the child sub-form, based on the Event Type selected in the parent form.

    The Role Table record currently includes an ID and a Role.
    I am thinking of adding a 3rd column to associate a set of Roles to an Event Type it would be used with, such as:
    [ID, Role, Event Type]. Then have the Role Combo Box filter its list to include only those Roles associated with a certain Event Type value, while not displaying the Event Type in the list.

    I can almost think my way through it, but not quite. Any Ideas would be appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is called cascading or dependent combobox/listbox. A very common topic. Usually the boxes are on same form.

    What does 'almost' mean - what have you tried?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Wade
    There's a good example of cascading combos here http://www.fmsinc.com/MicrosoftAcces...cascading.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

  4. #4
    WCStarks is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    314
    After being asked if I had tried it yet, I was finally able to resolve on my own, how to filter the options in a combo box in a sub-form, based on a value selected a combo box in its parent form.

    I need to reference a combo box in the parent form (Events) from its sub-form (Activities). I need to filter the [Role] Combo box options in the Activities form, depending on the value selected in the Events combo box, [Event_Type].

    Role_Act is the control table for the Activity Roles. The record structure for Role_Act is: [Role, Ev_Type], Where Ev_Type has a value of “All”, or of one or more values which match Event Types. I added Ev_Type to specify which Event_Types each Role should be associated with.

    Here is what I ended up with, and it seems to work. I borrowed some syntax from how Colin showed me, earlier, to toggle the sort order of a Combo box. I am still fairly new at this stuff, but as new problems are resolved, my horizons slowing expand. Thanks to you all for your help.

    Private Sub Role_GotFocus()
    Me.[Role].RowSource = "Select [Role_Act].[Role], [Role_Act].[Ev_Type]" & _
    " From [Role_Act]" & _
    " Where InStr([Role_Act].[Ev_Type], [Forms]![Events]![Event_Type])" & _
    " or [Role_Act].[Ev_Type] = 'All';"
    End Sub

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

Similar Threads

  1. Replies: 3
    Last Post: 07-03-2013, 10:38 AM
  2. Replies: 1
    Last Post: 10-30-2012, 10:29 AM
  3. Replies: 1
    Last Post: 02-03-2012, 03:51 PM
  4. Replies: 33
    Last Post: 01-13-2012, 07:44 AM
  5. Filter List box from combo box selection
    By thart21 in forum Forms
    Replies: 3
    Last Post: 11-09-2011, 12:00 PM

Tags for this Thread

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