autofilling multiple fields from lookup table in another table

[Guest]

I have a database with multiple tables. In one table (tbl_species_list), I
have a master list of wildlife information, including unique Species and
Common Names. In another table (tbl_wildlife_records), I enter data
collected on wildlife captures, including Species and Common names Looked Up
from the tbl_species_list.

My problem is this: I need the tbl_wildlife_records Species and Common
names fields to automatically fill in if the other is completed. I need both
names to be stored in the tbl_wildlife_records.

Right now, I have combo boxes for each field that display both names (by
looking up the tbl_species_list), but only one name is filled in when a
choice is made from the menu. I have large numbers of records that must be
added, so it would help my admin be more efficient if fields were filled in
automatically.

I know that it is not good database design to repeat Species and Common
names in two tables, but some datasheets list common name and some list
species name, and the admin must be able to enter either and have the other
fill-in.

I've searched for answers on this topic, but no one gives advice on how to
autofillin fields that are redundantly saved, like I need.

Thanks.

 

[Guest]

Hi, Kraymond.

First, a question. From your description of your tbl_species_list, I'm
assuming its structure is something like:

SpeciesListID Autonumber (Primary Key)
Species Text
CommonName Text
...other fields that describe this particular animal...

If this is so, does a common name refer to only one animal? For example, if
several species go by the name "Panther", how would a user know which panther
to select?

If it does in fact refer to only a given animal, you don't want to store
EITHER of these fields in your wildlife records table. All you need (and
want) to store is the primary key that identifies that record (the "foreign"
key). This is what a relational database is--you get access to related
tables' fields through foreign keys.

To permit your users to select either name and *display* both regardless of
which is filled in, simply use 2 combo boxes that select the primary key and
the appropriate text field, set the first Column Width to 0", set the Bound
Column to 1, and bind BOTH to the NUMERIC field in the wildlife records table
that corresponds to the primary key of SpeciesList. Then both fields will
display as you wish.

SELECT SpeciesList.ID, SpeciesList.Species FROM SpeciesList;
SELECT SpeciesList.ID, SpeciesList.CommonName FROM SpeciesList;

Hope that helps.
Sprinks

 

[SAlford]

I have exactly the same situation only with plants.
I have a combo box set up for each species and common name and I want to be
able to click on one combo box, pick the common name for the plant and have
it automatically fill in the species combo box also.
I have a table set up with a field for "species" and a field for
"commonname".
Sprinks' answer is good but since I am a beginner at this, could someone
please be VERY specific in their answer, it would be very much appreciated.
Step-by-step sort of thing.

Steve