Forum Discussion
Populating Comb Box With Multiple Values
I have an Access table with a Regions field and a States Field. The States Field is populated with one to twenty state abbreviations depending on the region. Using VBA, I'd like to populate the Regions combo box with the appropriate regions depending on what state is chosen in the State combo box. The problem I have is that some states are included in more than one region. For example, 'WY' is part of the Great Basin, Northern Rockies, and Rocky Mountain regions. 'NV' is part of the Great Basin and Northern California regions. In order for multiple regions to be populated in my Regions combo box (ie. Great Basin, Northern Rockies, and Rocky Mountain if a user selects 'WY' in the States combo box), is there some sort of VBA dictionary I could set up (much like in Python) to iterate through and select out the appropriate regions? Or if that's not possible, would I have to set up some sort of array and use a For Loop to select out the appropriate regions?
| Region_Name | States |
|---|---|
| Alaska | AK |
| Eastern Area | ME, NH, VT, MA, CT, RI, NY, NJ, DE, PA, MD, WV, OH, MI, IN, IL, WI, MN, IA, MO |
| Great Basin | AZ, ID, NV, UT, WY |
| Northern Rockies | ID, MT, ND, SD, WY |
| Northwest | WA, OR, HI |
| Northern California | CA, NV |
| Southern California | CA |
| Rocky Mountain | CO, KS, NE, SD, WY |
| Southern Area | TX, OK, AR, LA, MS, AL, TN, KY, VA, NC, SC, GA, FL, PR |
| Southwest | AZ, NM |
Any advice would be greatly appreciated.
3 Replies
Your design is not right. Never store multiple values in one field. You need three tables here: Region, State and StateInRegion. Like:
Part of your data will look like:
Ok, thank you very much!
there are only 58 states so no need to super normalized your table, 1 table is enough.
open state table then open form1.
