Populating Combo Box Based On User Selection From Another Combo Box

[VSCurtis]

Hi Guys,



I could use some help. My Access is a bit rusty and I can't remember how to populate a combo box based on a selection from another combo box. The basic flow of events is I make a selection from the Card Categories combo box and I want to populate the Card Attributes combo box with valid Card Attribute choices based on the Card Category I select. The database table structure is as follows. I have a Primary table Card Categories which has Category ID and Description and a second Primary table Card Attributes which also has ID and Description and an Icon field which contains a graphic image that is associated with each attribute. I then have a Junction table which brings together the Category ID and Attribute ID since it is possible for more than one Card Category to have the same Attribute. I realize that I could simplify this structure by including the Category ID in the Card Attributes table and using both IDs as the primary key, but that means carrying multiple copies of the Icon image for those Card Categories which share the same Card Attributes and I would rather not do that because I am already pushing the limit of size of my database because of the number of BLOB images in my database. I'm already at 1.84 GB and haven't even started filling in record data. I hope this extra information is helpful. I have included it so you can better understand the structure of my database.

Any assistance you can provide in helping to refresh my memory would be appreciated.

[Bob Fitz]

Search google for Cascading Combo Boxes.

Here's one of many I found: http://www.baldyweb.com/CascadingCombo.htm

[VSCurtis]

Thanks Bob, I'll give this a look. Sorry to be a nuisance. I'm sure once I look at a working example it will come back to me.

[Bob Fitz]

Certainly not a nuisance. If I thought that I wouldn't be here

[VSCurtis]

Bob,

Still struggling. Every example I find uses the visible value in the combo box. This isn't how my database is set up. The Card Category Combo Box is two columns, ID and Description. ID is hidden and this is the value I need for the WHERE clause of the Card Attribute. I'm stuck on how to access the key value in the Card Category Combo. Usually it's the value property but I don't see it listed in Expression Builder.

[VSCurtis]

Bob,

I got it working. Thanks for pointing me in the right direction. I was making it harder than it had to be. Overthinking it, I guess. I'm out of practice coding in Access. I mainly use it as a database design tool. I prototype in Access then usually upsize after I am happy with the final design.

I do have a nice little routine that auto generates keys. I personally hate Autonumber keys in Access my database design instructor in college would call them "Magic Numbers". Keys with no connection to the data, So I decided to create my own version. My key gen routine takes the first letter from a field in the table that has a unique value and then adds a formatted number. My key field is four positions so the key looks like A001. It continues to increment up to the max value and when that is reached it tacks on another A so the key now looks like AA01. When AA99 is reached it rolls over to AB and the process continues until all possible unique values have been used. The routine generates 3,800+ unique keys for each letter of the alphabet give a key field of Text(4). Do you think anybody here would be interested in this code? Everybody here has been very helpful and I haven't had to deal with arrogant idiots who would rather nit pick and find fault rather than address the purpose of the post. I would like to do something to return the favor.