Access form cascading combo box shows nothing in drop down menu

Question

I am trying to create a data entry form with cascading combo boxes to limit the users options. I had it working for a bit, then it decided to quit. I could just be overthinking it all though.

There are going to be about 50 boxes that I will have to set up. The first is a one to many. I got the first box to add to a new record correctly. When I get to the second combobox, the dropdown menu shows nothing. It's fine until I enter [forms]![NewSpecForm]![cboBoxSize] in the criteria. How do I create cascading combo boxes that will actually work permanently?

My third combo box is a many to many, so I created a junction table for that. I have found several tutorials on how to join the tables for cascading combobox and once I had that working, the first and second boxes weren't adding the correct info to the new record.

If I leave the AfterUpdate alone and have the second box on its own, it's adding the ID to the new record instead of the name. How do I fix that?

Answer 1

First of all, let's make sure we understand the concept behind "cascading combo boxes". The logic runs in such a way that after you make a selection from Combo1, the options available in Combo2 will change so that they are items only related to Combo1. Similarly, after we select an item from Combo2, the items in Combo3 will be only items related to what the user selected in Combo2.

So, first of all, every combo box besides Combo1 should be empty, or should be related to the default value of Combo1. You could do it either way.

Next, in the AfterUpdate event of Combo1, you should have something like this:

Me.cboCombo2.RowSource = "SELECT MyFieldNames " & _
                           "FROM tblMyTable " & _
                           "WHERE SomeID = " & Nz(Me.cboCombo1) & _  
                           "ORDER BY SomeValue"
Me.cboCombo2.Requery

Obviously, MyFieldNames, tblMyTable, and all those other values will depend on what you are actually looking for, your source table/query name and field names, etc...

OK, so now Combo2 should fill up with data that is only relevant to what you selected in Combo1, as we're using Combo1 as a filter in the SQL above.

If that looks OK, do more or less the exact same thing for Combo3. You may need to filter on values from both combos, or you may be able to get away with just using that value in Combo2, depending on your dataset.

Lather, Rinse, Repeat. I guess you're going to need to do that 50 times if you have 50 combos, but that's how it's done.

For further reference, see this article: http://www.fmsinc.com/microsoftaccess/forms/combo-boxes/cascading.html