Referential integrity not being enforced

K

Kurt

Despite enforcing referential integrity between my tables, I am able to
create a record in the child table without a corresponding record in the
parent table.

For example, tblPatients has a one-to-many relationship to tblVisits. (A
patient can have many visits.) They are linked by PatientID.

tblPatients
------------
PatientID (PK)
FName
LName
…

tblVisits
------------
VisitID (PK)
PatientID
VisitDate
VisitLocation
…

The main form, frmPatients, has one subform, fsubVisits. They are linked by
PatientID.

I can enter any number of records into fsubVisits without ever entering a
patient, and I don’t get the usual error message when I try to leave the
record or subform.

Any idea what’s overriding the referential integrity?

Thanks.

Kurt
 
P

Pat Hartman\(MVP\)

It is possible that you have a PatientID with a value of zero and have the
default value for PatientID in tblVisits set to 0. Access doesn't know when
a long integer is going to be used as a FK so it automatically assigns 0 as
the default value for all numeric fields. You need to remove the 0 so that
the default will be null. Then you need to set the required property of the
FK to Yes.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top