Cascading comboboxes

[OrganMan]

I'm sure my question is very simple but I just can't get it to work.

I have two tables:

1. Manufacturer with ManId and ManDesc
2. Set with SetId, SetDesc and ManId

Two comboboxes:

1. cboManufacturer which is displaying all of the manufacturers in the table from the rowsource property.
2. cboSet which I would like to display only the Sets from a the particular Manufacturer that is selected by the user. I have nothing selected in the rowsource for this combobox.

I tried the example on cascading combo boxes, but neither of the examples will work for me because I have two tables and far too many Manufacturers to use a select case.

I am thinking that the answer would include using the ManId but not quite sure how to do so.

Thanks for all the help.

OM

 

[RuralGuy]

Maybe this link will help.

 

[Singh400]

There are plenty of topics on this. Please use the search function.

 

[OrganMan]

Wondering if anyone else can help, I tried two different examples of cascading comboboxes still cannot get the 2nd combo (cboSet) to populate
This is my most recent code:

Private Sub cboManufacturer_AfterUpdate()
' Update the row source of the cboSet combo box
' when the user makes a selection in the cboManufacturers
' combo box.
Me.cboSet.RowSource = "SELECT SetDescription FROM" & _
" Sets WHERE ManufacturerDescription = " & _
Me.cboManufacturer & _
" ORDER BY SetDescription"

Me.cboSet = Me.cboSet.ItemData(0)
End Sub

I added the following code to the row source of cboManufacturer:

SELECT [Manufacturer].[ManufacturerId], [Manufacturer].[ManufacturerDescription] FROM Manufacturer ORDER BY [ManufacturerDescription];

...And it is populating cboManufacturer

Is there something wrong with my tables?

1st table:
Name: Manufacturer
Field: ManufacturerId (Number)
Field: ManufacturerDescription (Text)

2nd Table:
Name: Sets
Field: SetId (Number)
Field: SetDescription (Text)
Field: ManufacturerId (Number)

I created a 1-Many relationship from ManufacturerId in the Manufacturer table to the ManufacturerId in the Sets table.

Thanks for the help!

 

[JANR]

I would try something simpler. In the rowsource of cboSet put:

SELECT [SetDescription].[sets] 
FROM [sets]
WHERE [manufacturerId].[sets] = [forms]![YourFormName]![cboManufacturer]

Then in cboManufaturer's after update event:

Private Sub cboManufacturer_AfterUpdate()
   Me.cboSet.Requery
End Sub

Replace [yourFormName] with the actual name of yor form.

JR

 

[Singh400]

Agree with JANR the .requery method is much easier to use and maintain/update. If you still can't get it to work, upload the db here and I'm sure we can help

 

[JANR]

SELECT Sets.SetDescription, Sets.SetId 
FROM sets
WHERE (((sets.ManufacturerId) = [forms]![YourFormName]![cboManufacturer]))

I was a little bit fast with the SQL, I'v also added SetId which you should use as the bound coloum in cboSets

JR

 

[OrganMan]

I tried the code that you provided in the rowsource of cboSet still no go. I even tried reversing the table and field names in the code but still the same. Attched is the db (AnotherTry.mdb), thanks so much for all of the help.

 

[JANR]

SELECT Sets.SetDecsription
FROM Sets
WHERE (((Sets.ManufacturerId)=[forms]![frmCardLookup]![cboManufacturer]));

It works it's just a typo from me use this sql in cbosets

JR

 

[OrganMan]

Hey JANR,

Just wondering what code I would have to put in the rowsource of cboSubset to have a list of subsets populate after I select a manufacturer then a set. I have added data to the subset table, also added a SetId field and have also added a second after update event for cboSet. I have tried every possible combination that I can think of so I'm missing something here. I don't think having relationships setup here will make a difference will it?

Thanks JANR.

 

[JANR]

You want me to do your dirtywork

The basic idee here is to filterdown options after what the user selects in the previous combobox. If the basic tablestructure of your lookuptables is sound this is the best solution. Here's how it works:

In your first combo in the rowsource klikk on the querybuilder and select your first table and add at least 2 fields (ReordID and what to view) Hide RecordID by setting columnwidth to : 0;2cm

in the afterupdate event of the combo type: Me.nextcomboname.Requery.

2. In the next combo in the loop select Rowsource and build this combo's recordsource. Add a minimum of 3 fields (RecordId, Description and the field that relates to the first combo)

In this fields criteria add a refrence to combo1 using this syntax:
Forms!NameOfForm!NameOfCombo1

If there are more cascades just repeat the process of combo2.

Important note: By refrencing like this you HAVE to be sure WHAT to combobox is storing and NOT displying!! and that is the boundcoloum.

To make it easy for you here is your SQL for your combos:

Manufactuers combo:

SELECT Manufacturer.ManufacturerId, Manufacturer.ManufacturerDescription
FROM Manufacturer
ORDER BY Manufacturer.ManufacturerDescription;

Setscombo:

SELECT Sets.SetId, Sets.SetDecsription
FROM Sets
WHERE (((Sets.ManufacturerId)=[forms]![frmCardLookup]![cboManufacturer]))
ORDER BY Sets.SetDecsription;

Subsetscombo (don't know your Sortorder, set it your self. )

SELECT Subsets.SubsetId, Subsets.SubsetDescription, Subsets.YearId
FROM Subsets
WHERE (((Subsets.SetId)=[forms]![frmCardLookup]![cboset]));

And Yearcombo (I guessed a little here )

SELECT Years.YearId, Years.YearDescription
FROM Years INNER JOIN Subsets ON Years.YearId = Subsets.YearId
WHERE (((Years.YearId)=[forms]![frmCardLookup]![cbosubset]));

Don't forget to requery these combos:

Private Sub cboManufacturer_AfterUpdate()
   Me.cboSet.Requery
End Sub
 
Private Sub cboSet_AfterUpdate()
    Me.cboSubset.Requery
End Sub
 
Private Sub cboSubset_AfterUpdate()
    Me.cboYear.Requery
End Sub

JR

 

[OrganMan]

Everything is working great to this point, able to select a subset from the subset combobox. I have added a list box that will display the cards from my card table within that particular subset. How do I code to have this listbox populate when I select the subset. I am pretty sure that I have the code in the rowsource of the listbox correct, just not sure about coding for the event.

Thanks