Pick One Entry From Among Many

Shep,

Does the tblAppointmentsV1 table also contain an ID field, whichis the
basis of the relationship of the appointments to the records in the
tblPatientV1 table? Is this ID field represented on the
frmAppointmentsV2 form?

--
Steve Schapel, Microsoft Access MVP


shep wrote:
You are right again, but it appears I still have not done it correctly. I
now get "runtime error 3061 Too few parameters.Expected 1.
Here is highlited code:
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError

And complete code:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

I appreciate your effort to help me Mr. Steele and your patience.

TblAppointmentsV1 does have ID field and it too is autonumber and primary
key. It was not on the form, so I added it for testing, but got the same
error message.

I do not understand how to do the test Mr. Steele recommended. Would one of
you give me more specific guidance for that please. I am not a programmer,
so I hope y'all do not get exasperated with me. Entering the date via a
popup calendar is easy, but this procedure would just add a little more
efficiency.

Thanks

"Steve Schapel" wrote:

Shep,

Does the tblAppointmentsV1 table also contain an ID field, whichis the
basis of the relationship of the appointments to the records in the
tblPatientV1 table? Is this ID field represented on the
frmAppointmentsV2 form?

--
Steve Schapel, Microsoft Access MVP


shep wrote:
You are right again, but it appears I still have not done it correctly. I
now get "runtime error 3061 Too few parameters.Expected 1.
Here is highlited code:
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError

And complete code:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

I appreciate your effort to help me Mr. Steele and your patience.

I'm sorry I did not respond to the relationship part of your question. I do
not have a relationship between thr tables. Subsequent to your question, I
created one between the ID fields, but that did not change the error msg. I
the created one between ChartNumber fields on the tables, again to no avail.

Chart Number is unique to each patient and is automatically created with code.
I have a query qryPatientNameV1 that pulls ChartNumber from tblPatientV1 and
creates PatientName from tblPatientV1 LastName, FirstNam, MiddleIni.

I have a form frmMainPatientV1 created from qryPatientNameV1 and has
ChartNumber and PatientName fields.

frmAppointmentsV1 and frmPatientV1 are subforms on frmMainPatientV1

Thanks
"Steve Schapel" wrote:

Shep,

Does the tblAppointmentsV1 table also contain an ID field, whichis the
basis of the relationship of the appointments to the records in the
tblPatientV1 table? Is this ID field represented on the
frmAppointmentsV2 form?

--
Steve Schapel, Microsoft Access MVP


shep wrote:
You are right again, but it appears I still have not done it correctly. I
now get "runtime error 3061 Too few parameters.Expected 1.
Here is highlited code:
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError

And complete code:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

I appreciate your effort to help me Mr. Steele and your patience.

I found what was causing the error msg. I had the field name mis-spelled!!
Mea culpa!
It now runs without an error msg, but it does not post the date in
tblPatientV1. That may be related to the ID relationship issue Mr.Schapel
raised. Here is code now that does not get error msg:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET [Initial Visit] = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

"Steve Schapel" wrote:

Shep,

Does the tblAppointmentsV1 table also contain an ID field, whichis the
basis of the relationship of the appointments to the records in the
tblPatientV1 table? Is this ID field represented on the
frmAppointmentsV2 form?

--
Steve Schapel, Microsoft Access MVP


shep wrote:
You are right again, but it appears I still have not done it correctly. I
now get "runtime error 3061 Too few parameters.Expected 1.
Here is highlited code:
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError

And complete code:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

I appreciate your effort to help me Mr. Steele and your patience.

Shep,

We need to step back for a moment.

As I understand it, the tblAppointmentsV1 table is for information about
appointments. The appointments are for patients. I therefore assume
there needs to be a field in the tblAppointmentsV1 that identifies which
patient the appointment is for. Can you give some more details about
your data, as the process we are talking about will rely on being able
to tell Access which record in the tblPatientV1 table you want to add
the Initial Visit date to. If I am missing the point here, please let
us know.

--
Steve Schapel, Microsoft Access MVP


shep wrote:
I found what was causing the error msg. I had the field name mis-spelled!!
Mea culpa!
It now runs without an error msg, but it does not post the date in
tblPatientV1. That may be related to the ID relationship issue Mr.Schapel
raised. Here is code now that does not get error msg:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET [Initial Visit] = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

"Steve Schapel" wrote:


Shep,

Does the tblAppointmentsV1 table also contain an ID field, whichis the
basis of the relationship of the appointments to the records in the
tblPatientV1 table? Is this ID field represented on the
frmAppointmentsV2 form?

--
Steve Schapel, Microsoft Access MVP


shep wrote:

You are right again, but it appears I still have not done it correctly. I
now get "runtime error 3061 Too few parameters.Expected 1.
Here is highlited code:
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError

And complete code:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

I appreciate your effort to help me Mr. Steele and your patience.

You are correct. tblAppointmentsV1 is for appointment information for
patients. When I open the frmMainPatientV1, there are several tabs for
subforms, one of which is frmAppointmentsV1and another is frmPatientV1. The
mainform and all subforms have the fields ChartNumber and Patient, both
identify the patient.

On any subform, I can select a patient and that patient will show as I
select other tabs(subforms) until I select another patient. So when I have a
patient selected on the appointments subform, that patient is selected in
frmPatientV1 also.

tblPatientV1 has the ChartNumber field and frmPatientV1 is where the
ChartNumber is assigned to a new patient via autonumber code-not ACCESS
autonumber.

