MS Access: how to create joins to avoid "cannot add record(s); join key of table...not in recordset" error

Question

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:

The relationships between these tables is as below:

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

I chose the below fields in the wizard:

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.

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?

Answer 1

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.

Answer 2

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.