Pick One Entry From Among Many

[Guest]

I have a table, tblPatientV1 that contains a date field, IniVisit, for
initial visit. A form, frmPatientV1 is for entering and viewing most of
patient static information and it contains the IniVisit field, which I now
populate with a popup calendar on the form.
Another table, tblAppointmentsV1 contains appointments scheduling
information including appointment date field, ApptDate, ApptKept
(True/False), and IniVisit (True/False). A form, frmAppointmentsV2, is for
entering and viewing appointments information including those fields. The
ApptDate field is populated with a popup calendar on the form.

I would like to have the IniVisit date field in tblPatientV1 populated with
ApptDate from frmAppointmentsV2 when ApptKept is checked TRUE AND IniVisit is
already checked TRUE.

Thanks for guidance on how to do this

 

[Steve Schapel]

Shep,

Make an Update Query which you can run on the After Update event of the
frmAppointmentsV2 form. You could do it in code, if you like, something
like this...
If Me.ApptKept And Me.IniVisit Then
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE PatientID=" & Me.PatientID,
dbFailOnError
End If

 

[Guest]

Thank you for your help. I copied the code yopu provided into After Update
property of tfrm AppointmentsV2 and saved the form. When I make entry in
form and close it I get error:
Can't find the macro 'If Me.'
The macro (or the macro group) doesn't exist, or the macro is new but
hasn't been saved.

What might I need to do?

Thanks

 

[Steve Schapel]

Shep,

In the After Update property of the form, enter [Event Procedure] and
then click the little button to the right with the ellipsis (...) which
will open the Visual Basic Editor window. Then you can put the code in
there, between the lines Privet Sub Form_AfterUpdate() and End Sub.

I have assumed you have a numerical field called PatientID as the
primary key of your tblPatientV1 table, so you will of course need to
adjust the code as necessary.

 

[Guest]

That is what was needed ; but I still have a little bug. On closing the
form, I get this part of code highlited and "Syntax Error":
CurrentDb.Execute "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID,

ID in tblPatientV1 is an autonumber and is the primary key. Is that causing
the syntax error?

Thanks

Shep,

In the After Update property of the form, enter [Event Procedure] and
then click the little button to the right with the ellipsis (...) which
will open the Visual Basic Editor window. Then you can put the code in
there, between the lines Privet Sub Form_AfterUpdate() and End Sub.

I have assumed you have a numerical field called PatientID as the
primary key of your tblPatientV1 table, so you will of course need to
adjust the code as necessary.

--
Steve Schapel, Microsoft Access MVP

Thank you for your help. I copied the code yopu provided into After Update
property of tfrm AppointmentsV2 and saved the form. When I make entry in
form and close it I get error:
Can't find the macro 'If Me.'
The macro (or the macro group) doesn't exist, or the macro is new but
hasn't been saved.

What might I need to do?

 

[Steve Schapel]

Shep,

Did you include the 'dbFailOnError' that was part of the code I gave you?

 

[Guest]

yes sir I did. Here is the 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

Thanks

 

[Douglas J. Steele]

Looks as though you put dbFailOnError on a separate line. It's supposed to
be a continuation of the rest of the Execute statement.

 

[Guest]

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.

 

[Douglas J. Steele]

Try assigning your SQL to a variable and printing out the content of that
variable:

Dim strSQL As String

strSQL = "UPDATE tblPatientV1" & _
" SET IniVisit = #" & Me.ApptDate & "#" & _
" WHERE ID=" & Me.ID
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

What gets printed out for strSQL?

 

[Steve Schapel]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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]

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

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

:

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?

 

[Guest]

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,

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

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

:

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.

 

[Steve Schapel]

Shep,

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

 

[Guest]

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

 

[Steve Schapel]

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.

 

[Guest]

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