Many to Many relationship

[LOST]

I have 3 tables. A Resident table, a Doctor table and an Appointment
junction table that joins the Resident and Doctor table (using there PK).

Residents Table:
RESIDENTS_ID, ResidentsFN, ResidentsLN......

Doctors Table:
DOCTOR_ID, DoctorFN, DoctorLN.....

Appointmnet Table:
RESIDENTS_ID, DOCTOR_ID, Date, Time, Comments

Can I create a form that the user can enter appointments using Residents
names and Doctors names instead of their ID's?

I keep getting errors. Either I cannot enter new data or I get the
following I have created a form consisting of the Residents name and then a
subform that consists of the doctors name and appointment information. The
form automatically creates a new DOCTOR_ID(autonumber) and when I try to
change it to a Doctor already entered into the DB it gives this error:

The current field must match the join key '?' in the table that serves as
the 'one' side of one-to-many relationship. Enter a record in the 'one' side
table with the desired key value, and then make the entry with the desired
join key in the 'many-only' table.

How can I create an easy to update appointment form?

 

[Larry Daugherty]

Some of your issues are probably my fault for leading you down an
unbalanced path....

When I implement the many-to-many relationship as in your situation I
usually enforce Referential Integrity only on the more important side
of the Relationship. In your case that would be
Residents-to-Appointments. I would base the form for the entry of
Appointments on the Resident. I would treat Doctors as a simple
lookup table as follows:

When you create a new Appointment for a Resident, select the Doctor
from a list presented in a combobox on your Appointment form. Store
the Primary Key from the Doctor but display the dr.s name.

If, after doing the above, you still have problems then - this one
time only- I invite you to decode my address. attach a copy of your
current application and send it to me. Note that you can create a
special purpose Hotmail or other anonymous account to protect your
privacy.

In the last of the dialogue in the other thread I asked for the
complete purpose of all of the tables in your application. Please
include it the attachments or, better, back here in this thread.

HTH

 

[LOST]

All of a sudden I think I might be all sorts of messed up. Should the PK's
from the Resident and Doctors tables be combined to be the PK for the
Appointment table?

Do I create the combo box in the form or do I do it from the Appointment
table and then base the form on that? or base it on a query?

I think I am thinking too much and making this harder than it should be

 

[Larry Daugherty]

Yes, it's easy to over think things but it's even easier when you get
incomplete or ambiguous guidance, mea culpa.

Your first question: As you wish. I just reflexively create an
autonumber primary key in the design of every table. Then if the need
for a "child" table develops there's a PK to hang it on. Except that
you asked about "doing it (combobox) from the Appointment table".
Absolutely, positively not that. That's a Lookup Field. Visit
www.mvps.org/access and read what's written there about Lookup Fields.
Believe it.

The other keys in tblAppointment will appear as Long Integers. The PK
from tblResident will be written for you as you create the record for
the Appointment. The PK for the dr. will be written for you via
methods you choose in your design. I suggest that there be a combobox
on the form for the Appointment. Selecting the dr's name in the
combobox will cause the PK value from the dr's record to be saved but
the dr's name to be displayed. Gyrations will be required to make
the correct data always display. I won't get into that until I know
which direction your design is actually going.

HTH

 

[LOST]

OK, the only way I could figure out how to get the combo box working in the
form was to:

Create a form based on the Residents Table (first and Last name) with a
subform based on Appointments Table (Date, Time, Comments, MDID. I changed
the MDID to a combo box, MDID as the Control Source and the Doctors table as
the Row Source and choosing the Doctors First and Last name columns.

It seems to be working fine. Is this right?

Thank you very much for all your help.