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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|