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