Many to many self-join problem

[dbdbdo]

I have a table with Client information. I am trying to relate clients to each other using a join table. I created a small DB to model this and it works perfectly.

The model table (tblMain) is:
IndividualIDX PK
FirstName (text)
LastName (text)

The join table (tblRelationshipJoin) is:
Party1ID (long)
Party2ID (long)
Party1RelationshipType (long)
Party2RelationshipTypePri (long)
Party2RelationshipTypeSec (long)

The relationship table (tblRelationshipType) is:
RelationshipIDX PK
RelationshipType (text)

In a query I connected the IndividualIDX from the first table (tblMain) to Party1ID and the same key from an alias of the first table (tblRelation) to Party2ID.

Then using the relationship table (tblRelationshipType) and an alias (tblRelationshipType_1), connected the PK from each to Party1RelationshipTypePri and Party2RelationshipTypeSec respectively.

I entered sample data into the Client and Relationship tables like so:
Individual: John Public
Individual: Jane Doe
Relationship: Father
Relationship: Child

Then using the query, I entered the PKs from each of the tables. No problems.

Now to the real problem.

I went back to my working DB which already had two entries. I created a join table based on what I had done in the test DB. I entered the PKs similarly and again everything worked.

However, when I added new clients and attempted to enter the joining PKs as before, I received the error message "You cannot add or change a record because a related record is required in tblClientMain." I did this both in code and manually with the same results.

I verified that there were four entries in tblClientMain, two old and two new.

It seems as if data entered previous to creating the join table is "recognized" by Access and the data entered later is not.

I know the join table structure is correct since it works with the two previous entries. There is no PK on the join table since that doesn't work (I've tried it).


In both DBs I set Referential Integrity. Also, in the test DB the tables and queries are in the same DB, while in the working DB they are split (FE/BE).

Could this be a case of corruption? I compacted the DB using a specially created DB for this purpose. It uses the command "DBEngine.CompactDatabase." Still no success.

Any help is greatly appreciated!
TIA

[dbdbdo]

After all that verbiage, I found the problem.

A some point when I was relinking the FE and BE, I inadvertently linked some of the tables to an older BE and the rest to a newer one. The relation linking table was in the old one and the clients were in the the new. So when Access reported I needed an entry first, it was correct.

Well, nothing really wasted. If anyone needs to create a join like this, I have provided the framework.

Keep in mind, you can use the join table to ADD to the DB. However, to VIEW this table in a query (with meaningful data), it must be RIGHT-JOINED to the other tables as shown in the attachment.