Related record is required....

[Susan]

I am a novice at Access, but I thought I understood the basics of it. I
created this DB, which I thought was simple and straightforward, but I guess
I was wrong. Separately, the main form and its subforms work well, but I
have problems when I join them together. I have a main form called "Patient
Info" with PatientID as the Primary Key (there is also a field called Patient
Chart Number" which is unique to all patients and which I have also used as a
Primary Key in this form when testing it). I have several other smaller
tables --- tblIrritants, tbl OtherDx, tblOtherMeds, tblPulDx, tblPulMeds,
tblTravelHx and tbl SurgicalHx. Respectively, their Primary Keys are
IrritantsID, OtherDxID, OtherMedsID, PulDxID, PulMedsID, TravelHxID and
SurgicalHxID. All of the smaller tables also have the Patient Chart Number
listed, but not as a Key. I originally had all of the tables linked to the
PatientID or Patient Chart Number, which resulted in all being one-to-many
relationships. I got the "related record is required" when I tried to enter
the subforms (OtherDx, PulDx, PulMeds and OtherMeds)--it was never the same
subform twice in a row, it seemed. So, then I tried adding the names of the
smaller tables' keys to the main table so that I could link each table to its
corresponding key in the main form (PulDxID to PulDxID and so on)--
now I have many-to-one relationships. I have been able to enforce
referential integrity and cascade with all of these relationships mentioned
here. I still have the same problem with the "related record is required"
when entering the subform portions of the form. Sometimes it is required in
tblPatientInfo (the main table), sometimes it is the other tables. What am I
doing wrong here? Like I said, I thought it was a straightforward table, but
I guess not. All help will be greatly appreciated. Thanks!

Susan

 

[tina]

i'm assuming that the PatientInfo table is your "main" data table, and each
record describes one patient. i'm also assuming that *in the real world*
one Patient may have many Irritants
one Patient may have many Other Diagnoses
one Patient may have many Other Medications
one Patient may have many Travel Histories
one Patient may have many Surgical Histories
etc.

remember, your database relationships are based on the *real world*
relationships of your entities. if the above assumptions are correct, then
the "other smaller tables" you listed are "child" (many-side) tables of the
parent table PatientInfo.

I have a main form called "Patient Info" with PatientID as the Primary Key
(there is also a field called Patient Chart Number" which is unique to all
patients and which I have also used as a Primary Key in this form when
testing it).

you can only have one primary key in each table (whether it's composed of
one field or multiple fields. Hint: a single-field key will probably be
easier for you to work with, at this point.) you have to decide *at the
table level* what field in table PatientInfo is the primary key, set the
primary key *in the table design view*, and use only that key to relate
table PatientInfo to its' child tables.

in each child table, add the primary key from table PatientInfo, as a
foreign key field. make sure the foreign key field's Indexed property is set
to Yes (Duplicates OK). **do NOT put the primary key field from a child
table into the parent table PatientInfo.** in the Relationships window, set
the relationship FROM table PatientInfo TO each child table, on the matching
key fields. enforce referential integrity. be cautious about enforcing
Cascade Deletes; you need be absolutely sure that you want records from the
child table to be automatically deleted if the parent record is deleted.

in your mainform/subform setup, the subform's
LinkMasterFields/LinkChildFields properties must use the *primary
key/foreign key* values from the parent/child tables that the
mainform/subform are bound to. this is the standard setup to model
parent/child table relationships in forms. once the subforms are correctly
linked to the main form, when you go to a new record in the main form, you
will have to enter data in the mainform to create a record, before you can
enter data in any of the subforms.

hth

 

[Susan]

Tina,

Thanks for getting back to me! In the past weeks, I had the format just like
you mentioned, with one exception, but was having trouble with that
persistent error,
so I started tweaking...and tweaking ....and tweaking till I got to the
point where I forgot what I had done and when. The exceptions were that not
all of the Index Properties were set at Yes (Duplicates OK)--once I fixed
those I started having success (fingers crossed). It was a simple answer--my
brain was just too fried to deal with any more input, I guess. I am one
of those visual learners who has to write things down to put 2 and 2
together. Right before you responded, I decided to try an experiment and
took all relationships away from the tables--just to see what would happen.
Sure enough, all information flowed smoothly and accurately. I thought MAYBE
I could just connect the tables through queries, but then, I guess, that
would defeat the purpose of a relational database...Thanks, again!

Susan

 

[John Vinson]

Right before you responded, I decided to try an experiment and
took all relationships away from the tables--just to see what would happen.
Sure enough, all information flowed smoothly and accurately.

ummm...

An automobile will run perfectly well without steering or brakes.

Until of course you need to turn or slow down...

Deleting relationships is NOT the solution to your problem! Fixing
your forms (as Tina suggests) is a much much better idea.
Relationships are there and enforced FOR A PURPOSE; you really, really
don't want to have your database allow for diagnoses for nonexistant
patients, or payments for unknown, unspecified procedures... do you?

John W. Vinson[MVP]

 

[tina]

you're welcome, Susan, and never fear - as long as you create the
tables/relationships correctly, then link your forms/subforms correctly,
you'll have no problems.

since you say you're a novice (we all start out that way, no worries), here
are a few suggestions to help you learn how to use the Access software, and
even more importantly, how to apply data modeling principles to building
your tables/relationships
software:
Microsoft Access <version> Bible by Prague and Irwin
data modeling:
Database Design for Mere Mortals by Michael Hernandez
both:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
beginning with the Starting Out and Database Design 101 links.

hth