Last Visit information

[Radhika]

I have a table with multiple fields, the ones of concern being:
a. ID# (Primary Key)
b. Visit Date (Primary Key)
1. Existing patient (Yes/No-Check box)
2. Has tx info changed (Yes/No-Check box)
3. XRT (Yes/No)
4. XRT Dates (Date/Time)

I want to set-up the form so that if field 1 is checked as Yes and field 2
is checked as yes, field 3 and field 4 will get auto-populated with the
information for the latest Visit Date for each ID#.

How can i go about doing this?
Thank you,
Radhika

 

[John W. Vinson]

I have a table with multiple fields, the ones of concern being:
a. ID# (Primary Key)
b. Visit Date (Primary Key)
1. Existing patient (Yes/No-Check box)
2. Has tx info changed (Yes/No-Check box)
3. XRT (Yes/No)
4. XRT Dates (Date/Time)

I want to set-up the form so that if field 1 is checked as Yes and field 2
is checked as yes, field 3 and field 4 will get auto-populated with the
information for the latest Visit Date for each ID#.

How can i go about doing this?
Thank you,
Radhika

Well... you shouldn't. This would appear to be storing the exact same data
redundantly in two different records. Would you want or expct the XRT yes/no
field to change if the value were edited in the other record?

What's the rationale here? Even the existance of the "existing patient"
checkbox field is suspicious to me: can't you look into the Patients table and
see?

 

[Radhika]

The idea is that when someone fills in the information in the form, he does
not have to go back to the previous record to extract the XRT info and
re-enter it for the same ID# if the patient is an existing patient and his tx
info has remained the same as that for the previous record.

It is just to make data entry easier.

Sorry if i was not clear.

Thank you,
Radhika

 

[John W. Vinson]

The idea is that when someone fills in the information in the form, he does
not have to go back to the previous record to extract the XRT info and
re-enter it for the same ID# if the patient is an existing patient and his tx
info has remained the same as that for the previous record.

In that case the XRT field should be in the parent "Patient" table. The user
wouldn't have to go back in any case - that's something the computer can do
much more easily with a query. I guess I'm groping a bit here because I have
no idea what XRT info or TX info might be, and whether they are permanent
attributes of a patient, attributes associated with a visit, or what.

 

[Radhika]

They are fields associated with each visit. They cannot be in the parent
field because the information can change per visit. It only remains the same
if 'Existing patient' is selected as 'yes' (check box) and 'Has previous
medical history changed' is not checked.

I am trying to create an after update event for the two check box fields,
but do not know how to create a code that pulls in information from the last
visit.

Thanks,
Radhika

 

[John W. Vinson]

I am trying to create an after update event for the two check box fields,
but do not know how to create a code that pulls in information from the last
visit.

I'd suggest using a DLookUp on the visits table in the checkbox's afterupdate
event. You can use a criterion of the maximum visit date such as

VisitDate = DMax("VisitDate", "[Visits]", "[PatientID] = " & [PatientID])

 

[Radhika]

Thank you!

How would i incoropate this information into an If statement saying:
If Me.MedicalStatus = Ture Then
DLookUp VisitDate = DMax("VisitDate", "[Visits]", "[PatientID] = " &
[PatientID])

Radhika

I am trying to create an after update event for the two check box fields,
but do not know how to create a code that pulls in information from the last
visit.

I'd suggest using a DLookUp on the visits table in the checkbox's afterupdate
event. You can use a criterion of the maximum visit date such as

VisitDate = DMax("VisitDate", "[Visits]", "[PatientID] = " & [PatientID])

 

[John W. Vinson]

Thank you!

How would i incoropate this information into an If statement saying:
If Me.MedicalStatus = Ture Then
DLookUp VisitDate = DMax("VisitDate", "[Visits]", "[PatientID] = " &
[PatientID])

Radhika

Try

If Me.MedicalStatus Then ' a Yes/No field is already either true or false
Me![XRT Date]= DMax("[VisitDate]", "[Visits]", _
"[PatientID] = " & Me!PatientID)
Me![XRT] = DLookUp("[XRT]", "[Visits]", _
"[PatientID] = " & Me!PatientID & _
" AND VisitDate = " & DMax("[VisitDate]", "Visits", _
"PatientID = " & Me!PatientID))
End If