Please HELP!! beginners problem with the lookup table

[Guest]

Hi I have created a table to collect personal information such as name, age
of client etc, 1 of my fields is to find out the place they would most like
to visit abroad, i want to create a lookup table where by i can add submeunus
for example, when they come to fill in the information, the field reads ideal
holiday destination, the drop down menu lists USA & CANADA, CARIBBEAN, ASIA
etc and off of these headings are Florida, St Lucia, China etc is there any
way i can do this? Thanks in advance Mas

 

[tina]

recommend two lookup tables, as

tblRegions
RegionID (primary key)
RegionName

tblPlaces
PlaceID (primary key)
PlaceRegion (foreign key from tblRegions)
PlaceName

tblRegions will look like

RegionID RegionName
1 USA & CANADA
2 CARIBBEAN
3 ASIA

tblPlaces will look like

PlaceID PlaceRegion PlaceName
1 1 Florida
2 1 Toronto
3 2 St Lucia
4 3 China
5 3 Japan
6 3 Thailand

do the data entry in a form bound to the personal information table, not
directly in the table itself. in the form, create an unbound combobox
control with tblRegions as the RowSource. create another combobox control,
bound to the appropriate "place to visit" field in the personal information
table. you can restrict the droplist of the "place" combo box, based on the
region selected in the first combo box, as

SELECT PlaceID, PlaceName FROM tblPlaces WHERE PlaceRegion =
Forms!FormName!NameOfRegionCombobox

requery the "place" combo box control, on the "region" control's AfterUpdate
event, as
Me!NameOfPlaceCombobox.Requery

hth

 

[Guest]

You'll find a demo of the sort of thing Tina describes at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


The demo actually has three levels of geographical location, the English
local administrative units of parish, District and County using my local area
as an example, but the principle of data entry via correlated combo boxes is
exactly the same. In your cast the 'single form view using correlated combo
boxes' approach from the options available sounds the most suitable.

While I'd be inclined to go withTina's logical model, and my demo reflects
this, there is an alternative approach:

Regions
--------------------------------
RegionName (primary key)

Places
-------------------------------
RegionName (part of primary key, AND foreign key referencing Regions)
PlaceName (part of primary key)

The Clients table would now include both PlaceName and RegionName columns as
a composite foreign key and the relationship between these columns would be
on both columns. Its imperative with this model that referential integrity
and cascade updates between Clients and Places, and Places and Regions are
enforced.

The latter model is a more classical one, but you might find the first one,
with single column keys easier to implement. The latter one does make it
very simple to set up the correlated combo boxes on the form, however, as you
simply need two bound combo boxes, cboRegions and cboPlaces, one with a
RowSource:

SELECT RegionName
FROM Regions
ORDER BY RegionName;

The other with a RowSource:

SELECT PlaceName
FROM Places
WHERE RegionName = Forms!YourForm!cboRegions
ORDER BY PlaceName;

And in the form's Current event procedure and the cboRegions combo box's
AfterUpdate event procedure requery the places combo box with:

Me.cboPlaces.Requery

Ken Sheridan
Stafford, England