Cascading comboboxes help

[arashii]

Hi guys. I need some help troubleshooting my cascading combos... that won't cascade .

Basically the setup is:

Combo 1 : CATEGORY
Combo 2 : ItemNames that fall into selected CATEGORY

My 2 combos get populated OK, but when I add in my Criteria in the Query Builder, Combo 2 doesn't show any ItemNames anymore . I'm not sure what the problem is because I was able to do it before, but when I tried to replicate it (about a million times I tried ~_~ ), it won't work any longer.

I'm not sure if I'm typing my criteria in wrong, I tried 2 types of criteria already:

[Forms]![tbl_Inventory]![cbo_Category]
[Forms]![tbl_Inventory]![cbo_Category].[value]

and I have a requery statement in my Afterupdate for Combo 1.

Private Sub cbo_Category_AfterUpdate()
Me.cbo_ItemName.Requery
End Sub

Can someone be kind enough to take a peek please?

Thank you so so much!

 

[boblarson]

Okay, you have a few problems:

1. your InventoryID should be an autonumber
2. You should be storing the ItemID and not Item Description in the Inventory Table.
3. You were trying to set the second combo based on the description and not the ID so it wasn't seeing anything.

I fixed it all and if you look at the stuff inside (including relationships) you should see several of the changes.

It all should work now like you want.

 

[arashii]

Hi bob,

Thanks for the time .

1. For InventoryID, I left it at not-autonumbered first because I keep entering trial records in the datasheet, and I want to be able to start at number 1 when I ultimately use it to enter real records . I had it autonumbered before but then when I delete the trial records, the new records start at where the deleted ones left off.

2. Should I really be storing the ID instead of the Name? I stored the Name because I want to create a Report based on the form... and I'm not quite sure if I can create something like:

Date | Item Name | Quantity

if I have the ItemID in the table instead of Name. But I will try.

3. Sorry, I'm not sure I get how I was setting the combo based on Description and not ID. Is this in my Query? Or because of the wrong storing of Description (#2?)

Also, I tried using the form you edited to enter a new record. But since it is the Description that is in the dropdown combo, but the ItemID in the table, it's not entering the data. It says 'Control can't be edited.' How can I have the Description in the combo but have it enter the ID in the table?

Thanks again so much~!

 

[boblarson]

1. If you are associating any meaning to the autonumber you should not. It only guarantees a unique number and if you need something with meaning then you need to do keep the current autonumber and create a different method to assign a number (maybe with DMAX - look for posts here on that)

2. You should store the number as storing text means that you will potentially have to go change data in many places if the text changes. Plus it violates normalization rules. You can get the name by using a QUERY for the report which can link between tables to get all the associated data for each record.

3. You were trying to set the id number of the category as the criteria of the description so it wasn't looking at the right stuff.

lso, I tried using the form you edited to enter a new record. But since it is the Description that is in the dropdown combo, but the ItemID in the table, it's not entering the data. It says 'Control can't be edited.' How can I have the Description in the combo but have it enter the ID in the table?

I must have forgotten to save the form after my changes.

Just change the bound field in the item combo to ItemID instead of description.

 

[arashii]

Hi again ,

1. No, I'm not attaching any meaning to the autonumber. Actually, I just put in an ID because there needs to be a PK for the record ~_~.

2. Yeah, I get it now ! Thanks for pointing out the normalization thing. I guess I'm still not used to seeing tables as all IDs , so I automatically put in the Description for a more 'understandable' table .

3. I finally get what you mean lol ~_~; . I'm just not too sure I understand the logic of how to go about doing this though.

SELECT tbl_ItemCatalog.ItemID, tbl_ItemCatalog.ItemName, tbl_ItemCatalog.CategoryID FROM tbl_Category INNER JOIN tbl_ItemCatalog ON tbl_Category.CatID=tbl_ItemCatalog.CategoryID WHERE (((tbl_ItemCatalog.CategoryID)=Forms!tbl_Inventory!cbo_Category));

For the WHERE statement, how can it equate tbl_ItemCatalog.CategoryID)=Forms!tbl_Inventory!cbo_Category? I guess this is where I made the mistake. I thought since cbo_Category returns the Category Description and not the ID, then I should select a field (in this case Category) to make the match (e.g. "Drinks" = "Drinks"). In the current statement, wouldn't that be something like (1 = "Drinks")? Is there a general rule here that I should always use the PK field for my filter criteria?

Thanks for the explanations, I really appreciate it .

And it's amazing how Access automatically convers the Description to the ID when storing the value of the combo...

 

[BarryMK]

Just a small point but if you delete all the test records from your tables and then compact the database it will reset all autonumbers to 0.