3rd Combo Box Cascade Issues

[GAccess]

Hi All,



I currently have 2 combo boxes accurately cascading :

a. Combo Box 2 options depend on the selection of Combo Box 1

I want to have a 3rd combo box dependent on the selection of Combo Box 2.

Currently seeing blank options in my form though a selection in Combo Box 2 has been chosen. I have tried to look at some previous threads on this but my problem still hasn't been resolved.

Side note: I am currently using a query for reference on all combo boxes which represents the entire table of information that I need. I had to do this because when I attempted to create a combo box 3, many error messages of different types popped up and ruined the cascade I programmed at the beginning.

A little info on me: novice in VBA. Hopefully someone can give me information that will contain little to no coding? Thanks for your help

[John_G]

How are you determining the contents of combo box 2 after making a selection in combo box 1? If that method works, then setting the criteria for combo box 3 should be similar. This concept of "cascading combo boxes" is very common, but it is difficult, if not impossible, to do without VBA somewhere.

Please post some more details of what you trying to do, and how.

John

[ssanfu]

Hi All,

I currently have 2 combo boxes accurately cascading :

a. Combo Box 2 options depend on the selection of Combo Box 1

I want to have a 3rd combo box dependent on the selection of Combo Box 2.

Currently seeing blank options in my form though a selection in Combo Box 2 has been chosen. I have tried to look at some previous threads on this but my problem still hasn't been resolved.

Side note: I am currently using a query for reference on all combo boxes which represents the entire table of information that I need. I had to do this because when I attempted to create a combo box 3, many error messages of different types popped up and ruined the cascade I programmed at the beginning.

A little info on me: novice in VBA. Hopefully someone can give me information that will contain little to no coding? Thanks for your help

Attached is an a2K mdb example I created a few years ago. The form that opens has list boxes, but combo boxes work the same. The list boxes allowed me to see all of the options. Be sure and check the code.... The "frmDataEntry" has combo boxes - 7 IIRC.

[GAccess]

I set up a normalized table with a hierarchy of choices. Example.


Location Transportation Color
Mall Train Blue
Mall Train Black
Mall Bus Yellow
Park Bus Yellow
Park Bike N/A
Neighbor Home None None
School Taxi Green
School Bus White

Each cell is filled out and each column of each row is filled like the one above.

My First Combo Box provides choices of location.
Second Combo Box provides choices of transportation based off of location.

Third Combo box is suppose to provide the color of the the transportation selected in the second combo box.

FYI this is a generic example.

[GAccess]

Attached is an a2K mdb example I created a few years ago. The form that opens has list boxes, but combo boxes work the same. The list boxes allowed me to see all of the options. Be sure and check the code.... The "frmDataEntry" has combo boxes - 7 IIRC.

SSanfu,

Do you think you can tell me how to make use of the onfocus for my third combo?

Very impressive attachment by the way.. however it looks like you coded everything

[John_G]

The contents of the third combo box depend on the selection in BOTH combo box 1 and 2. Example: Transportation "Bus" is available for three different locations, so to select the color, you need to know both the location and the transportation type.

The row source for combo1 would be something like "Select distinct location from tablename"

After you select from combo1, in its after update event, you would set the rowsource for combo2:

combo2.rowsource = "Select distinct Transportation from tablename where location = ' & combo1 & "'"
combo2.requery

And then, after selecting from combo2, you do the same for combo3, but using the selections of combo1 and combo2:

combo3.rowsource = "Select distinct color from tablename where location = ' & combo1 & "' AND transportation = '" & combo2 & "'"
combo3.requery

This will need some mods depending on how you have your combo boxes set up, tables names and field names. But this should point you in the right direction.

John

[ssanfu]

There is an error in the combo1 and combo2 rowsources: there are missing closing double quotes:

combo2.rowsource = "Select distinct Transportation from tablename where location = '" & combo1 & "'"
combo3.rowsource = "Select distinct color from tablename where location = '" & combo1 & "' AND transportation = '" & combo2 & "'"

[ssanfu]

SSanfu,

Do you think you can tell me how to make use of the onfocus for my third combo?

Very impressive attachment by the way.. however it looks like you coded everything

I use the onFocus event to have the dropdown list visible without having to click in the arrow.
You should use the After update event to do the re-querying and clearing.

In my example, my lookup tables are seperate, where you have one table. Not wrong or bad, just different, so setting up the combo boxes is a little different.

I don't like changing the rowsources. So instead of setting the rowsources in code, I set it when designing the form/combo box.
I would set up the combo boxes like this:

