Scroll through sub-forms records simultaneously and in order by date

[BallunarCrew]

I have a tabbed form that displays patient data. I have a sub-form on one of the tabs that displays exam data for the selected patient. I have a requirement to display three exams at a time for the patient. I can put the sub-form in the tab 3 times and it shows the same exam for the patient three times. We want to have it display three different exams and when the user clicks the Next Record button on one sub-form (or maybe it is a Next Record button that is on the main form) it moves to the next record on all three. It would display exams 1,2 and 3 then 2, 3 and 4, then 3, 4, and 5, etc. I have a prgramming idea in mind but am wondering if there is some magic that Access has, that I just don't know about, to do this before I re-invent the wheel.
Thanks

 

[MajP]

You just need to move the record in the first subform. The next subform would have a query something like

select Top 1 patientExam from tblPatientExams where patientID = forms!PatientForm!txtPatientID and patientExamID > forms!PatientForm!subFormOne.patientExamID

And the third would be a mod
select Top 1 patientExam from tblPatientExams where patientID = forms!PatientForm!txtPatientID and patientExamID > forms!PatientForm!subFormTwo.patientExamID

basically the second subform has a query that returns only one record greater (whatever that means) than the record in the first subform. The third has a query returning only one record greater than the record in the second subform. The only code is need to move the first subform forward or backwards.

 

[BallunarCrew]

That sounds like a great idea. I have an exam date field I want to use. I just have one question. In your sample code, what is "patientExam" referring to. I can make an easy translation to my tables and fields for the other table and fields you show.

Here is my very basic table structures:
Patients
PTID - key field

PT_Exams
PTID - key field
ExamDate - key field
LumbarTestScore
PelvicTestScore
...

select Top 1 patientExam from tblPatientExams where patientID = forms!PatientForm!txtPatientID and patientExamID > forms!PatientForm!subFormOne.patientExamID

 

[MajP]

I assume the first subform is linked to the main form by patientID. I am not a big fan/user of composite keys and appears your PK is a composite key of PTID and ExamDate. I am especially not a big fan of composite primary keys where one is also a foreign key. I would just have an auto ID 'ExamID'. It is not wrong, but is more complicated to work with (at least for me). I am not very good at SQL so what i show is a general Idea (I can write vb code by memory, but not sql)

select Top 1 PTID, ExamDate, ... from PT_Exams where PTID = forms!PatientForm!txtPatientID and ExamDate > forms!PatientForm!subFormOne.txtExamDate order by ExamDate

You may also have to force the form to requery the subforms after you change the first subform record.

 

[MajP]

Also do you want three subforms or a single subform displaying only three records? Either way can be done.