How do I use a combo box in a main form to access a subform row?

[Gordon Padwick]

I have two tables in Access 2003.

The first table, that contains common names of plants, has two columns: CommonNameID (primary key) and Common (common name).

The second table, that contains scientific names of plants, has six columns: PlantID (primary key) CommonNameID (used to link to the first table), Family, Genus, Species, and Variety (components of scientific names).


Background information: each plant is uniquely identified by a scientific name (controlled by an international body). Many plants are also known by one or more common names over which there is no control. Thus, an individual plant can have various common names and an individual common name can refer to several different plants.

The form I want to create enables a user to select a common name and display one or more corresponding scientific names.

After defining an appropriate relationship between the two tables, I used the Forms Wizard to create the form with its subform.

The main form contains a text box that displays a common name from the first table. The subform contains text boxes that displays the components of the corresponding scientific names.

A user can use the navigation buttons at the bottom of the main form to select common names, one after the other, each time seeing the appropriate scientific names in the subform. This works okay.

The problem is that the first table contains 14,746 common names, so selecting a specific common name is tedious.

I want to replace the text box in the main form that displays common names (selected by navigation buttons) with a combo box with the Auto Expand property enabled so that a user can easily choose a common name. That’s where I have a problem.

How should I set the Link Child Fields and Link Master Fields properties on the subform?

I’ll appreciate any suggestions about solving this problem.

[nico5038]

Make sure that the Wizard is active (in general he will be) and add a new combobox. Select the (last) option to search for a value and Access will create the combo you need.

Nic;o)

[OldBirdman]

I agree, the Access navigation buttons become problematic after about 20 records and virtually impossible over 100 records. If your only
concern is to add a combobox to the form, Nico's ideas are a start.

If you are trying to build a reference tool, and operator efficiency is very important to you, I think you need to start over.
(1) The tables are not normalized
(2) A one-to-many relationship, as currently defined will cause you problems as you develop this project
(3) A combobox requires switching from mouse to keyboard, or if only mouse, at least 1 more click than a listbox
(4) If these are species/varieties produced by genetic engineering, a plant could be in 2 or more species, or even 2 families. I read that animal genes can be inserted into plants, so a variety could be in 2 kingdoms. I know, one gene does not make a plant into a half animal, but...

To explain:
(1) "The second table, which contains scientific names of plants..." This should be 3 or 4 (or more) tables. tblFamily would have 2 fields, FamilyID(PK) and FamilyName. The table tblGenus would have 3 fields, GenusID(PK), GenusFamilyID(FK), and GenusName. tblVariety could combine with tblSpecies to treat a variety as a unit if this deals with natural species, but needs 2 tables if dealing with developed varieties such as roses.
(2) A plant can have more than one 'common name'. Quercus velutina — Black Oak or Eastern Black Oak or Dyer's Oak in family = Fagaceae. This makes the relationship of this project a many-to-many relationship.
(3) "...selecting a specific common name is tedious." So is continually clicking the 'dropdown' arrow of a combobox, which then ALWAYS displays the list from the currently select item. If the next selection is alphabetically before the current selection, this requires extra clicks. I would guess that this project will not search for next species in alphabetic order.
(3a) Listbox implies 'Pick One' and no typing is allowed. No operator error. Additions would be handled elsewhere.
(4) Whether listbox or combobox, the subform can easily be filtered by the row selected. The common name key is in a hidden column, and the OnClick event sets a filter in the subform. The effort here should be directed to the list/combo box. Just to use the Black Oak example, would this be "Oak, Eastern Black", "Oak, Black", "Black Oak, Eastern", or "Eastern Black Oak"? It would also have to appear in the list/combo as "Oak, "Dyer's" or "Dyer's Oak". Dyer's Oak is also Gall Oak (Q. lusitanica). Gets worse if searching for 'Live Oak', which may or may not have 'Live' in name, but does in common usage.

OldBirdman

[Gordon Padwick]

Thanks for the two suggestions.
I don't quite understand what Nic is suggesting, perhaps he can elaborate.
I assume he is referring to the Forms Wizard. When using that wizard, the new form/subform doesn't appear until I click Finish in the wizard, so how can I add a combo box while the wizard is still open?
Also, Nic refers to the "last option to search for a value." I don't see such an option in the forms wizard. Perhaps this is something available in a later version of Access than I'm using (Access 2003).
I'll greatly appreciate further help concerning these matters.
My thanks also to OldBirdman. I didn't expect a reply from someone who has such a detailed understanding of plant common and scientific names. After I get this matter of linking from a combo box in the main form to a record in the subform under control, I'll look into OldBirdman's suggestions for restructuring the database. I prefer to use a combo box rather than a list box in the main form because the Auto Expand property of the combo box simplifies locating a specific common name.
Gordon