row source
Combo1: "SELECT DISTINCT location FROM tablename"

Combo2: "SELECT DISTINCT Transportation FROMtablename WHERE location = '" & Me.Combo1 & "'"

Combo3: "SELECT DISTINCT color FROMtablename WHERElocation = '" & Me.Combo1 & "' AND transportation = '" & Me.Combo2 & "'"

Then in the after update event of combo box 1, you have to clear the values in combo boxes 2 & 3 and re-query them:

Private Sub Combo1_AfterUpdate()
    Me.Combo2 = NULL
    Me.Combo3 = NULL
    Me.Combo2.Requery
    Me.Combo3.Requery
    Me.Combo2.SetFocus
End Sub

' this causes the list to automatically drop down
Private Combo1_GotFocus()
    Me.Combo1.Dropdown
End Sub

In combo box 2, you would have:

Then in the after update event of combo box 1, you have to clear the values in combo boxes 2 & 3 and re-query them:

Private Sub Combo2_AfterUpdate()
    Me.Combo3 = NULL
    Me.Combo3.Requery
    Me.Combo3.SetFocus
End Sub

' this causes the list to automatically drop down
Private Combo2_GotFocus()
    Me.Combo2.Dropdown
End Sub

For combo 3:

' this causes the list to automatically drop down
Private Combo3_GotFocus()
    Me.Combo3.Dropdown
End Sub

[GAccess]

The contents of the third combo box depend on the selection in BOTH combo box 1 and 2. Example: Transportation "Bus" is available for three different locations, so to select the color, you need to know both the location and the transportation type.

The row source for combo1 would be something like "Select distinct location from tablename"

After you select from combo1, in its after update event, you would set the rowsource for combo2:

combo2.rowsource = "Select distinct Transportation from tablename where location = ' & combo1 & "'"
combo2.requery

And then, after selecting from combo2, you do the same for combo3, but using the selections of combo1 and combo2:

combo3.rowsource = "Select distinct color from tablename where location = ' & combo1 & "' AND transportation = '" & combo2 & "'"
combo3.requery

This will need some mods depending on how you have your combo boxes set up, tables names and field names. But this should point you in the right direction.

John

Hey John,

Thanks for the detail. I made an error by using the same color more than once because that is not what is happenining in my spreadsheet. The last column has unique entries. With that said.. I will use both of what you and ssanfu said and come to a solution!

Thanks so much

[GAccess]

nevermind almost getting this..

[ssanfu]

Hi all, I decided that the coding route was the best choice and went along with John's suggestion on using a rowsource command as well as SSafu's detailed code on the combo boxes. SSafu had a little extra snaz to his work and I find it very useful as it will appeal to the user's eye.

Anyways. As I said, I am not so great at coding. I went head and used baldy's http://www.baldyweb.com/FirstVBA.htm and placed the entire code given by SSafu and John between :

Private Sub cmdFind_Click()

End Sub

The second and third combo boxes are not populating anything upon selection of the first combo box:



combo1.rowsource = "Select distinct location from TBL"

combo2.rowsource = "Select distinct transportation from TBL where location = '" & Me.comboProponent & "'"
combo3.rowsource = "Select distinct color from TBL where transportation = '" & Me.comboSubproponent & ''"


the reason I labeled combo3 as such is because each color is unique.

Hope you can still help.

The purpose of cascading combo boxes is to limit the choices in the second combo box based on the selection of the first combo box.
After you make a selection form combo box 1, there will be choices available in combo box 2.
After you make a selection form combo box 2, there will be choices available in combo box 3.

To do this, you have to use the after update events of the combo boxes, not a button click event.

The way you have the code/combo boxes set up won't work.


Using my method, combo boxes 2 & 3 are re-queried using the combo box after update events.

Using John's method, the combo boxes 2 & 3 row sources are updated using the after update events.

Does this help?

[GAccess]

I went the original spread sheet you sent SSafu. I mirrored the query setup that you had for your third drop down box in the "orange" form. That helped.

The key was to make a 1 to many relationship with the Location Table to the LocationTransportationColor Table and then place all fields that would be involved in my combo3 into the query to use as requery information.

Thanks so much for your help.

This is solved.

[ssanfu]

I went the original spread sheet you sent SSafu. I mirrored the query setup that you had for your third drop down box in the "orange" form. That helped.

The key was to make a 1 to many relationship with the Location Table to the LocationTransportationColor Table and then place all fields that would be involved in my combo3 into the query to use as requery information.

Thanks so much for your help.

This is solved.

Great. Glad you got it working.