Continuing plaintive pleas for help from Jeff Boyce & John Vinson

I suppose by "resolver table" you are referring to the table I've got which
has PtID & Protocol_ID as foreign keys from tblPatient & tbl Protoocols. This
is the only location where I include info specific to a given patient's
activity relative to a given protocol. In the protocol table I include info
that relates only to the protocol - not to the patient - & I don't have a
foreign key "PtID" in the protocol table. In the patient table I include info
relating only to the patient - not to the protocol - and I don't have a
foreign key "Protocol_ID" in the patient table. I do have upcoming patient
appointment data in tblPatient, but i haven't figured out how to enter data
relative to a new patient and have it populate in existing queries. At this
point I'm entering data into tblPt, going to tblPatientProtocolActivity (the
"resolver table" you referred to"), and seeing no new patient there... so I
then head to a new row, use the combo box derived from PtID (the foreign key
from tblPt) to find the new patient, and enter it that way. THEN - and only
then - does the new patient appear in the queries I've built.

--
Thanks for your time!

I'm not sure I saw a question...

Regards

Jeff Boyce
Microsoft Office/Access MVP



"NC_Sue" wrote in message
...
I suppose by "resolver table" you are referring to the table I've got which
has PtID & Protocol_ID as foreign keys from tblPatient & tbl Protoocols.
This
is the only location where I include info specific to a given patient's
activity relative to a given protocol. In the protocol table I include
info
that relates only to the protocol - not to the patient - & I don't have a
foreign key "PtID" in the protocol table. In the patient table I include
info
relating only to the patient - not to the protocol - and I don't have a
foreign key "Protocol_ID" in the patient table. I do have upcoming patient
appointment data in tblPatient, but i haven't figured out how to enter
data
relative to a new patient and have it populate in existing queries. At
this
point I'm entering data into tblPt, going to tblPatientProtocolActivity
(the
"resolver table" you referred to"), and seeing no new patient there... so
I
then head to a new row, use the combo box derived from PtID (the foreign
key
from tblPt) to find the new patient, and enter it that way. THEN - and
only
then - does the new patient appear in the queries I've built.

--
Thanks for your time!

Question:
How can I get new patient info entered in light of my table structure
without manually entering patient into 2 tables?

My query & report seem to work OK on patients already included within the
patient table, but it's the new guy that gets added in that doesn't show up.
So I suspect it's the form that's the problem.

As to table structure, I'm not sure what all info you need. I have the
following fields:
PtID (autonumber, primary key)
LastName
FirstName
MedicalRecordNumber
MDID (foreign key from MD table, works fine)
DateOfBirth
DateOfDeath
CauseOfDeaht
ApptDate
ApptTime
Room#OfInpatients
AppointmentNeeded? (yes/no - for pts who don't have a current appt & who
aren't inpatients but who NEED an appointment set)
Comments (memo to self - things to do for this patient)

Those are the biggies.

I have a seperate Protocol table:
ProtocolID (autonumber, primary key)
Protocol name
A few other tedious details that aren't pertinent.

The 3rd table may be my problem. It contains info specific to any one
patient's activities related to any one protocol. Since a protocol may have
many patients enrolled and any one patient can enroll in several protocols, I
have the structure as follows:

PtID (Number... foreign key from tblPt)
Prot_ID (Number... foreigh key from tblPt)
ScreeningDate
Cohort
Study#
PtStatus
Day1Treatment
LastStudyTreatment

As to relationships, I joined both the PtID & ProtocolID primary keys from
the first 2 tables to the PtID & ProtocolID from the 3rd table, enforcing
referential integrity and cascading updates.


--
Thanks for your time!


"Jeff Boyce" wrote:

I'm not sure I saw a question...

Regards

Jeff Boyce
Microsoft Office/Access MVP



"NC_Sue" wrote in message
...
I suppose by "resolver table" you are referring to the table I've got which
has PtID & Protocol_ID as foreign keys from tblPatient & tbl Protoocols.
This
is the only location where I include info specific to a given patient's
activity relative to a given protocol. In the protocol table I include
info
that relates only to the protocol - not to the patient - & I don't have a
foreign key "PtID" in the protocol table. In the patient table I include
info
relating only to the patient - not to the protocol - and I don't have a
foreign key "Protocol_ID" in the patient table. I do have upcoming patient
appointment data in tblPatient, but i haven't figured out how to enter
data
relative to a new patient and have it populate in existing queries. At
this
point I'm entering data into tblPt, going to tblPatientProtocolActivity
(the
"resolver table" you referred to"), and seeing no new patient there... so
I
then head to a new row, use the combo box derived from PtID (the foreign
key
from tblPt) to find the new patient, and enter it that way. THEN - and
only
then - does the new patient appear in the queries I've built.

--
Thanks for your time!

On Tue, 26 Dec 2006 08:48:00 -0800, NC_Sue
wrote:

Question:
How can I get new patient info entered in light of my table structure
without manually entering patient into 2 tables?

The simplest way would be to use a Form based on the Patients table,
with a Subform based on your many-to-many resolver table. Use the
PatientID as the Subform's master and child link field.

John W. Vinson[MVP]

Getting closer? Maybe not.
I've built the form & subform. Oddly enough, on the subform, it's attempting
to populate the 2nd row of the datasheet with the patient's info - I'm using
a combo box in the subform that allows me to see the name of the patient
rather than his unique ID. I see that I now have John Doe listed with all of
his protocol-specific info on the subform, then a second line of the
datasheet says "John Doe" with all other data fields in that row blank.
And I still can't enter new data using the form/subform.
I'm getting frustrated, and you guys are saints.
--
Thanks for your time!


"John Vinson" wrote:

On Tue, 26 Dec 2006 08:48:00 -0800, NC_Sue
wrote:

Question:
How can I get new patient info entered in light of my table structure
without manually entering patient into 2 tables?

The simplest way would be to use a Form based on the Patients table,
with a Subform based on your many-to-many resolver table. Use the
PatientID as the Subform's master and child link field.

John W. Vinson[MVP]

You mention "forms" and "datasheets". Are you working entirely in forms, or
are you doing something directly in tables?

Why do you need to see the patient in the sub-form? Isn't the sub-form
joined to the mainform by the patient ID? Why show it again?

What am I missing...?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"NC_Sue" wrote in message
...
Getting closer? Maybe not.
I've built the form & subform. Oddly enough, on the subform, it's
attempting
to populate the 2nd row of the datasheet with the patient's info - I'm
using
a combo box in the subform that allows me to see the name of the patient
rather than his unique ID. I see that I now have John Doe listed with all
of
his protocol-specific info on the subform, then a second line of the
datasheet says "John Doe" with all other data fields in that row blank.
And I still can't enter new data using the form/subform.
I'm getting frustrated, and you guys are saints.
--
Thanks for your time!


"John Vinson" wrote:

On Tue, 26 Dec 2006 08:48:00 -0800, NC_Sue
wrote:

Question:
How can I get new patient info entered in light of my table structure
without manually entering patient into 2 tables?

The simplest way would be to use a Form based on the Patients table,
with a Subform based on your many-to-many resolver table. Use the
PatientID as the Subform's master and child link field.

John W. Vinson[MVP]

On Tue, 26 Dec 2006 15:14:00 -0800, NC_Sue
wrote:

Getting closer? Maybe not.
I've built the form & subform. Oddly enough, on the subform, it's attempting
to populate the 2nd row of the datasheet with the patient's info - I'm using
a combo box in the subform that allows me to see the name of the patient
rather than his unique ID. I see that I now have John Doe listed with all of
his protocol-specific info on the subform, then a second line of the
datasheet says "John Doe" with all other data fields in that row blank.
And I still can't enter new data using the form/subform.
I'm getting frustrated, and you guys are saints.

Sorry you're frustrated!

Some specific questions which might help:

- What is the Recordsource property of the Mainform?
- What are the Master Link Field and Child Link Field properties of
the Subform control?
- What is the Recordsource property of the Subform? If it's a query
please post the SQL.
- What happens when you type something into one of the blank data
fields on the second row of the subform? (The John Doe is correct - it
just means that when you add a new record it will inherit John Doe's
PatientID from the current record on the mainform, if the mainform
contained information about Alisia Rodriguez it would show her name,
I'm guessing).

John W. Vinson[MVP]

I'm trying to work entirely in forms. I created the subform in
autoform-datasheet view. And my problem is trying to enter data into the
form for a new patient - I'm having to go back to the patient and resolver
tables to get a new patient into my database.
--
Thanks for your time!


"Jeff Boyce" wrote:

You mention "forms" and "datasheets". Are you working entirely in forms, or
are you doing something directly in tables?

Why do you need to see the patient in the sub-form? Isn't the sub-form
joined to the mainform by the patient ID? Why show it again?

What am I missing...?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"NC_Sue" wrote in message
...
Getting closer? Maybe not.
I've built the form & subform. Oddly enough, on the subform, it's
attempting
to populate the 2nd row of the datasheet with the patient's info - I'm
using
a combo box in the subform that allows me to see the name of the patient
rather than his unique ID. I see that I now have John Doe listed with all
of
his protocol-specific info on the subform, then a second line of the
datasheet says "John Doe" with all other data fields in that row blank.
And I still can't enter new data using the form/subform.
I'm getting frustrated, and you guys are saints.
--
Thanks for your time!


"John Vinson" wrote:

On Tue, 26 Dec 2006 08:48:00 -0800, NC_Sue
wrote:

Question:
How can I get new patient info entered in light of my table structure
without manually entering patient into 2 tables?

The simplest way would be to use a Form based on the Patients table,
with a Subform based on your many-to-many resolver table. Use the
PatientID as the Subform's master and child link field.

John W. Vinson[MVP]

The Recordsource property of the Mainform is “tblPatients�.
“PtID� is the Master Link Field and Child Link Field properties of
the Subform control.

“tblPatientProtocolActivity� is the Recordsource property of the Subform.

What happens when you type something into one of the blank data
fields on the second row of the subform? (The John Doe is correct - it
just means that when you add a new record it will inherit John Doe's
PatientID from the current record on the mainform, if the mainform
contained information about Alisia Rodriguez it would show her name,
I'm guessing). OHMYGOD – It populates like it should – I hadn’t tried that.
DUH.

John, Jeff – you guys are terrific. Thank you SO much for your patience and
expertise!!!


--
Thanks for your time!


"John Vinson" wrote:

On Tue, 26 Dec 2006 15:14:00 -0800, NC_Sue
wrote:

Getting closer? Maybe not.
I've built the form & subform. Oddly enough, on the subform, it's attempting
to populate the 2nd row of the datasheet with the patient's info - I'm using
a combo box in the subform that allows me to see the name of the patient
rather than his unique ID. I see that I now have John Doe listed with all of
his protocol-specific info on the subform, then a second line of the
datasheet says "John Doe" with all other data fields in that row blank.
And I still can't enter new data using the form/subform.
I'm getting frustrated, and you guys are saints.

Sorry you're frustrated!

Some specific questions which might help:

- What is the Recordsource property of the Mainform?
- What are the Master Link Field and Child Link Field properties of
the Subform control?
- What is the Recordsource property of the Subform? If it's a query
please post the SQL.
- What happens when you type something into one of the blank data
fields on the second row of the subform? (The John Doe is correct - it
just means that when you add a new record it will inherit John Doe's
PatientID from the current record on the mainform, if the mainform
contained information about Alisia Rodriguez it would show her name,
I'm guessing).

John W. Vinson[MVP]

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).

Thanks so much for all your time and talent.
--
Thanks for your time!


"John Vinson" wrote:

On Tue, 26 Dec 2006 15:14:00 -0800, NC_Sue
wrote:

Getting closer? Maybe not.
I've built the form & subform. Oddly enough, on the subform, it's attempting
to populate the 2nd row of the datasheet with the patient's info - I'm using
a combo box in the subform that allows me to see the name of the patient
rather than his unique ID. I see that I now have John Doe listed with all of
his protocol-specific info on the subform, then a second line of the
datasheet says "John Doe" with all other data fields in that row blank.
And I still can't enter new data using the form/subform.
I'm getting frustrated, and you guys are saints.

Sorry you're frustrated!

Some specific questions which might help:

- What is the Recordsource property of the Mainform?
- What are the Master Link Field and Child Link Field properties of
the Subform control?
- What is the Recordsource property of the Subform? If it's a query
please post the SQL.
- What happens when you type something into one of the blank data
fields on the second row of the subform? (The John Doe is correct - it
just means that when you add a new record it will inherit John Doe's
PatientID from the current record on the mainform, if the mainform
contained information about Alisia Rodriguez it would show her name,
I'm guessing).

John W. Vinson[MVP]