Error: Data in table violates referential integrity rules.

[Guest]

I am trying to enforce referential integrity for a relationship and getting
this error. As far as I can tell, the records in the child table all have a
corresponding record in the parent table, so I don't know what the problem
is. I created a form which listed the fields from the child table and the
parent table, then looked at each record. The information from both tables
was included in each record. I don't know what else could be giving me this
error. Any ideas?
-rg

 

[Allen Browne]

A common cause of this error is a foreign key field that has a default value
assigned.

For example, Access gives a Number field the Default Value of zero. If you
don't assign a value to this field, it automatically gets the zero value,
which fails to match any value in the main table, even though you did not
specifically assign a value for this field.

Don't give up on the Referential Integrity. It is worth tracing this down.

 

[Guest]

Thanks for your reply,

All the fields that are used for linking are filled in with information.
The two fields (one in parent, one in child), are text fields. When I scroll
through the records within the form I created that includes all the fields
from both tables, each record has the information listed from both tables.
Do you know what else could be wrong?
-rg

 

[Allen Browne]

If the fields are all accounted for, and all have values, then the values
must be mismatched.

Examples: one field is shorter than the other (fewer characters), or the
entries are not identical (e.g. leading space), or one has a zero-length
space where the other has a null, or ...

 

[Guest]

Thanks for your reply. I makes sense what you say unfortunately, I can't see
any way to find out. If the values are mismatched, would the child record
pull in the data from the correct parent record in the form I've created. If
so, how am I going to find the records that don't match. I created the form
so that I could find out what, if any parent record connected with my child
records. All records are accounted for and matched appropriately based on
the info in my form, so I'm at a total loss for finding out what values are
mismatched.

 

[Allen Browne]

2 suggestions: divide and conquor, and enlist Access to help.

If there are several indexes on the table, remove some. Remove the relations
too if needed. Once it starts working, you have identified which one is the
problem.

Then restore that index/relation, and when it fails, use the Immediate
Window (Ctrl+G) to ask Access what the values are in the mismatched fields.