How do you control contents of multiple dropdowns? 1

[Hairy64]

I have a field in my database that I restrict via a dropdown list. Now I need to add a second field that will restrict what is entered via a dropdown - the contents of which is controlled by the first dropdown list.

i.e. I select class category of 'Safety' from dropdown #1, then dropdown list #2 would then contain the list of safety classes to select from.
If I select class category of 'vehicles' from dropdown #1, then dropdown list #2 would then contain the list of vehicle classes to select from.[/color blue]

Can anyone tell me how to do this?

Thank You
Mike J.

I have not failed. I've just found 10,000 ways that won't work - Thomas Jefferson

 

[RobertT687]

It's pretty easy.
Assume you have two combo boxes (cbo1 and cbo2).

Assume the RowSource of cbo1 is 'Select ClassCategory From ClassCategories'

Assume the initial RowSource of cbo2 is 'Select AvailableClass From AvailableClasses'

Assume there is a column of ClassCategory in table AvailableClasses.

In the On_Change event of cbo1 add code like:

   Me!cbo2.RowSource = "Select AvailableClass From AvailableClasses WHERE ClassCategory = '" & Me!cbo1.Value & "'"
   Me.cbo2.Requery

If the datatype of ClassCategory is numeric, omit the concatenated single quotes surrounding Me!cbo1.Value

 

[Hairy64]

RobertT687

First, I want to thank you for your response and apologize for the delay in responding. Spring colds can cause things like that to happen.

Anyway, I can't get it to work. Here is more detailed information:

Table structure:
ID (Key, auto number. Not used)
Category (text, indexed w/no duplicates)
Course (text, indexed w/no duplicates)

When I click on the SelectCategory combobox, the rowsource is

SELECT DISTINCT Category FROM Tbl_Training_Courses ORDER BY Category;

This part works just fine.

Under the 'On Change' event, I have the following code which should - and does - change the rowsource for the second combobox (SelectCourse):

Private Sub SelectCategory_Change()

    Me!SelectCourse.RowSource = "SELECT Course FROM Tbl_Training_Courses WHERE Category = '" & Me!SelectCategory.Value & "' ORDER BY Course;"
    Me.SelectCourse.Requery
    
    End Sub

This is the one that does NOT work for some reason.

Any ideas?

 

[RobertT687]

How is it not working? Is it returning an error?
What if any results appear in the second combo box?
Do you have anything coded as the default rowsource of the SelectCourse combo box?
Is your code building a legit SELECT statement?
Try this:
In your Change event:

Private Sub SelectCategory_Change()
dim tsql as string
    tsql = "SELECT Course FROM Tbl_Training_Courses WHERE Category = '" & Me!SelectCategory.Value & "' ORDER BY Course;"
    debug.print tsql
    Me!SelectCourse.RowSource = "SELECT Course FROM Tbl_Training_Courses WHERE Category = '" & Me!SelectCategory.Value & "' ORDER BY Course;"
    Me.SelectCourse.Requery
    
    End Sub

See what is printed in the immediate window. Cut and paste that as the SQL in a new query and see if it returns anything.

Otherwise I can't see why it doesn't work.

 

[Hairy64]

RobertT687

Now I feel stupid !!!

Know how 'they' say the simple things cause the biggest problem? Well, if I had selected table/Query as the Row Source Type, then I would of had this running two days ago.

I am not sure how I missed that, but the problem is solved and the ComboBoxes are working fine now.

Thank you for your help!

MichaelJ

I have not failed. I've just found 10,000 ways that won't work - Thomas Jefferson

 

[RobertT687]

Glad you got it working.

Thanks for the star!