Changes you requested to the table were not successful error

[BlueJacket]

I know this answer is going to be simple, but I can't seem to figure out the root cause of this error message: The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

This is my table setup:

PropertyID (FK)
TansactionID (PK, Autonumber)
RevenueTypeCode (FK, Indexed - Duplicates OK)
AmountReceivedDate
AmountReceived

I have this table linked to my Property Details table via PropertyID and my Revenue Code table via RevenueCode.

I can't add a new record with a duplicate PropertyID. Why is that? It's not indexed. That error message makes me think it's trying to add a record to my Property Details table, which doesn't make any sense. Unless it's referring to the relationship between the tables, which I don't understand how there can be duplicate values in the relationship.

Any help in this matter would be much appreciated.

 

[jdraw]

Can you post a copy of the database --remove confidential info first?
Just need enough data to show the issue.

 

[BlueJacket]

In the midst of getting a copy of the database ready, and deleting the unnecessary tables, I tried something out and it seemed to fix it.

When I created the table, it was defined as a one-to-one relationship between tblRevenue and tblPropertyDetails (because not all of the information was entered), so when I tried adding info that would make it a one-to-many relationship, it caused that error. I thought that Access would just automatically make it into a one-to-many relationship when it detected the change, but it appears I had delete the relationship, add the info that would make it a one-to-many relationship, then reestablish the relationship in order for it to work.

 

[The_Doc_Man]

Yep. Access is generally written to believe that YOU know what kind of relationship you have, so if you say One-to-One than you get One-to-One and automatic changes do not occur.