A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Continuing plaintive pleas for help from Jeff Boyce & John Vinson



 
 
Thread Tools Display Modes
  #11  
Old December 27th, 2006, 06:16 PM posted to microsoft.public.access.tablesdbdesign
John Vinson
external usenet poster
 
Posts: 4,033
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

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]
  #12  
Old December 27th, 2006, 06:49 PM posted to microsoft.public.access.tablesdbdesign
NC_Sue
external usenet poster
 
Posts: 51
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

I have 2 forms with subforms (as I need to be able to look @ data both ways).
One is a form with the "master form" source being tblProtocols & the "child
form" being tblPatientProtocolActivity. One is a form whose master is
tblPatients with the child being tblPatientProtocolActivity. I can't enter a
new patient into either one although I can assign a patient to a new protocol
without difficulty.

Could my problem be that I've enforced referential integrity within my
relationships?
--
Thanks for your time!


"John Vinson" wrote:

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]

  #13  
Old December 27th, 2006, 08:49 PM posted to microsoft.public.access.tablesdbdesign
John Vinson
external usenet poster
 
Posts: 4,033
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

On Wed, 27 Dec 2006 09:49:00 -0800, NC_Sue
wrote:

I have 2 forms with subforms (as I need to be able to look @ data both ways).
One is a form with the "master form" source being tblProtocols & the "child
form" being tblPatientProtocolActivity. One is a form whose master is
tblPatients with the child being tblPatientProtocolActivity. I can't enter a
new patient into either one although I can assign a patient to a new protocol
without difficulty.


With a form/subform setup, you can enter data into the mainform, and
into tblPatientProdocolActivity - but there's no (instant) way to add
data to BOTH "one" side tables on the same form. That is, if you have
to enter a new patient, AND a new protocol, AND assign the patient to
that protocol, you need to bring up another form in some way (either
by opening a form separately, or using VBA code in a combo box's Not
In List event).

Could my problem be that I've enforced referential integrity within my
relationships?


Only if you've done it wrong. What tables are related to which?

John W. Vinson[MVP]
  #14  
Old December 27th, 2006, 10:31 PM posted to microsoft.public.access.tablesdbdesign
NC_Sue
external usenet poster
 
Posts: 51
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

tblProtocol's primary key, Protocol_ID, is the foreign key in
tblPatientProtocolActivity, i.e. in the relationship window I drug
Protocol_ID from tblProtocol to tblPatientProtocolActivity.

Similarly, tblPatient's primary key, Pt_ID, is the foreign key in
tblPatientProtocolActivity, i.e. in the relationship window I drug Pt_ID from
tblPatient to tblPatientProtocolActivity.

Just for kicks, I unclicked "enforce referential integrity" on the
relationships above, and it now works fine.

Thoughts???

Again, thank you SO much.
--
Thanks for your time!


"John Vinson" wrote:

On Wed, 27 Dec 2006 09:49:00 -0800, NC_Sue
wrote:

I have 2 forms with subforms (as I need to be able to look @ data both ways).
One is a form with the "master form" source being tblProtocols & the "child
form" being tblPatientProtocolActivity. One is a form whose master is
tblPatients with the child being tblPatientProtocolActivity. I can't enter a
new patient into either one although I can assign a patient to a new protocol
without difficulty.


With a form/subform setup, you can enter data into the mainform, and
into tblPatientProdocolActivity - but there's no (instant) way to add
data to BOTH "one" side tables on the same form. That is, if you have
to enter a new patient, AND a new protocol, AND assign the patient to
that protocol, you need to bring up another form in some way (either
by opening a form separately, or using VBA code in a combo box's Not
In List event).

Could my problem be that I've enforced referential integrity within my
relationships?


Only if you've done it wrong. What tables are related to which?

John W. Vinson[MVP]

  #15  
Old December 28th, 2006, 12:19 AM posted to microsoft.public.access.tablesdbdesign
John Vinson
external usenet poster
 
Posts: 4,033
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

On Wed, 27 Dec 2006 13:31:01 -0800, NC_Sue
wrote:

tblProtocol's primary key, Protocol_ID, is the foreign key in
tblPatientProtocolActivity, i.e. in the relationship window I drug
Protocol_ID from tblProtocol to tblPatientProtocolActivity.

Similarly, tblPatient's primary key, Pt_ID, is the foreign key in
tblPatientProtocolActivity, i.e. in the relationship window I drug Pt_ID from
tblPatient to tblPatientProtocolActivity.

Just for kicks, I unclicked "enforce referential integrity" on the
relationships above, and it now works fine.


It's only working because by unclicking enforcement, you're allowing
garbage data into the database!! Disabling the speedometer on your car
won't protect you from speeding tickets or accidents!

There's *something* wrong with your Subform. You should have the
Subform based on tblPatientProtocolActivity; you should have a Combo
Box control on the subform, bound to Protocol_ID, and based on
tblProtocol; the Protocol_ID should be the bound column of the combo
box.


