Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    7

    Unanswered: Cascading combo boxes

    Hi,

    I'm new to Access and am trying to create an input form with 3 cascading combo boxes: Sector, Segment, and Subsegment.

    The database has a main table of Company records of which 3 of the fields are Sector, Segment, and Subsegment. Each of these has their own table consisting of an ID and Name, e.g.:
    Sector table:
    SectorID: 1, SectorName: New Media
    SectorID: 2, SectorName: IT
    ...

    Segment table:
    SectorID: 1, SegmentID: 1, SegmentName: Advertising
    SectorID: 1, SegmentID: 2, SegmentName: Online Video
    ...

    Subsegment table:
    SegmentID: 1, SubsegmentID: 1, SubsegmentName: Ad network
    SegmentID: 1, SubsegmentID: 2, SubsegmentName: Ad exchange
    ...

    I have an input form from which I'd like users to be able to select first the Sector, then Segment, and Subsegment, with each subsequent combo box updated per the previous selection.

    What's the quickest / easiest way to do this? I have minimal SQL experience though can understand basic syntax.

    Thanks,
    Jerome

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I have attached a sample database based on your tables. Use frm_input, I have added code to each combo box to refresh (update) the dropdown when the combo box gets the focus.

    In your subsegment table I have added a field called sector_id, otherwise you could have an incorrect list displayed.
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2010
    Posts
    7
    Thanks.

    I decided to use the AfterUpdate() event procedure instead of GotFocus(). It seems that when I change the Sector selection for one of the records, the Segment selection changes for ALL records. Any thoughts on why this is happening? I suppose it has to do with how the requerying?

    -Jerome

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK
    to get this to work I used a form which acts as a report filter
    that form has a listbox with the index of the report version you want to run
    a button whihc opens the report

    in the forms on open event
    the following code is bunged in
    Select Case Forms!Form1.List0.Value
    Case 2: Me.RecordSource = "select * from Query2"
    Case 3: Me.RecordSource = "select * from Query3"
    Case Else: Me.RecordSource = "select * from Query1"
    End Select

    where list0 is the name of the listbox indicating the report version wanted
    form 1 is the name of the form holding the listbox list0

    it works because all 3 versions have the same columns in them
    had you had a single table with all the products in you coudl open the report and set a filter for the specific product

    this is a kludgey workaround.. it works but Im almost certain you should redesign your tables
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •