Hi all,

I wish I could get my head around access.Probably need to take a formal
course. Anyway, I have an A-2007 database of plant information for several
sites that works OK but could be better. Basically we go to sites and
document the vegetation found there.



I enter data into Plant_list table from a form called Data_Entry_Form that
loads location and date from dropdowns, and a subform that loads Family from
a dropdown, Genus and species from another dropdown, and common names as
text, and other information such as whether or not a sample was collected as
check boxes.



It occurred to me that I spend too much time looking up Family and common
names that could autofill. The master table that I pull the Genus/species
names from has common names in it anyway, and I can add one for Family. But
after a day of googling and playing with DLookup I can't get anything but
errors.



What I'd like to do is when I chose the Name Authority from the form's
dropdown, to have it autofill the common name data into Plant_List table. It
should pull from the field Common Name in the table Name_Authority and load
that into Common Name in the Plant_List table based on the Genus and species
name I chose (which is actually linked Name_Authority in Plant_List to ID in
the Names table. I used the dropdown wizard). I thought perhaps having the
saem name might confuse things so I made another copy and changed the name
in one table to Names_Authorities but to no avail. It must be something
simple I'm doing wrong but I sure can't find it. Thanks for your time, even
if to say use what you have and move on with your life!

On Thu, 2 Dec 2010 18:36:20 -0500, "TimK" <timkozz (AT) cfl (DOT) rr.com> wrote:

Hi TimK,
It does indeed sound like you're on the wrong track. Long before you
worry about data entry forms, you have to worry about the database
design. This is the hardest part to get right for a beginner, and
without a correct db design all you build on top of it is eh <insert
eufemism here>.

If you have a dropdown to choose the scientific name from, and you
also want to show the common name, you can add that as an extra column
in the dropdown (let's call it myPlantsDropDown):
select PlantID, ScienceName, CommonName from tblPlants
The dropdown will have 3 columns, and the columnwidth property would
be set to 0;1;1.
If you want to display the CommonName in another control, you can set
that control's ControlSource to:
=myPlantsDropDown.Column(2)
This will pick up the value at index=2 (3rd column) and display it.

I hope this helps a bit. Consider hiring a seasoned developer as your
buddy.



Microsoft Access MVP

On 03/12/2010 04:58:48, Tom van Stiphout wrote:
I Agree with Tom

I know nothing about plants - or anything else fr that matter, but I guess
you need

Table of Family I'm guessing but would this be something like Ranunculaceae
FamilyID AutoNumber
FamilyName Text Indexed(No Duplicates)
FamilyCommonName Text Buttercup family

Table of Genus Ranunculus
GenusID AutoNumber
GenusName Text Indexed(No Duplicates)
FamilyID Number Long with a relationship to the Family Table
GenusCommonName Text Buttercup

Table of Species Ranunculus flammula
SpeciesID Autonumber
Speciesname Text
GenusID Number Long with a relationship to the Genus Table
SpeciesCommonName Text Lesser Spearwort

Table of Sites London Hyde park
SiteID AutoNumber
SiteName Text Indexed(No Duplicates)

Table JoinSiteGenus
SiteID Number Long Joint Key
GenusID Number Long Joint Key
SampleDate
SamplerName

So by choosing Lesser Spearwort (Ranunculus) flammula automatically, working
backwards down the chain, we know that it a Buttercup in the Buttercup
Family.

I'm guessing that the easiest way of data entry would be to have a main form
for the survey site and a continuous subform on it with dropdowns where you
select the Species. It is possible to use the same dropdown to select either
the common name or the scientific name.

The JoinSiteGenus table allows you to ask questions like "Where and when were
buttercups found?"

HTH

Phil

Thanks to you both for your replies. In looking at your advice with the
database opened, my real main problem occurred to me, which it no doubt
would have done to you both had I done a better job explaining it. Why would
I want to load information into the plant list table that already exists in
records for the names and authorities table. All I should be loading is the
primary key that corresponds to the name in the dropdown. So I eliminated
the extra information from the subform, put together a query and then a
number of different reports based on it to make sure, and low and behold, it's
all there just fine.


I do have to respond to one point in my defense though, that is the notion
that these sorts of issues arise because somehow users aren't able to think
ahead. In our fieldwork (ecological research) we use statistical models with
multivariate regression techniques, sample designs, and logistical plans, in
all of which knowing what is going on before the design is at least as
important as in database work. Unfortunately the real world is not nearly as
tidy as textbooks would have us believe. Often someone changes the scope of
the work, or another notices some new bit of data that should be captured,
or things just turn out to be too difficult to realize in the field, and
things change. It's not because we lack the foresight necessary to put the
database together before the job begins. In colloquial terms it's known as
mission creep, and it is pervasive.

</soapbox>



Fortunately this work is only a few weeks old so it was no problem to
rebuild the db and load the information into it. Again, thanks Tom and Phil
for taking the time to reply. I appreciate it. If you're ever in central
Florida and need a plant or critter identified, let me know...