Referential Integrity -- I guess 2

[uscitizen]

i think this is kind of weird.

lets say i have two tables in my a2k mdb (there are more but only two concern me now). one is called 'Patients' the other is called 'Prior_Therapies'. as you would expect they are related.

Patients has a composite primary key made up of Protocol_Id and Patient_Id and Prior_Therapies' pk is made up of Protocol_ID, Patient_ID and Therapy_Code.

i find that i can add a record to the table 'Prior_Therapies' but when i attempt to add a new record using the form i built for this table that the message "You cannot add or change a record because a related record is required in table 'PATIENTS'" issues.

why? no problems adding records to the Prior_Therapies table but the form won't accept a new record!? what aren't i seeing?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[BNPMike]

Your heading says referential integrity but you avoid talking about it. It's not magic. It can only be there if you put it there.

Your problem does seem odd but against that surely, for whatever reasons, Access is doing what you need - you probably don't want to add a patient-therapy before you add the patient.

I do wonder why Protocol-Id is a part key of patient. Surely a patient is separate to their treatment(s).

 

[LittleSmudge]

It sound like you have NOT established referential integrity within the database
(This is usually done in the Relationships window and you can see it it has been done because the links between the table display a 1 at the one end and an infinity sign at the many end.)


However, what I think you might have done in the set up of your subform is to link field(s) in the many end table to field(s) in the One end table via the subformcontrol.
This is done by placing field names in the ParentField and ChildFields properties of the subform control
If you've done this and you place data in the child fields refered to without there being the appropriate values to match them in the parenyt fields in the one end table then you will get the results you are experiencing.


If I'm on the right track and you need more help in resolving the problem - post back here.



'ope-that-'elp.


G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.