Microsoft access - Form Class Object code

Question

The Sub code below is form class object of access. I want this sub code to be executed to display the highest pain score in HighestPainScore column as shown below. For example, pt 1111111 with surgery date 7/26/2017 has highest pain score 10, the sub code picks 10 and writes 10 from row 2 to row 7 in HighestPainScore field. Pt 1111111 with surgery date 7/27/2017 has highest pain score 2, the sub code picks 2 and writes 2 from row 8 to 10.

Problem: when I click SortHighestPainScore button, it does not sort as code said (PatientMRN ASC, SugeryDate ASC, PainScore DESC); in addition, 0 is written all 12 rows in HighestPainScore field.

Pls advise. Thanks.

PatientMRN	SurgeryDate	PainScore	HighestPainScore
1111111	7/26/2017	0	10
1111111	7/26/2017	1	10
1111111	7/26/2017	0	10
1111111	7/26/2017	10	10
1111111	7/26/2017	5	10
1111111	7/26/2017	6	10
1111111	7/27/2017	2	2
1111111	7/27/2017	0	2
1111111	7/27/2017	0	2
4444444	4/7/2017	5	10
4444444	4/7/2017	10	10
4444444	4/7/2017	4	10

Option Compare Database

Private Sub SortHighestPainScore_Click()

Dim CopyCurrentMrn As String
Dim CopyNextMrn As String
Dim CopyCurrentSurgeryDate As Date
Dim CopyNextSurgeryDate As Date
Dim CopyHighestPainScore As Integer
Dim ID1 As Integer
Dim ID2 As Integer
Dim db As Database
Dim rs As Recordset

CopyCurrentMrn = ""
CopyNextMrn = ""

'Find total record
Set db = CurrentDb
Set rs = db.OpenRecordset("TablePainScore")
ID2 = rs.RecordCount

DoCmd.SetOrderBy "PatientMRN Asc"
DoCmd.SetOrderBy "SurgeryDate Asc"
DoCmd.SetOrderBy "PainScore Desc"

'Copy First record
DoCmd.GoToRecord , , acFirst
ID1 = 1
CopyHighestPainScore = PainScore
MsgBox "Answer of current Pain Score: " & CopyHighestPainScore
[HighestPainScore] = CopyHighestPainScore
CopyCurrentMrn = [PatientMRN]
CopyCurrentSurgeryDate = [SurgeryDate]
MsgBox "Answer of current mrn: " & CopyCurrentMrn
MsgBox "Answer of current Surgery Date: " & CopyCurrentSurgeryDate

'Loop
    Do While ID1 <= ID2 - 1
    
     DoCmd.GoToRecord , , acNext
    
     CopyNextMrn = [PatientMRN]
     CopyNextSurgeryDate = [SurgeryDate]
     MsgBox "Answer of next mrn: " & CopyNextMrn
     MsgBox "Answer of next Surgery Date: " & CopyNextSurgeryDate
    
     If CopyCurrentMrn = CopyNextMrn And CopyCurrentSurgeryDate = CopyNextSurgeryDate Then
        ID1 = ID1 + 1
        [HighestPainScore] = CopyHighestPainScore
       
     Else
        ID1 = 1
        CopyHighestPainScore = [PainScore]
        [HighestPainScore] = CopyHighestPainScore
       
     End If
    
     CopyCurrentMrn = [PatientMRN]
     CopyCurrentSurgeryDate = [SurgeryDate]
     
     MsgBox "Answer of current mrn: " & CopyCurrentMrn
     MsgBox "Answer of current Surgery Date: " & CopyCurrentSurgeryDate

    Loop
    
End Sub

Answer 1

If you want to DISPLAY ALL PainScores for ALL patients for ALL Surgeries, along with the HighestPainScore for each surgery as an additional field, a query can be written to do that, incorporating the previous query as a subquery:

SELECT PatientMRN, SurgeryDate, PainScore, MaxScore.HighestPainScore

FROM tblYourTableNameGoesHere

INNER JOIN
(SELECT PatientMRN, SurgeryDate, Max(PainScore) AS HighestPainScore

FROM tblYourTableNameGoesHere

GROUP BY PatientMRN, SurgeryDate) AS MaxScore

ON tblYourTableNameGoesHere.PatientMRN = MaxScore.PatientMRN

AND tblYourTableNameGoesHere.SurgeryDate = MaxScore.SurgeryDate

ORDER BY tblYourTableNameGoesHere.PatientMRN, tblYourTableNameGoesHere.SurgeryDate

Answer 2

It looks like you're trying to store a calculated value in multiple fields in a table. Generally, experienced developers do not do that; they do not calculate values and store those values in a field in a table.

If you want to DISPLAY the highest pain score for a specific incident, the most effective way is a straightforward aggregate query like this:

SELECT PatientMRN, SurgeryDate, Max(PainScore) AS HighestPainScore

FROM tblYourTableNameGoesHere

GROUP BY PatientMRN, SurgeryDate

ORDER BY PatientMRN, SurgeryDate