I have 3 Tables
tblInvestments table has a field titled “Geographic Location”, which is using a Lookup, List Box, Table/Query, with tblGeoLocal as data source to offer limited options for data selection.
When the user selects the “Geographic Location” List Box option in tblInvestments, the user can choose from the following options: “US”, “Asia”, “Europe” (again, coming from tblGeoLocal)
The tblInvestments table also has a field titled “Region”…which is right next to field “Geographic Location”
The tblUSReg is where the “US” region options are held…ie (West, South, Northwest)
The question is…how do I go about populating the “Region” field in tblInvesents with options such as “West, South, And Northwest” (as an example) when the user chooses “US” from the tblInvestments “Geographic Location” List Box. My goal is to make sure the user can only select options from a predetermined list.
How about this:
Create a table called tbllookup with two fields: Key and Value.
the table would contain something like:
The Geo Locations Combo has a Value List source type with a Row Source of "US";1;"Asia";2
the col count is 2 and bound col of 2
The click event for Geo Locs looks something like:
Me.cboRegion.rowsource = "Select Value from tblLookup where key = " & Me.cboGeographicLocation.Value
which populates the selection list.
you will have to handle enabling disabling the combo boxes so users can select in the appropriate order (of course) but this should get you started....