Cascading Combo Boxes

[beckie1234]

I need some real help. I a working on a subform that a user wants for easier data entry. On it, it has three combo boxes each combo box need to feed the next with choices that come from the previous boxes selection. I have so far three tables that I am using. I haven't attempted getting the third combo box to work yet because I can't get the first two to work. The tables are line, tbl1, tbl2. They are the ones I am using for the first two combo boxes to work. Where do I start? Just as I think it will work it doesn't. HELP!!

 

[RuralGuy]

Let's see if Mr.Green's examples help a bit. Cascading Lists for Access Forms

 

[beckie1234]

That is one of the examples I have been working with and for some reason I can not get it to work with what it is I am doing. I have set the code to the naming conventions I am using so that it basically duplicates what I am seeing and I am still missing something. I really need help in figuring this out. I just don't know what I am missing. Thanks.

 

[RuralGuy]

I don't know what your db contains but if the RowSource of the 2nd ComboBox is looking at the 1st ComboBox, then all you need to do in the AfterUpdate event of the 1st ComboBox is to Requery the 2nd ComboBox with Me.2ndComboBoxName.Requery. (using your 2ndComboBoxName of course!)

 

[beckie1234]

I haven't tried it yet but I will in a little while. Please keep checking back later so I can ask more questions. I have been trying to get this to work since about 6:30 this morning so I think I need to walk away and regather. Thank you for your help. When I do requery how would I write it? the combo boxes are labeled combo box 2, 3, and 4.

 

[RuralGuy]

You should really avoid spaces in any names. It makes life a lot easier down the road. Having said that, try something like:
Me.[combo box 2].Requery
You need the brackets [] because of the spaces in the name.

 

[beckie1234]

This is the code I am trying to use:

Private Sub ComboBox2_AfterUpdate()
Me.ComboBox3.RowSource = "Select MachCenterName FROM" & _
" tblMachCenter WHERE LineId= " & Me.ComboBox2 & _
" ORDER BY MachCenterName "
Me.tblMachCenter = Me.tblLine.ItemData(0)
Me.ComboBox2.Requery
End Sub

At least it is one of the versions I have used. I doesn't seem to work and I still need to add a ComboBox3 that will have a list that is populated by ComboBox2 selection. ComboBox2 has two choices. ComboBox3 has 9 or 10 depending on the line chosen and the ComboBox4 will have 2 to 4 selections based on the item from ComboBox3. I try to follow camel case when I do my naming Conventions. Any suggestions or go ahead and stick with the one on the link you sent. Thanks for your help.

 

[RuralGuy]

What are these two lines for?

Me.tblMachCenter = Me.tblLine.ItemData(0)
Me.ComboBox2.Requery

Are you actually changing the RowSource of ComboBox3 or just refreshing it? If it is not changing then Me.ComboBox3.Requery is all you need.

 

[beckie1234]

I was hoping i was writing a query that would use the line1 or line2 selection to populate the 3rd combo box. When I take the line before the requery out I get a data type mismatch or criteria error message when I select the line I want and then click on the down arrow on the 3rd combo box on the subform. I changed the requery to 3rd combo also. What do you suggest?

 

[RuralGuy]

I'm afraid you have lost me. It looks like you have a table named tblMachCenter as well as a control on your form! Is that true? Now you mention a SubForm! Can you post a stripped down version of your db for us to look at and play with?

 

[beckie1234]

Ok I have recreated a stripped down version of the DB showing this subform. I is used to report downtime for two lines and the users just want to pick from a list that becomes loaded from a previous choice, those choices are done through a comboBox list.

 

[RuralGuy]

Hi Beckie,
Did you see that your db never got posted?

 

[beckie1234]

I thought it was attached. Trying again. Can you please email the thing back to my email when you are done looking at it or do you just want to post it?

 

[RuralGuy]

Beckie,
All of your ComboBoxes should be based on queries and not tables directly. I can't tell from the db what you want the 1st cbo to display?

 

[beckie1234]

The first combo box will be changed to a Calender to enter a date. What kind of query am I looking at and where would it go? Now I am confused about where to start?

 

[RuralGuy]

You would create a query for each cbo that has a criteria in one of the fields to reference the previous cbo! As for now, can I just start with the 2nd cbo?

 

[beckie1234]

Please go ahead and start from cbo 2. That is the one that lists Line 1 or Line 2. It feeds to the list in cbo 3.

 

[RuralGuy]

I'm working at getting cbo2 and cbo3 working in tandem to give you an idea where to go from there.

 

[beckie1234]

I have to go offline for about 4 hours but I will be back so please stick with me on this. I really appreciate all you are doing.

 

[RuralGuy]

See if you can pick it up from here. cbo2 and cbo3 are working together. If not then post back.