Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    combo box list change (Access 97)

    Can anyone help? I have 2 fields on a form- the first is a combo list of possible grouping values, and the second is a combo list of the options within every grouping. What i'm hoping to do is get it so that the options visible in the drop-down menu in the second combo are only those that belong to the group that a user has selected in the first.
    Eg: The first combo contains the groups "Aspect" and "Geology"
    The second combo contains "North" South" "East" and "West" (with an extra field in the lookup table called Group, which in this case has "Aspect" for all of those), as well as "Sandstone" "Basalt" and "Shale" (which all have "Geology" in the Group column of the lookup).

    Is it possible that once a user selects (for example) "Geology" from the first combo, that in the second combo list, only the Geology options (Sandstone, Basalt and Shale) appear when it is clicked? I've tried to get it to work using queries but can't. Maybe some OnEvent macro??? Can anyone help? I hope i've been clear enough as to what I'm trying to do- it's a little hard to explain.
    Thanks.... OzMax <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combo box list change (Access 97)

    All you need to do is use the first combobox as the criteria for the saved query that populates the second combobox. Then in the AfterUpdate event of ComboBox1, you put something like this: Me!ComboBox2.Requery.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Aug 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box list change (Access 97)

    That's great! Thanks Charlotte! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    I've run into another problem though- can i get it to work on a subform?
    I've done what you suggested: The 1st combo box is the criteria for the 2nd combo box in the query (eg [Forms]![Facets]![ComboBox1]). On the form (which is only used as a subform) the requery is part of a macro that runs in the AfterUpdate event on the form.

    This all works fine if i open the form by itself, but if i access it as a subform (which it is primarily) then the query falls over, thinking it's a parameter query, and brings up an input dialog. Is there a way around this so that the requery action runs happily on a subform? The subform is not based on a query itself but is a simple one to many link. Any ideas anyone?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combo box list change (Access 97)

    If the combobox is on a subform, you have to fully reference the control on the subform. That means that either you can't use the form both ways, or you have to change the rowsource of the combobox to a different query if it's on a subform. The criteria for a query with a combo on a subform would be something like this:

    [Forms]![ParentFormName]![Facets].Form![ComboBox1]

    Where ParentFormName is the name of whatever form the subform is on.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Aug 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box list change (Access 97)

    Thank you!!!!!!!! It works perfectly. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    The subform is never used by itself (ie not as a subform) so it works no worries.
    Thanks again.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    284
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combo box list change (Access 97)

    I'm having the same problem with my combo boxes. But I don't know what you mean by "using the first combobox as the criteria for the saved query that populates the second combobox". Can you explain? I have a query that takes all the fields from both tables (Courses table, and the Modules table). I have courses (e.g. Language Arts, Math, Social Studies, etc.), and then when you select a course the next combo box should show the modules that are listed under that course.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: combo box list change (Access 97)

    What I mean is that the query for the first combobox (call it cboCourses) should be something like "SELECT CourseID, CourseName FROM Courses".

    The query for the second combobox (call it cboModules) should be something like "SELECT ModuleID, ModuleName FROM Modules WHERE Modules.CourseID = Forms!Form1!cboCourses".

    You would use the AfterUpdate event of cboCourses to requery cboModules so that it would present the modules associated with the selected course.

    Does that make it clearer?
    Charlotte

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: combo box list change (Access 97)

    Let me amplify one point in Charlotte's post. When she says "... the query for the first combo box should be..." what she is referring to is the Row Source of the combo box. In other words, the Row Source of the combo box should be a SQL String query, and that is what controls the choices you are presented in a combo box when you drop it down. Hope this makes it a bit clearer.
    Wendell

Posting Permissions

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