combo box list change (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

That’s great! Thanks Charlotte!

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?

Reply | Quote

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.

Reply | Quote

Thank you!!!!!!!! It works perfectly.

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote