Continuing plaintive pleas for help from Jeff Boyce & John Vinson

On Wed, 27 Dec 2006 05:56:00 -0800, NC_Sue
wrote:

OK - so I've normalized the tables & the form with master from tblPatients &
child from tblPatientProtocolActivity works fine, but I still find entering
new patients awkward. I can enter a new patient into the form, but am unable
to enter the protocol he belongs to (get a message telling me that there is
no corresponding record in tblPatientProtocolActivity), and am unable to exit
the form (as the full record couldn't be entered) without going to
tblPatientProtocolActivity & completing the entry there, i.e. selecting the
protocol for the patient.

Is there a way to make this less cumbersome? It's still TONS better than my
original database (where I would enter a patient multiple times in
tblPatient, once for each time he was screened for or enrolled in a protocol).

I presume that tblPatientProtocolActivity contains a ProtocolID field?
Does your Subform have a control bound to that field? The simplest
solution would be to use a Combo box based on the Protocol table,
bound to the ProtocolID field on the subform.

You do not need to display the Patient name on the subform (it's
already visible right in front of you on the mainform; just trust the
subform Master/Child to fill it in); but you should be able to select
the protocol right there on the subform. You should NEVER need to open
*ANY* of your tables to edit data, much less look up cryptic ID's and
type them in. Let the computer do that mindless work, that's what it's
good at!

You may want to look at some other working Access applications to see
how forms and subforms work. Clear your mind a bit of the problems
with your own database and take a look at (say) the Northwind sample
database. It lets the user enter new Customers (think patients), Items
(think protocols), Orders (think protocol activity) - without ever
opening the table.

John W. Vinson[MVP]