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 |
#21
|
|||
|
|||
Shep,
I think this design is going to make life difficult for you. I would strongly recommend that you just have one field in the tblPatientV1 table that uniquely identifies each patient. This field should be the Primary Key field in the tblPatientV1 table, and also used as the Foreign Key field in tables that are related to tblPatientV1, such as the tblAppointmentsV1 table. If I understand you correctly, the ChartNumber could fulfil this function, so you can scrap the ID field from the tblPatientV1 table altogether. I am not sure what data you have in the Patient table, but if this is the patient's name, it is not guaranteed to be unique, and as such should not be used to relate tables, or to relate subforms to main forms, etc. I know we have strayed somewhat from your original question. If we go back there, I think adapting the code I gave you to the following will probably work in this instance... but the above point about revising the table and form design still applies. If Me.ApptKept And Me.IniVisit Then CurrentDb.Execute "UPDATE tblPatientV1" & _ " SET [Initial Visit] = #" & Me.ApptDate & "#" & _ " WHERE ChartNumber='" & Me.ChartNumber & "'", dbFailOnError End If -- Steve Schapel, Microsoft Access MVP shep wrote: I do appreciate continued effort. It appears that I may have a design problem, but everything seems to work OK. Nonetheless, I can remove one if needed. Neither is primary key in tblPatientV1. The primary key is ID (autonumber) which I let ACCESS assign. For frmAppointmentsV1 subform, Link Master Field =ChartNumber;Patient Link Child Field = ChartNumber;Patient Thanks |
#22
|
|||
|
|||
I agree Steve. You have given me more than enough guidance to solve this.
So I am going to do some rework of the tables and study some more on table/relationships. I am confident I can get it done now, so will close this thread. Thanks again for being so patient and for your valuable advise. "Steve Schapel" wrote: Shep, I think this design is going to make life difficult for you. I would strongly recommend that you just have one field in the tblPatientV1 table that uniquely identifies each patient. This field should be the Primary Key field in the tblPatientV1 table, and also used as the Foreign Key field in tables that are related to tblPatientV1, such as the tblAppointmentsV1 table. If I understand you correctly, the ChartNumber could fulfil this function, so you can scrap the ID field from the tblPatientV1 table altogether. I am not sure what data you have in the Patient table, but if this is the patient's name, it is not guaranteed to be unique, and as such should not be used to relate tables, or to relate subforms to main forms, etc. I know we have strayed somewhat from your original question. If we go back there, I think adapting the code I gave you to the following will probably work in this instance... but the above point about revising the table and form design still applies. If Me.ApptKept And Me.IniVisit Then CurrentDb.Execute "UPDATE tblPatientV1" & _ " SET [Initial Visit] = #" & Me.ApptDate & "#" & _ " WHERE ChartNumber='" & Me.ChartNumber & "'", dbFailOnError End If -- Steve Schapel, Microsoft Access MVP shep wrote: I do appreciate continued effort. It appears that I may have a design problem, but everything seems to work OK. Nonetheless, I can remove one if needed. Neither is primary key in tblPatientV1. The primary key is ID (autonumber) which I let ACCESS assign. For frmAppointmentsV1 subform, Link Master Field =ChartNumber;Patient Link Child Field = ChartNumber;Patient Thanks |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form Subform data export | [email protected] | Using Forms | 6 | January 6th, 2005 06:25 PM |
Could I get some query theory clarification? | Dennis Snelgrove | Running & Setting Up Queries | 3 | November 27th, 2004 11:13 PM |
Converting GAL address entry to outlook contact | Ramana | Contacts | 2 | October 23rd, 2004 08:21 PM |
QDE (Quick Date Entry) | Norman Harker | Worksheet Functions | 37 | September 5th, 2004 01:24 AM |
QDE (Quick Date Entry) | Norman Harker | General Discussion | 3 | September 3rd, 2004 08:00 AM |