Form - Primary & Foreign key

[Guest]

I'm just learning Access. I need to produce a from which will allow the
imput of data for several tables. This is for the horticultural area.
One table is "Genus" another "Species" and the third "Nursery" All have
primary keys (ID) and Species and Nursery use the primary key of Genus and
Species respectively to allow for future searches. The tables are joined in
a one to many relationship Genus - Species - Nursery. My problem. When I
create the table and add the fields which need to be filled out I am blocked
from entering the necessary information for the second record. This is due
to the primary key not allowing duplicate records. How can I have the form
show "genusname" on a second form while entering a new "speciesname" and show
both "genus" and "species" while entering information on which nursery grows
this type of plant?

 

[kingston via AccessMonster.com]

I'm going to assume you have something like the following for your data
structure:

tblGenus {GenusID - PK, ...}
tblSpecies {SpeciesID - PK, GenusID - FK (many), ...}
tblNursery {NurseryID - PK, GenusID - FK (many), SpeciesID - FK (many), ...}

Create a form for tblNursery. Create a form for tblSpecies and drop the form
for Nursery in it while in design mode. Follow the subform wizard and link
the two forms via GenusID and SpeciesID (or just SpeciesID depending on your
needs). Now create a form for tblGenus and drop the Species form (which has
the Nursery subform) in it while in design mode. Link the two forms via
GenusID. You should end up with a main form with a sub form that has a sub
form. If you wanted to input two species for the same genus, you'd do it in
the middle sub form. HTH

 

[Guest]

Thank you kingston! I will give your suggestion a try and see if I can make
it work.