Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    One Combo controls the contents of another

    I am creating a form that starts by selecting the contents of 2 combo boxes.
    There are 6 regions - displayed in the first combo box for the user to select
    and 245 locations (total) - when a particular region is selected I want the second combo to only show the locations the apply to the selected region.
    The table with the info is [Locations] and the fields are [Region] and [Store Name]
    The first combo is set and displays the [Region] to be selected the second... "don't work so good"

    Help?
    There is always a way.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Combo controls the contents of another

    Take a look at this thread <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=37361&page=1&vie w=expanded&sb=5>http://www.wopr.com/cgi-bin/w3t/showflat.p...w=expanded&sb=5</A><A target="_blank" HREF=http://link>http://www.wopr.com/cgi-bin/w3t/showflat.p...w=expanded&sb=5</A>

    If you still have questions post back.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Combo controls the contents of another

    You could use the OnChange event of the first combo box to set the RowSource of the second combo box. (I'm sure there may be more efficient ways to pull this off, but here is my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>.)
    You could use a SelectCase statement like this:<pre>Private Sub cboRegion_Change()
    Select Case cboRegion
    Case 1 'or whatever values you have assigned to Region
    'cboStoreName.RowSource = "SELECT....(SQL Statement filtered to the Region listed above)"
    cboStoreName.Requery
    Case 2
    'cboStoreName.RowSource = "SELECT....(same situation as before)
    cboStoreName.Requery
    'As many Case statements as it takes
    Case Else
    Exit Sub
    End Select
    End Sub</pre>

    Be sure to set your LimitToList property to TRUE, just to avoid any errors caused by someone manually entering an invalid value.

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Combo controls the contents of another

    Sorry, I messed up the url...here it is <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=37361&page=1&vie w=expanded&sb=5>http://www.wopr.com/cgi-bin/w3t/showflat.p...w=expanded&sb=5</A>

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Combo controls the contents of another

    So close!
    this almost worked...
    SELECT YourTable.Cust_Num FROM YourTable WHERE YourTable.Route_Num = [Forms]![YourForm]![name of Route_Num dropdown];
    Problem: when I open the form and select a region the second combo updates properly... if I then select a different region the second list remains unchanged.

    Now what?!
    There is always a way.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Combo controls the contents of another

    You will need to requery the second combo in the after update event of the region combo.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Combo controls the contents of another

    I re-did the combo and I am getting a syntax error, Here is the string...
    SELECT [Locations].[Store Name] FROM Locations WHERE [locations].[region]=[forms]![location detail form]![cboRegion] ORDER BY [Locations].[Store Name];
    I get the "Enter Parameter Value" box on the [forms]![location detail form]![cboRegion]
    There is always a way.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One Combo controls the contents of another

    When all else fails i use the expression builder especially when dealing with form controls. Click on the elipsis next to the row source in the property sheet for the combo. Delete the current criteria in the criteria row and right click. Select build from the shortcut menu and build the criteria string from the selections in the builder. Hopefully this will correct your syntax problem.

Posting Permissions

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