qryPatientNameV1 is created from tblPatientV1 and has the ChartNumber and
PatientName. frmMainPatientV1 is created from qryPatientNameV1 and has
ChartNumber and PatientFields. The forms that are included as subforms are
created from tables that also have ChartNumber and Patient fields and those
fields on the forms have qryPatientV1 as row source for ChartNumber and
Patient.

ChartNumber and Patient are combo boxes on the forms. When I click on
ChartNumber I see both the ChartNumber and PatientName. I select and both
fields are populated.

Shep




"Steve Schapel" wrote:

Shep,

We need to step back for a moment.

As I understand it, the tblAppointmentsV1 table is for information about
appointments. The appointments are for patients. I therefore assume
there needs to be a field in the tblAppointmentsV1 that identifies which
patient the appointment is for. Can you give some more details about
your data, as the process we are talking about will rely on being able
to tell Access which record in the tblPatientV1 table you want to add
the Initial Visit date to. If I am missing the point here, please let
us know.

--
Steve Schapel, Microsoft Access MVP


shep wrote:
I found what was causing the error msg. I had the field name mis-spelled!!
Mea culpa!
It now runs without an error msg, but it does not post the date in
tblPatientV1. That may be related to the ID relationship issue Mr.Schapel
raised. Here is code now that does not get error msg:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET [Initial Visit] = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

"Steve Schapel" wrote:


Shep,

Does the tblAppointmentsV1 table also contain an ID field, whichis the
basis of the relationship of the appointments to the records in the
tblPatientV1 table? Is this ID field represented on the
frmAppointmentsV2 form?

--
Steve Schapel, Microsoft Access MVP


shep wrote:

You are right again, but it appears I still have not done it correctly. I
now get "runtime error 3061 Too few parameters.Expected 1.
Here is highlited code:
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError

And complete code:
Private Sub Form_AfterUpdate()
If Me.ApptKept And Me.InitialVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID, dbFailOnError
End If
End Sub

I appreciate your effort to help me Mr. Steele and your patience.

Shep,

So, in summary, the patient is identified in the tblAppointmentsV1 table by:
ChartNumber
Patient
both
??

--
Steve Schapel, Microsoft Access MVP


shep wrote:
You are correct. tblAppointmentsV1 is for appointment information for
patients. When I open the frmMainPatientV1, there are several tabs for
subforms, one of which is frmAppointmentsV1and another is frmPatientV1. The
mainform and all subforms have the fields ChartNumber and Patient, both
identify the patient.

On any subform, I can select a patient and that patient will show as I
select other tabs(subforms) until I select another patient. So when I have a
patient selected on the appointments subform, that patient is selected in
frmPatientV1 also.

tblPatientV1 has the ChartNumber field and frmPatientV1 is where the
ChartNumber is assigned to a new patient via autonumber code-not ACCESS
autonumber.

qryPatientNameV1 is created from tblPatientV1 and has the ChartNumber and
PatientName. frmMainPatientV1 is created from qryPatientNameV1 and has
ChartNumber and PatientFields. The forms that are included as subforms are
created from tables that also have ChartNumber and Patient fields and those
fields on the forms have qryPatientV1 as row source for ChartNumber and
Patient.

ChartNumber and Patient are combo boxes on the forms. When I click on
ChartNumber I see both the ChartNumber and PatientName. I select and both
fields are populated.

Shep

Both. And both are text fields, should they be number fields?
Thanks Steve

"Steve Schapel" wrote:

Shep,

So, in summary, the patient is identified in the tblAppointmentsV1 table by:
ChartNumber
Patient
both
??

--
Steve Schapel, Microsoft Access MVP


shep wrote:
You are correct. tblAppointmentsV1 is for appointment information for
patients. When I open the frmMainPatientV1, there are several tabs for
subforms, one of which is frmAppointmentsV1and another is frmPatientV1. The
mainform and all subforms have the fields ChartNumber and Patient, both
identify the patient.

On any subform, I can select a patient and that patient will show as I
select other tabs(subforms) until I select another patient. So when I have a
patient selected on the appointments subform, that patient is selected in
frmPatientV1 also.

tblPatientV1 has the ChartNumber field and frmPatientV1 is where the
ChartNumber is assigned to a new patient via autonumber code-not ACCESS
autonumber.

qryPatientNameV1 is created from tblPatientV1 and has the ChartNumber and
PatientName. frmMainPatientV1 is created from qryPatientNameV1 and has
ChartNumber and PatientFields. The forms that are included as subforms are
created from tables that also have ChartNumber and Patient fields and those
fields on the forms have qryPatientV1 as row source for ChartNumber and
Patient.

ChartNumber and Patient are combo boxes on the forms. When I click on
ChartNumber I see both the ChartNumber and PatientName. I select and both
fields are populated.

Shep

Shep,

It doesn't matter whether they are text or numbers. But it is very
unusual (and probably unnecessarily risky and complicated) for there to
be two. Which of these is the Primary Key field in the tblPatientV1
table? And what is used as the Link Child Fields and Link Master Fields
properties of the frmAppointmentsV1 subform? Sorry, still trying to get
a grasp of what you've got there.

--
Steve Schapel, Microsoft Access MVP

shep wrote:
Both. And both are text fields, should they be number fields?
Thanks Steve

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

"Steve Schapel" wrote:

Shep,

It doesn't matter whether they are text or numbers. But it is very
unusual (and probably unnecessarily risky and complicated) for there to
be two. Which of these is the Primary Key field in the tblPatientV1
table? And what is used as the Link Child Fields and Link Master Fields
properties of the frmAppointmentsV1 subform? Sorry, still trying to get
a grasp of what you've got there.

--
Steve Schapel, Microsoft Access MVP

shep wrote:
Both. And both are text fields, should they be number fields?
Thanks Steve