Cascading Combo Boxes in a subform trying to populate a many to many relationship????

[adfeds]

I'm trying to setup some cascading combo boxes built around a single table that has a many to many relationship through a "junction" table. A session or Class offering may have many locations and a location can have many class offerings, so I have created a junction table. This part has been easy and if I populate that junction table with test data I can run all the queries I want.

The big issue is the data input, so this is a form issue. I am trying to build a form that allows me to input the city for the location. I've successfully done this in a form based of the junction table in other subforms if it is just a single combo box. But, I need to have cascading combo boxes, because the location has to be filtered for ease of input. First by Country, then State, then the City.

I am putting all of this in a subform based on the junction table "CitySession"

The first combos' rowsource
SELECT DISTINCT [tblCity].Country FROM tblCity;

Then, to populate/filter the other combos

I've been able to utilize the after update events...

Private Sub cboCountry_AfterUpdate()
On Error Resume Next
cboState.RowSource = "Select DISTINCT tblCity.State " & _
"FROM tblCity " & _
"WHERE tblCity.Country = '" & cboCountry.Value & "' " & _
"ORDER BY tblCity.State;"
End Sub

Private Sub Combo6_AfterUpdate()
On Error Resume Next
cboCity.RowSource = "Select tblCity.CityUID, tblCity.City " & _
"FROM tblCity " & _
"WHERE tblCity.State = '" & cboState.Value & "' " & _
"ORDER BY tblCity.City;"
End Sub

Seems to me like it should work, but I get blank combo boxes, The state cbo populates after I select a blank field, and the city field shows up right away, but is not filtered at all. (the city combobox is the only one bound and it is bound to CityID). Also, when I select state, all the records above reflect whatever I just selected. I suppose this is due to not binding the boxes, but if I do bind the country and state combo boxes, I get "The Value you entered is not valid for this field.".

Is this a subform issue? It does populate my junction table and while I had the combo boxes working on their own form, I couldn't get it to populate the junction table. I need to populate that junction table somehow?

Come to my rescue. I'm not new to Access, but I've not done any development work in ten years and this little frustration has been going on for at least 4 days now! Ugh.... should have kept up my skills!:banghead:

 

[Cronk]

How many columns does the combo have and what are the field width settings?

 

[adfeds]

I created 3 distinct combo boxes, so the number of columns is two in each. As far as field widths, 48 for the state, country and city is 60 iirc. Your question brings a few things to mind that I will try. I could pull all the data at once in 4 fields, ID, country, state, and City and hide the three that don't have relevance to that "first" selection (country). Wonder if I can get the distinct Country SQL to work then, though... Thanks for the reply... any other ideas out there? Am I onto something?

 

[adfeds]

So far no dice, but am eager to get this solved. Will work with anyone that has an idea Thanks guys

 

[Cronk]

You didn't post the code for the cboState afterupdate event.

 

[Mihail]

Why you try to set the row sources for yours combos in VBA ?
Design the row sources in Query Design View, by pressing the ... (three dots) button in Properties Sheet.
Set the Criteria to take value from the previous combo:
Forms!YourFormName!PreviousComboName

This way you simplify VBA:

Private Sub cboCountry_AfterUpdate()
   cboState.Requery
End Sub

 

[adfeds]

So, while I like the idea Mihail, that doesn't seem to be working. Could be that I don't know how to use the Forms!Subforms!cboName pathing properly because I am having trouble mapping the data anytime I use Access 2010's new navigation form as the basis of my work. So, if it means anything, what would be the path if I am using the Navigation form, and this is on the Session Tab (form?) called frmSession, and then I have the subform called frmCitySessionSubform where the controls are located.

 

[Cronk]

While Mihail's suggestion is an alternative, there was nothing wrong with what you started with.

I suspect you have renamed your combo box because in cboCountry_AfterUpdate you set the row source of combo cboState.

Then in the following procedure in the code in your original posting, you refer to Combo6