Cascading Combo Box From Multiple Tables

[hardhitter06]

Hi Everyone,

I'm using Access 2003.

I've read a little bit about cascading combo boxes from the forum, but can't seem to get enough clarification for me to solve my problem.

Basically what I am looking for is two drop down boxes (combos), and after I make the first selection, based on that selection, particular information will flood into the second for me to choose from the list.

For example, in the first combo box, there's choice A, B and C (will always be this choice).

When you Choose A: 1, 2, and 3 will flood into the 2nd box.

When you Choose B: 4, 5, 6 will flood into the 2nd box.

When you Choose C: 7, 8 ,9....
(All of these will also always be the same choice)

Used this example for simplicity.

What I currently have as a "set up" is an Input form that feeds directly into tblVendors. Within this input form, I have a subform that has a combo box from tblCommodities that lists criteria "A, B and C" that will feed into that given table.

Now what I need some help with is when choosing from that first combo box that is already set up, to have these specific lists based on the first selection flood into the 2nd box.

Now do I need to create another table with this second generation of information, or can I create another field in tblCommodities that has the 2nd generation? Basically this is where I'm stuck and any help would be great.

Thank you!

 

[CEH]

Sounds like your form and subform should be based on a query of tblVendors and tblCommodities..... You might find you avoid your current problems.

 

[hardhitter06]

I don't have a "problem", I'm trying to learn how to create this additional combo box. The subform is used with a query between tblVendors and tblCommodities. I just need to find out how I take this one step further and have the second combo box based off of the first selection.

As I stated before, I'm not sure how you match up the choices of Combo2 to Combo1's choice. In other words, if a user selects "A" from combo box 1, how am I going to make Combo2's choices available, do I need to make another table with this second set of data?

If I add another table, do I then link it to this query I already have or do I create a new one?

My problem is I don't really know what I'm doing, it's not like something isn't working. Up to the first combo box, everything works fine...I just need to be schooled a little on how to flood data into the second combo box once a user makes a choice in the first...

 

[ByteMyzer]

Assume a scenario like the following:

You have two tables:

MyTable1
--------
ID1 | Name
-------------
A   | Value A
B   | Value B
C   | Value C


MyTable2
--------
ID2 | ID1 | Name
-------------------
1   | A   | Value 1
2   | A   | Value 2
3   | A   | Value 3
4   | B   | Value 4
5   | B   | Value 5
6   | B   | Value 6
7   | C   | Value 7
8   | C   | Value 8
9   | C   | Value 9

You have a form with two Comboboxes: MyCombo1 and MyCombo2.

In your form's Code Module you would have the following:

[COLOR="navy"]Private Sub[/COLOR] Form_Load()

    [COLOR="DarkGreen"]' Sets the list for the first Combobox[/COLOR]
    Me.MyCombo1.RowSource = "SELECT MyTable1.ID1, MyTable1.Name" _
        & " FROM MyTable1;"
    Me.MyCombo1.Requery

[COLOR="navy"]End Sub[/COLOR]

[COLOR="Navy"]Private Sub[/COLOR] MyCombo1_AfterUpdate()

    [COLOR="darkgreen"]' Clears the second Combobox when the value
    ' of the first Combobox has changed[/COLOR]
    Me.MyCombo2 = Null

[COLOR="navy"]End Sub

Private Sub[/COLOR] MyCombo2_GotFocus()

    [COLOR="DarkGreen"]' Sets the list for the second Combobox
    ' based on the selection in the first Combobox[/COLOR]
    Me.MyCombo2.RowSource = "SELECT MyTable2.ID2, MyTable2.Name" _
        & " FROM MyTable2" _
        & " WHERE MyTable2.ID1 = '" & Me.MyCombo1 & "';"

    Me.MyCombo2.Requery

[COLOR="navy"]End Sub[/COLOR]

 

[hardhitter06]

Alright, I'm having problems trying to do this. I made the two combos but they are empty and I need a little clarity and I think I'll be okay.

