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>

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.

Re: combo box list change (Access 97)

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?

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:

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.

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.