Auto update of 1-to-many relationship

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

Gary Schuldt

I have a running database to which I want to add some new functionality, and
I can't figure out the best way to do it.

It's a (botanical) plant database. A Plant's "name" is a compound field
that starts with its GenusName followed by a variable number of sub-generic
qualifiers (subgenus, species, etc.). These fields are in the tblPlant
table, which is updated via frmPlant.

Every Plant must have at least a Genus component to its name, and many
Plants can belong to the same Genus.

I want to add a tblGenus (containing GenusName plus other stuff) so I can
record information at the Genus level. The relationship between tblGenus
and tblPlant is one-to-many. I know how to construct that much.

This is what I need help with: When I add a new record to tblPlant, I want
to automatically create a new record in tblGenus if the tblPlant.GenusName
is a new one (i.e., doesn't already exist). I want this done behind the
scenes with no confirmation popup.

I'm not sure if there is some way to get Access to do that automatically, or
if I have to write some code for it, and, if the latter, what event to trap.

Gary
 
Thanks, Allen.

I did plan on making the GenusName the PK of the tblGenus, so I'm OK there.
And thanks for the URL for the details.

Right now the value for txtGenusName in frmPlant is pushed from an
antecedent search form (if there is a hit on an existing botanical name). I
hope that that code will still work if I convert the field from txt to cbx.

Gary
 
If you are programmatically pushing the value into the combo from somewhere
else, you could:
- Open a recordset into tblGenus to see if the entry exists, and if not,
- Append the value to tblGenus, and
- Requery the combo so it gets the new value, and then
- push the value in.

The code does show how to open the recordset and append the value to the
table.
 
The preceding form is just a search form and only pushes a value for
frmPlant.txtGenusName when the search criteria yield a hit. In the no-hit
case, the search form just opens the frmPlant and sets the focus to
GenusName so the user can type a new value in at that point.

Gary
 
Back
Top