How to populate subform's data fields with data from previous entry?

[DPROMICS]

Situation: I have a subform that is displayed when
a new patient clinic visit's data is to
be entered.

The primary table contains patient demographic
data. The clinic visits data is in a clinic
visit table. The patient's demographics table
has a one to many relationship with the clinic
visits table. That is for each entry in the
patient's demographics table there can be/are
multiple clinic visits entries in the clinic
visits table for a patient.

Every thing "else" works as it should and/or is
intended to work in this Access App. Er, well
except for some reports. But that help request
is in another posting on this forum.

I would like to be able to populate the new
clinic visits subform data fields with the data
from the patient's most recent (previous)
clinic visit. This would help to speed up data
entry for the clinical staff because some clinic
visit data items do not necessarily change
from clinic visit to the next clinic visit.

So Far: In the fields of the current clinic visit subform
I have a =DLookup function in the data item's
default value in its properties. Of course a query
to go with the DLookUp function.

The Problem: The DLookUp function does find the patient's
previous clinic visit data in the clinic visits
table. However, it finds and displays the
patient's first clinic visit data. Not the
last (most recent) clinic visit's data.

The DLookUp function:
=DLookUp "Lab_INR","Qry_Coumadin_Clinic_Most_Recent_Lab_INR","Tbl_PT_Demographics].[PT_DB_Number]")

The underlying query called by the DLookUp function:

SELECT [Tbl_PT_Demographics].[PT_DB_Number], [Tbl_PT_Demographics].[PT_Name_Last], [Tbl_PT_Demographics].[PT_Name_First], [Tbl_Coumadin_Clinic].[Lab_INR], [Tbl_Coumadin_Clinic].[Lab_Name]

FROM Tbl_PT_Demographics INNER JOIN Tbl_Coumadin_Clinic ON [Tbl_PT_Demographics].[PT_DB_Number]=[Tbl_Coumadin_Clinic].[PT_DB_Number]

WHERE (((Tbl_Coumadin_Clinic.Clinic_Visit_Date)=(SELECT Max(Clinic_Visit_Date)

FROM Tbl_Coumadin_Clinic T WHERE T.PT_DB_Number = Tbl_Coumadin_Clinic.PT_DB_Number));

Solution: OK how do I get this to find the patient's most
recent (last) clinic visit's data?

Is there a better way to populate a subform with
existing data than by using the DLookUp function?

I will greatly appreciate any suggestions, code/method examples, suggestions, etc.

 

[lupins46]

There is a DMax function which has the same syntax as the Dlookup, assuming you have a field which is suitably sequenced to give you the latest visit.

 

[hermanlaksko]

The last visit of any patient will have the highest ID in your table, so a dmax for the id where PatientID = YrPatient should do the trick.

Once you have this "maxID" you can use this to get any data you want, via a select statement. Note that Dlookup and Dmax are very slow to collect data... very! so use them as little as poss. They are smart, but slow.

Set your sub forms recordset = YrSelect where VisitID=MaxID and PatientID=YrPatient.


Herman
Say no to macros

 

[BoxHead]

DPROMICS,

If you have 15 minutes, I have a suggestion.

Create a query on the Patient table. Include all fields, call it qryP. Do the same thing with the Visits table but set the Sort to Visit Date Descending. Save that as qryV.

Use the forms wizard to create a form for each of these. frmP and frmV.

Open frmP in Design view, select the wand so the wizards are active and select the Subform tool. Place it at the bottom of frmP and follow the wizard through the steps to place frmV as a subform on frmP with the Patient ID fields linked.

Select the combo box tool, place it at the top of frmP and follow it through the steps to 'find a record' based on the PatientID and Patient Name.

When you open this in form view, you should be able to select a patient's name from the combobox and automatically display the patient demographic info in the top of the form and that same patient's visit info (in reverse order) at the bottom of the form.

Even if you don't like the look or if there are other things on your current form this doesn't achieve, I think that all of the code you would need to do what you are trying to do would be available to you in the design view of this form.

Just a thought.



John

Use what you have,
Learn what you can,
Create what you need.

 

[DPROMICS]

Thanks to both of you for your suggestions. I will look in to your suggestions and see where they lead.

Basically, the way the program is set up:

My philospy of creating applications is to make them flow the way the user currently performs the task. Yes, there are some ways things were being done that can be changed in the applocation to make it work better for the user. Also, even though this is an Access app, I have made it look, as best I can, as a polished professional program that could just as well have been written in VB, C or whatever.

Given that:

The app's form contains some subforms. One subform at the top displays the currently selected patient's data. Only what is needed to display for the selected patient is displayed Intis way the clinician always sees what patient is selected, no matter in what other form/subform they are viewing and/or entering data for that patient.

There are tabs on the form. The tabs are Patients, Reports, Coumadin Clinic .. . . etc.

The clinician first selects the patient they want to enter clinical data for.

They then select the Coumdain Clinic Tab to view the patient's clinic visits data and/or enter data for a new clinic visit.

All of this works quite nicely. Its the reports and populating new visist data fields that are causing me problems.

Yep, DLookUps are slow. I will have to look at the other suggestion.

 

[NewfieSarah]

Hey DPROMICS it looks like you know what you are at??? So i was wondering if you could help me out?? I have been trying to get the same thing to happen as you did with your forms. so data entry is alot faster then it would be. Here is on of the links that i have been discussing, it would be great if you or anyone else could help me with this topic thanks!!

http://www.tek-tips.com/viewthread.cfm?qid=1145719&page=1

as you might be able to tell I havent been getting far with it, and that is why i need more help. Thanks

 

[sdk]

Hi

if you use:

If Me.NewRecord Then _
    VBA.SendKeys "^'"

(note "^'" is the same as ctrl + ' )
on the control's 'Enter' event, this will pre-populate the field when the user enters the field for a new record.

Alternatively, you can use the OnCurrent event for the form to set the focus on & move through each target conrol and use the same sendKeys command, before returning to the first cell to allow the user to enter data.

To me, these are simpler (albeit possibly longer) & easier than using alternatives (Max/mins, recordsets etc), and will always be consistent. They of course will not work on the first record of a datasheet - that would involve an alternative where required.

Cheers

S