Pick One Entry From Among Many

[Steve Schapel]

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

 

[Guest]

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.

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

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