0

I'm new to MS Access. I have a very simple plant taxonomy database that I am using to try to learn and understand the program. The database has three tables shown below:

enter image description here

The relationships between these tables is as below:

enter image description here

Referential integrity is enforced, and related fields are set to cascade updates. I then built the below form using the Form Wizard:

enter image description here

I chose the below fields in the wizard:

enter image description here

Perhaps contrary to how some might choose to build such a database, I want to see one record per Species and don't therefore want to use subforms. Hence I chose the below option, and was happy with how the resulting form was laid out.

enter image description here

When I attempt to add new records via this form, I am able to enter data for Families, but attempting to enter data for Genera or Species gives a "cannot add record(s); join key of table...not in recordset" error.

What have I done wrong in setting up these tables and form?

0

In order to retain the referential integrity of the database, when adding a record to, say, the tblSpecies table, the GenusID field must contain an ID which is already present in the tblGenus table, else the record in the tblSpecies table will be an orphan, thus breaking the referential integrity of the database.

Similarly, when adding records to the tblGenus table, the foreign key (FamilyID) must correspond to the primary key (ID) of a record in the tblFamily table.

Since you do not wish to use subforms, you will therefore need to either prepopulate your tblFamily & tblGenus tables with all of the possible foreign key values, before adding records to your tblSpecies table; or, add a button to your form which would open a form referencing the parent tables, allowing the user to add such records on-the-fly.

  • That makes sense. However, if the form is populated from top to bottom, in the order presented, then by the time the user gets to the Species box, the relevant FamilyID and GenusID would exist. As an Access neophyte, I had assumed that the software would then be able to associate the user's entry with these relations and update the links accordingly. – STim Jul 30 at 8:38
-1

You cannot modify an SQL inner join - the relationships to the underlying tables are lost.

You should not be entering data into multiple tables by means of a single form based on a query. It's possible to do so in some circumstances, but using a parent form bound to the referenced table, and within it a subform bound to the referencing table, is a better solution.

That way each for or subform is bound to one table and so each is updatable by itself.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.