Bdw 1 notary can have multiple indices and 1 notary can have multiple cases
at the moment you do not have referential integrity between tblNotaries and tblNotaryIndex. Add an autonumber primary key to tblNotaryIndex. removing the existing multiple primary key. Then you can set referential integrity. If the reason you had a multi field index was to prevent duplicates, you can still set that as a separate, non primary index - click on the indexes option on the ribbon.
When you append data to tblCaseInfo, do you set a value in the field NotaryRefNo? If not and yu let it default to Zero, then you would violate RI.
If you need to leave the NotaryRefNo not set then you need to:
First, make sure the field tblCaseInfo.NotaryRefNo is set to not required.
Next, do one of the following:
1) make sure the field tblCaseInfo.NotaryRefNo in the table's design does not have a default value
or
2) Append Null into the field tblCaseInfo.NotaryRefNo
at the moment you do not have referential integrity between tblNotaries and tblNotaryIndex. Add an autonumber primary key to tblNotaryIndex. removing the existing multiple primary key. Then you can set referential integrity. If the reason you had a multi field index was to prevent duplicates, you can still set that as a separate, non primary index - click on the indexes option on the ribbon.
I added the primary key in tblNotaryIndex but still for some reason I cannot enforce referential integrity. NotaryRefNo in tblNotaries is indexed Yes no duplicates and in tblNotaryIndex is indexed Yes Duplicates Ok. Do you think that this is the reason I cannot enforce ri?
no - NotaryRefNo in tblNotaries needs to be indexed Yes no duplicates and joined to NotaryRefNo in tblNotaryIndex which needs to be indexed Yes Duplicates Ok.Do you think that this is the reason I cannot enforce ri?
It may be that tblNotaryIndex has orphan records where NotaryRefNo in tblNotaryIndex does not have a matching NotaryRefNo in tblNotaries. Either way, you should be getting a message which is telling you why you cannot enforce referential integrity.
can you send a screen shot of your indexes in tblNotaryIndex as well
I presume the parent field has the same number of characters specified? Otherwise, it might be to be to do with the other relationship in the table - shelfID.
Yes they are the same size ie: 4 and have both an input mask \R999;;
I removed the relationship of shelfID but same problem
I don't see why this is an issue - can you upload a copy of the db (compacted and zipped), having removed any sensitive data
Ref Post #6
If tblNotaryIndex.NotaryRefNo now is an autonumber type and tblNotaries.NotaryRefNo is still a TEXT type, you will not be able to set RI.I added the primary key in tblNotaryIndex but still for some reason I cannot enforce referential integrity. NotaryRefNo in tblNotaries is indexed Yes no duplicates and in tblNotaryIndex is indexed Yes Duplicates Ok. Do you think that this is the reason I cannot enforce ri?
Linking fields MUST be of the same type.
Looking at the image in Post #1, tblNotaryIndex should have a foreign key from tblNotaries, a 1-to-many relationship, not a 1-to-1 relationship.
I think there should be a junction table between tblCaseInfo and tblNotaries..... If I understand correctly.....