John W. Vinson[MVP]
  #16  
Old December 28th, 2006, 01:21 AM posted to microsoft.public.access.tablesdbdesign
NC_Sue
external usenet poster
 
Posts: 51
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

My Subform is based on tblPatientProtocolActivity; I have a Combo
Box control on the subform, bound to Protocol_ID, and based on
tblProtocol; the Protocol_ID is the bound column of the combo
box... I just checked all of these things and they are true statements.

Any other thoughts?

--
Thanks for your time!


"John Vinson" wrote:

On Wed, 27 Dec 2006 13:31:01 -0800, NC_Sue
wrote:

tblProtocol's primary key, Protocol_ID, is the foreign key in
tblPatientProtocolActivity, i.e. in the relationship window I drug
Protocol_ID from tblProtocol to tblPatientProtocolActivity.

Similarly, tblPatient's primary key, Pt_ID, is the foreign key in
tblPatientProtocolActivity, i.e. in the relationship window I drug Pt_ID from
tblPatient to tblPatientProtocolActivity.

Just for kicks, I unclicked "enforce referential integrity" on the
relationships above, and it now works fine.


It's only working because by unclicking enforcement, you're allowing
garbage data into the database!! Disabling the speedometer on your car
won't protect you from speeding tickets or accidents!

There's *something* wrong with your Subform. You should have the
Subform based on tblPatientProtocolActivity; you should have a Combo
Box control on the subform, bound to Protocol_ID, and based on
tblProtocol; the Protocol_ID should be the bound column of the combo
box.


John W. Vinson[MVP]

  #17  
Old December 28th, 2006, 02:45 AM posted to microsoft.public.access.tablesdbdesign
John Vinson
external usenet poster
 
Posts: 4,033
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

On Wed, 27 Dec 2006 16:21:01 -0800, NC_Sue
wrote:

My Subform is based on tblPatientProtocolActivity; I have a Combo
Box control on the subform, bound to Protocol_ID, and based on
tblProtocol; the Protocol_ID is the bound column of the combo
box... I just checked all of these things and they are true statements.


There is something we're not communicating!

Open your Relationships window and View All.

The only relationships you've mentioned are from tblProtocol to
tblPatientProtocolActivity, by Protocol_ID; and from tblPatients to
tblPatientProtocolActivity, by Patient_ID. Is that correct? Do you
have any relationships from the Primary Key of
tblPatientProtocolActivity to any other table (based on what you've
said, you shouldn't)?

John W. Vinson[MVP]
  #18  
Old December 29th, 2006, 12:45 AM posted to microsoft.public.access.tablesdbdesign
NC_Sue
external usenet poster
 
Posts: 51
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

No I don't - the primary key of tblPatientProtocolActivity doesn't relate to
any table.

The primary key from tblPatient is related to tblPatientProtocolActivity by
virtue of the primary key in tblPatient being the foreign key in
tblPatientProtocolActivity.

Similarly, the primary key in tblProtocol is a foreign key within
tblPatientProtocolActivity.


--
Thanks for your time!


"John Vinson" wrote:

On Wed, 27 Dec 2006 16:21:01 -0800, NC_Sue
wrote:

My Subform is based on tblPatientProtocolActivity; I have a Combo
Box control on the subform, bound to Protocol_ID, and based on
tblProtocol; the Protocol_ID is the bound column of the combo
box... I just checked all of these things and they are true statements.


There is something we're not communicating!

Open your Relationships window and View All.

The only relationships you've mentioned are from tblProtocol to
tblPatientProtocolActivity, by Protocol_ID; and from tblPatients to
tblPatientProtocolActivity, by Patient_ID. Is that correct? Do you
have any relationships from the Primary Key of
tblPatientProtocolActivity to any other table (based on what you've
said, you shouldn't)?

John W. Vinson[MVP]

  #19  
Old December 29th, 2006, 03:59 AM posted to microsoft.public.access.tablesdbdesign
John Vinson
external usenet poster
 
Posts: 4,033
Default Continuing plaintive pleas for help from Jeff Boyce & John Vin

On Thu, 28 Dec 2006 15:45:01 -0800, NC_Sue
wrote:

No I don't - the primary key of tblPatientProtocolActivity doesn't relate to
any table.

The primary key from tblPatient is related to tblPatientProtocolActivity by
virtue of the primary key in tblPatient being the foreign key in
tblPatientProtocolActivity.

Similarly, the primary key in tblProtocol is a foreign key within
tblPatientProtocolActivity.


I'm baffled then at why you're getting this message.

I don't usually do this (others take note - unsolicited emails of
databases will get a consulting contract proposal in reply g) but
could you compact your database, Zip it, and email it to me at

jvinson at wysard of info dot com

(edit out the blanks and the punctuation). I'll try to see what is
causing this bizarre error.

John W. Vinson[MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.