I am updating this database so I'm learning about it as I go. It seems that they are 3 tables involved..tblCommodities, tblCommoditiesGroupTwo (this one is the new one), and tblVendorCommodity.

The fields in each:

(1) tblCommodities:
CommodityID | AutoNumber
CommodityName | Text

Data (table format):
226 | Advertising & Marketing
227 | Architectural & Engineering

(2) tblCommoditiesGroup2:
VendorCommodityGroup2ID | Autonumber
CommodityID | Number
VendorCommodityGroup2 | Text

Data:
12 | 226 | Live Animals
13 | 226 | Domestic
14 | 226 | Animal Feed
15 | 227 | Minerals
16 | 227 | Earth and stone
17 | 227 | Scrap and waste

(3) tblVendorCommodity:
VendorCommodityID | Autonumber
Vendor | Number
Commodity | Number

Data:
6960 | 6156 | 226
6961 | 6154 | 226
6962 | 6155 | 227

This is the code I substituted in:

Private Sub frmVendorInput_Load()

' Sets the list for the first Combobox
Me.MyCombo79.RowSource = "SELECT tblCommodities.CommodityID, tblCommodities.Name" _
& " FROM tblCommodities;"
Me.MyCombo79.Requery

End Sub

Private Sub MyCombo79_AfterUpdate()

' Clears the second Combobox when the value
' of the first Combobox has changed
Me.MyCombo79 = Null

End Sub

Private Sub MyCombo81_GotFocus()

' Sets the list for the second Combobox
' based on the selection in the first Combobox
Me.MyCombo81.RowSource = "SELECT tblCommodityGroupTwo.VendorCommodityGroup2ID, tblCommodityGroupTwo.Name" _
& " FROM tblCommodityGroupTwo" _
& " WHERE tblCommodityGroupTwo.CommodityID = '" & Me.MyCombo79 & "';"

Me.MyCombo81.Requery

End Sub


So maybe that will help show you what I may be doing wrong. Also, for the table I created (the Group2) one, do I need to use the lookup wizard for a particular field in another table? Those other two tables were in there so I think they are set up how they should be...

 

[boblarson]

Don't know if it will help, but perhaps this sample will:
http://downloads.btabdevelopment.com/Samples/combos/CascadingCombosSample.zip

 

[hardhitter06]

I can't figure out what I'm doing wrong from the example

 

[boblarson]

I can't figure out what I'm doing wrong from the example

can you post your db?

 

[hardhitter06]

I can't attach my database, says I'm missing a token. I know it would help everyone lend a hand if they could see what was going on, but can anyone think of what else i could be doing wrong?

 

[hardhitter06]

From the Dashboard (Main Page), under “Input”, go to “Vendor. The combo box is on the bottom already set up for the first level. This was how the database was set up before I started updating it.

The tables involved are tblVendorCommodity, tblCommodities, tblCommodities2 (I believe). The only table I’ve created for this new feature was Commodities2.


I deleted my attempts because I was screwing up this form so that I couldn’t even use the first combo box. Plus the subform thing confuses me, really not experienced with those.


An important note is before I added this third table, the Commodity Combo box ties into the vendor…but I wasn’t really incorporating that into my code, and I’m not sure if you have to, but it’s more than just two combo boxes…these selections need to be linked to the respected vendor being assigned the type of Commodity. I think it will make a little more sense if you check out all the tables with “Commodity” in the name.

I was getting this error as I mentioned before about attaching the database so I deleted an extreme amount of queries, forms and reports, but I don't believe any of them tied into what I am working on now.

Hopefully this can get the ball rolling...thankyou everyone

 

[hardhitter06]

It has to do with the relationships or how the tables are talking because linking the vendor to the selected two part commodity is necessary and I think that's where I'm getting stuck. There's a couple qryCommodities that might need to be adjusted as well...I just can't seem to get the right combination together. I really need someone to trouble shoot this plzzzz.