Linking combo boxes 2

[cantech2]

I have created categories and subcategories fields, I want the selection made on the categories combo box to determine the options available on the subcategories combo box

 

[bluekestrel]

Cantech

I am assuming you have 2 tables - Categories and SubCategories. Their must be a relationship between the two. There should be a lookup field in the SubCategories table to the Category field in the Category Table.

On your form firstly create the combo box for Categories.For the purposes of this example called cmbCategoryList

Now create a query based on the SubCategory Table.

This query should contain all the fields you want to display in your SubCateogries combo box Plus the Category field.

On the criteria of the Category Field in the query put the following

[Forms]![frmYourForm]![cmbCategoryList]

save the query

Now create a second combo box on your form and use the combo box wizard to select the query you just created as the record source. For this example we will call this second combo box cmbItem

Nearly finished - now in the properties of the first combo box you created you must put an AfterUpdate event which will requery the second combo box each time a different category is selected in the first combo box.

***********************************************
Private Sub cmbCategoryList_AfterUpdate()

Me.cmbItem.Requery


End Sub
***********************************************


Hope this helps

 

[cantech2]

Dear Bluekestrel,

your solution was wonderful. I implemented the combo box relationships and it worked. I was able to do this for upto 4 subcategory levels.

Thanks and God bless you,
Cantech2

 

[jennifer21277]

I am also attempting to link 2 combo boxes. I have followed the instructions above, but when I select an option in the first combo box, before I am able to select a corresponding option in the second combo box, I am prompted to enter a parameter value. How do I link the combo boxes without having to enter a parameter value?

 

[bluekestrel]

Jennifer

I suspect that you have a problem with the query - as you select the first combo box the After Update Event triggers the query to run which the contents of the second combo box are based on.

Please check the query and in particular the [Forms]![frmYourForm]![cmbCategoryList]

Ensuring that this is placed in the criteria section of the query under the Category column. This has the effect of filtering the second combo box based on the selection of the first.

Check all your form names carefully as the slightest deviation will cause this failure.

Regards

 

[hysonmb]

I just wanted to say that I've been looking for a good answer for this question off and on over the past few days and this solution did it. Thanks!

 

[jennifer21277]

bluekestrel,

Thanks for responding so quickly! From what I can see, I have done all of the steps you outlined. I have 2 tables:
Plan Types (Category)-this table has one field and 2 records, DB and HW; and Plan Events (Sub Category)-this table has 2 fields, one of which is Plan Types, and 38 records. Of the 38, 19 are DB Plan Events and 19 are HW Plan Events. In the query, I linked the two tables using the plan types. This is what I have in the criteria section under "Plan Types":

[Forms]![Case Form]![cmbPLAN TYPE]

Here is what I have in my AfterUpdate event for Plan Type:

Private Sub cmbPLAN_TYPE_AfterUpdate()
Me.cmbEVENT.Requery
End Sub

This still prompts me to enter a parameter. Can you see if I am doing something incorrectly?

 

[lespaul]

What does the query look like that your are Requery-ing (in the cmbEVENT)?

 

[bluekestrel]

Jennifer

The tables and query sound fine although you don't need to include both tables in the query - define the link in relationships.

Just go to Tools - Relationships and make sure both tables are in there and linked on the Plan Type.

In your query then you only need the one table which is Plan Events - put the two fields in to the query and put the "[Forms]![Case Form]![cmbPLAN TYPE]" criteria under the Plan Type Field.

Your 2nd combo box should be built on this query and you can either display one or both fields in the results.

Areas to check carefully are the combo box names - If you used the wizard to create the combo box although you give it a name this is really the label - you have to go in to the properties of the combo box and select the tab "other" and look at the name at the top - this is by default something like combo0 - this obviously must match your query and it may be that you are referencing the label name and not the combo box name which is why it is asking you for more information.
eg "[Forms]![Case Form]![combo0]"

Also check the query name is referenced correctly in the after update event field - it could just be a typo

Try also to avoid using spaces in object names as this can cause problems Try capitalisation instead such as frmCaseForm

If this doesn't solve things give me another shout and I will email you a working example that you can adapt to suit. I see no reason why it won't though -

Regards

Blue Kestrel

 

[tizwaz]

Bluekestrel
I too am trying to link 2 combo boxes. I've followed your instructions and it is working to a point. When I change the selection of category in the first combo box the list of subcategories changes in the second combo box. However the value showing in the second combo box is the value previously selected (ie one of the choices that belongs with the category previuosly selected)and I need to click the drop down arrow to see the correct choices.
Am I doing something wrong or is this what you intend? I need the second combo box to show either the first of the subcategory choices to correspond with my new category choice or a blank box.
Sorry if this sounds a bit garbled - hope you know what I mean.

 

[tizwaz]

It's ok I've got it now. Added Me.cmbItem.Value = Null to the after update event of cmbCategoryList. 2nd combo box now blank when I change the category in the 1st combo box. This should do it.

 

[pepitxispi]

What about doing this for a website? I am working with CodeCharge Studio, but I am rookie and although I have managed to run the trick in access, I am still figuring out how to do it in a web site...

Any help will be extremely welcome!!!!

Thanks

 

[Greenfuji]

Blue Kestrel

Can I have a copy of a working form with combo boxes?
email: greenfuji@hotmail.com

My after update is fine, I opted for the = Null like someone else above. I cant seem to get the query right though.