autofill in form field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have three fields: 'category', 'common name' and 'sci name', each
with a combo box. The correct combo box for common name appears, dependent
on the selection in the category combo box. I have 10 different categories,
and 10 differen t tables of common names as options for the combo box. I
would like the sci name to fill in automatically when I enter the common name.
 
I currently have three fields: 'category', 'common name' and 'sci name', each
with a combo box. The correct combo box for common name appears, dependent
on the selection in the category combo box. I have 10 different categories,
and 10 differen t tables of common names as options for the combo box. I
would like the sci name to fill in automatically when I enter the common name.
Click to expand...

Why?

Storing data redundantly is NEVER a good idea. Your taxonomy table
should contain the common name and the scientific name, and (probably)
some unique identifier for the species - and all your other tables
should have ONLY one of these, a foreign key field. Storing both names
in your target table is simply bad design.

Also, if you have ten tables of names, one for each category, you need
to reconsider. What if you add an eleventh category, or conflate two
categories into one!? Much better would be to have ONE organism table
with these three fields.

John W. Vinson[MVP]
 
Well I disagree. We have a listing of traps with the species of animal we
catch in each trap. The user has the combo box to select an animal name, so
that they don't need to retype each time. It makes no sense to me not to
have all the pertinent information (common name and scientific name) in one
place. Perhaps it's 'bad design' from a programmer's point of view, but if
it doesn't give the user what they need then it is pointless.

I guess I will look elsewhere for help.
 
What you store in the table and what you display on the form are (or at
least should be) two separate things.

The combo box should get its information from whatever table contains the
details of species caught. Only the primary key for that table should be
stored in the "data" table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Well I disagree. We have a listing of traps with the species of animal we
catch in each trap. The user has the combo box to select an animal name, so
that they don't need to retype each time. It makes no sense to me not to
have all the pertinent information (common name and scientific name) in one
place. Perhaps it's 'bad design' from a programmer's point of view, but if
it doesn't give the user what they need then it is pointless.
Click to expand...

I apologize. I was being excessively didactic.

Of course, the user should not have to reenter data! But if you are
storing the species name AND the common name in the trapping data
table for every record, you are both wasting space and risking update
anomalies. Suppose you find that a very commonly trapped species was
entered into the Species table with its Latin name misspelled, or some
taxonomist has revised the family and the species now has a new name.

If you store only the unique species identifier in the trapping table,
you now have only ONE record to correct - the scientific name field in
the Species table. If you copy the scientific name into the Trapping
table, then you must track down and correct every single trapping
record involving that species. You say, quite correctly, "have all the
pertinent information (common name and scientific name) in one place"
- and that is *exactly* what I'm proposing. The one place is the
Species table.

What the user SEES is the common name and the Latin name. What's
stored in the trapping data table is the unique key to the species.
That way both the user and the computer can be satisfied.


John W. Vinson[MVP]
 
I understand. The problem is that not all our species (and scientific names)
are in the combo box, so the user needs to have the option to add a new name
(common and scientific). That's why both fields are present for the data
table. They would use the combo box for the regular species and the
scientific name would autofill, but they could also type in both fields. I
don't want all the new names to be added to the combo box either, as it would
fill up with rare species and cause the user to have to scroll through a long
list. If you have a better (and not too complicated!) way, I'll be glad to
hear it. Meanwhile, someone posted a similar question and got an answer, so
I now have the autofill in place.
 
I understand. The problem is that not all our species (and scientific names)
are in the combo box, so the user needs to have the option to add a new name
(common and scientific). That's why both fields are present for the data
table. They would use the combo box for the regular species and the
scientific name would autofill, but they could also type in both fields. I
don't want all the new names to be added to the combo box either, as it would
fill up with rare species and cause the user to have to scroll through a long
list. If you have a better (and not too complicated!) way, I'll be glad to
hear it. Meanwhile, someone posted a similar question and got an answer, so
I now have the autofill in place.
Click to expand...

Thank you for the explanation; I now understand your reasoning. Again,
my apologies for being so high-handed.

John W. Vinson[MVP]
 
Back
Top