Row Source for Combo Box (1 Viewer)

[wilderfan]

I have 2 unbound combo boxes on a form.

The row source for the 2nd combo box is based on a query, using SQL code.

I'd like the SQL query for the 2nd combo box to change - depending on the selection made for the 1st combo box.

So, is it possible to use some sort of If statement in the row source property? That is,

If combo box 1 = ???? Then
run query 1
Else
run query 2


Hope this makes sense.

 

[boblarson]

Yes, it is. What is the SQL for the first combo and how exactly do you want it to change and what is it dependent upon?

 

[wilderfan]

Hi, Bob.

I'm still working on that tv episode db project.

The first combo box lists functions or job titles: Executive Producer, Producer, Director, Script Consultant, Writer, Actor. These are from a table which I have called tblFunctions. The fields are: functionID, Function (Sorry for the use of the word Function; I goofed on that.)

The second combo box lists names. The fields are: nameID, FName, LName, Category. Category has a list of values to choose from: Cast, Creative, Both

In other words, a single person (or name) could be categorized as someone who is just a Cast person, or just a Creative person (eg. Producer, Director, etc.) or Both.

The reason I added the Category field to the table tblName is to limit the # of names showing up in the combo box dropdown. That is, if you're adding an actor's name to an episode, then the combo box doesn't need to list those names of people who are just behind-the-scenes Creative people.

 

[missinglinq]

FUNCTION is, indeed, a Reserved Word in Access, and really needs to be changed before it jumps up and bites you where you'd rather not be bitten!

The concept you're talking about here is called 'cascading comboboxes.' Googling that term, or searching on it here, should get you a gazillion hits or so with examples.

Linq ;0)>