How to Change a Combo Box's Row Source Based on Selection Made in Another Combo Box

[Draco]

Ok, so, what I know of Access I've pretty much taught myself but VBA is a beast of thing to learn. The problem is, not knowing VBA in and out really limits what you can accomplish in Access.



The issue I have is I have a combo box bound to a table (Conrtol Source - Main Table, which of course is the Record Source for the entire form) and bound to another table (Row Source - lookupmedium). I also have another combo box bound to the same table as the other one (Control Source - Main Table) but I want to change the Row Source based on the selection made in the first combo box mentioned.

For example:

When the user selects TV from the first combo box I need the second combo box to list only the options that are TV stations

e.g. user selects TV from combo box one and the second combo box uses the the subLookupTV table as it's Row Source so that only TV stations are shown.

or the user selects Radio from combo box one and the second combo box uses the subLookupRadio table as it's Row Source so that only Radio Stations are shown.

I already have the tables built that will only contain radio stations or only contain TV stations and so forth. Just need the piece of code to link them all in that way. I assumed it might be an "IF-THEN" code or a Select Case code but it's hard to say.

Thanks!

[Bob Fitz]

IMHO it might be better to have one table for ALL the radio and TV stations with a field that identifies each record as one or the other.
Your second combo box could then be based on this table with criteria in the identifying field that references the first combo box.

[Draco]

So what your saying is to combine my seperate tables into one table and have a second collumn in that table identifying the media type (i.e. TV, Radio, etc) for each station and then have the row source for combo box 2 be filtered based on the results of combo box 1?

How would that work?

I'm guessing I would have to create a query based on the combined table, for each media type that uses the results of the selection in combo box 1 as a filter. The results of which would become the row source for combo box 2?

How would I go about coding that?

Or am I way off?

[alansidman]

There is a good example of what Bob is suggesting in this thread

http://www.access-programmers.co.uk/...d.php?t=223980

Alan