Cascading Combo Boxes and keeping Relational data

[reb0101]

Ok guys, I am making myself crazy here and I know there has to be an easy way on this but I just can’t find it.
I have finally gotten the relationship aspects of a Database down. Got that part finally working pretty well.
But as you know, that is only showing me existing data in tables that I want to ‘extract’ and put into a ‘master’ table for lack of a better term.
Here’s an example.
User selects Region which then isolates down to Country then down to City then down to Port then down to Carrier.
Obviously there are several countries to choose from per region, cities per country, etc.
Once they make these selections I need them to cascade from one combo or list box to the other (REGION to COUNTRY to CITY).
I have not had great success with that so that’s one issue.
But ultimately I want them to be able to make these selections after they drill down and then by clicking on them or whatever, insert that record into a master table.
I can insert a combo box, have it pull values from say REGION and then insert that value into the REGION field in the master table, but that’s not really helping me to filter the COUNTRY for the region I pic in the next combo box over.
Any relatively simple ideas?

 

[boblarson]

The thing to note is that you do not need to have all fields in the table you are filling. If you have a table which stores the City and which Region and Country it is in, You only need to also have a UniqueID field in that city/region/country table which then can be used to select and you store that ONE FIELD (the ID field) in your master table. You can then, in a query, retrieve the information that all goes together. You can even have all of the fields in your final combo's row source which then you can use a formula to display (DISPLAY NOT STORE) the values of city, region and country.

So if your record source for city is Something like this:

SELECT MyID, CityName, RegionName, CountryName
FROM tblCountryRegionCities
WHERE RegionName = [Forms]![MyFormNameHere]!CboRegion
ORDER BY CityName

then for the city you would have your combo show the second column and hide the rest

ColumnCount property - 4
ColumnWidth property - 0";2";0";0"
(use cm instead of " if that is your measurements)

And then to refer to the region and country to DISPLAY their values on their text boxes on the form:

For Region:
=[cboCity].[Column](2)

For Country
=[cboCity].[Column](3)


The columns are ZERO BASED so 2 represents the THIRD column and 3 represents the FOURTH column.


I hope that helps explain it.