Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2 comb boxes (2000)

    I have a table that has 2 fields that are Market and Facility and a Market can have many facilties. I have a form that I have made 2 combo boxes from the preceeding fields and the user can select from the list. However, I want to make this foolproof where when the person chooses a market only facilites in that market can appear. Any help would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 comb boxes (2000)

    You can change the Row Source of the second combo box in the After Update event of the first one. See MSKB article ACC2000: How to Synchronize Two Combo Boxes on a Form.
    Alternatively, you can set the Row Source of the second combo box to a query with a parameter that refers to the first combo box. See MSKB article ACC2000: How to Create Synchronized Combo Boxes.

    You can also search for cascading combo boxes in this forum.

    Post back if you have further questions.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 comb boxes (2000)

    I am not sure if I am doing this right. For the first combo box(market),it has a query that returns market Id and market and I can choose market. The next combo box is system(which comes from a query that has market ID, market, and system and where I can choose system). Question is this the correct setup for the 2 combos and what would be the after update SQL that would go with this to limit the systems when choosing a particular market.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 comb boxes (2000)

    Assuming that Market ID is a number field, you could use code like this:

    Private Sub cboMarket_AfterUpdate()
    Me.cboSystem.RowSource = "SELECT [System] FROM [Systems] WHERE [Market ID] = " & Me.cboMarket
    End Sub

    where cboMarket and cboSystem are the names of the combo boxes and Systems is the name of the table or query that returns the systems, and System is the name of the field you want to display.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 comb boxes (2000)

    Thanks I finally have it working. Next thing, I have a query that has criteria forms!FrmReportMenu!cboMarket and the same for cboSystem that looks for those 2 combo boxes on the form report menu. However, when I run the query no data appears. What do you think is happening?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 comb boxes (2000)

    Can you post the SQL for the query?

  7. #7
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 comb boxes (2000)

    Here is the SQL.

    SELECT [TBL Health Systems Current].System, [TBL Health Systems Current].Market, [TBL Health Systems Current].[Srvc Prv Billed Tax Id], [TBL Health Systems Current].[Srvc Prv Line1 Nm], [TBL Health Systems Current].Hospital, [TBL Health Systems Current].[Rating Area]
    FROM [TBL Health Systems Current]
    WHERE ((([TBL Health Systems Current].System)=[Forms]![FrmReportMenu]![cboSystem]) AND (([TBL Health Systems Current].Market)=[Forms]![FrmReportMenu]![cboMarket]))
    ORDER BY [TBL Health Systems Current].[Srvc Prv Billed Tax Id];

  8. #8
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 comb boxes (2000)

    My guess (and it is a guess) is that the bound columns in your comboboxes are set to fields other than [TBL Health Systems Current].Market and [TBL Health Systems Current].System. By default the value of a combo box is the first field in it's RowSource, even if that column is hidden. If this is the problem, you could either change the query to match the bound column of each combo box, or change the bound column property in the combo boxes to match the query.

    Does that make sense? (I haven't explained it very well!)
    Waggers
    If at first you do succeed, you've probably missed something.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 comb boxes (2000)

    You are correct and thanks for the help.

Posting Permissions

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