several way to get what you like done.  but here is a video which may give you an idea as to how to get this done

New Record on Top

I like to have a filter Combo Box on the Parent Form, set the default to the current year and filter the Subform when the Parent Form loads.

By "open to" or "open at" the latest record, do you mean:
- Show only the latest record on the subform?
- Show all related records in subform, with the latest record on the first row?  Or last row?
- Show all related records in subform, with the latest record selected as the current record?

I created a Global function in an Access Module that I use to selected an item in a subform after adding or deleting an item.   This is the pertinent code sections.

Details

Public Enum FindRecordType

   frtFindByAutonumber
   frtFindFirstRecord
   frtFindLastRecord
   frtFindByOffsetValue
   frtFindByIndexPosition
  
End Enum
Copy

Details

Public Sub FindFormItem(frm As Form, Optional value As Long = 0, Optional FindType As FindRecordType = FindRecordType.frtFindByAutonumber)
  
   Dim idx As Long
   Dim rs As DAO.Recordset
   Set rs = frm.RecordsetClone
  
   On Error Resume Next
  
   ' if table empty
   If rs.EOF Or rs.BOF Then Exit Sub
  
   Select Case FindType
      Case FindRecordType.frtFindByAutonumber
         rs.FindFirst "ID = " & value
         If (rs.NoMatch = False) Then
            frm.Bookmark = rs.Bookmark
         End If
        
      Case FindRecordType.frtFindFirstRecord
         rs.MoveFirst
         frm.Bookmark = rs.Bookmark
        
      Case FindRecordType.frtFindLastRecord
         rs.movelast
         frm.Bookmark = rs.Bookmark
        
      Case FindRecordType.frtFindByOffsetValue
         rs.MoveFirst
         rs.AbsolutePosition = value
         frm.Bookmark = rs.Bookmark
        
      Case FindRecordType.frtFindByIndexPosition
         idx = value
         If (idx < rs.RecordCount - 1 And idx >= 0) Then
            rs.Move idx
         Else
            rs.Move rs.RecordCount - 1
         End If
         frm.Bookmark = rs.Bookmark
        
   End Select
  
   rs.Close
   Set rs = Nothing

End Sub
Copy

To use this function here's the source code from a sub form that calls the above function to select an item in the sub form after adding or deleting an item from the list of items in the form.

Details

Dim sel As Long

Private Sub Form_AfterDelConfirm(Status As Integer)
   On Error Resume Next
   Me.Form.Parent.Requery
   FindFormItem Me.Form, sel, frtFindByIndexPosition
End Sub

Private Sub Form_AfterInsert()
   On Error Resume Next
   Me.Form.Requery
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
   On Error Resume Next
   sel = Me.Form.RecordsetClone.AbsolutePosition
End Sub

Private Sub Form_Current()
   On Error Resume Next
   txtSelected = txtID     ' needed to highlite the selected record
   FindFormItem Me.Parent.Form, Nz([txtID], 0), frtFindByAutonumber
End Sub
Copy

Thanks everyone.
Firstly - Kevin: It's exactly your last option that I'm trying to achieve (Show all related records in subform, with the latest record selected as the current record?).
Secondly - thanks for all the other ideas - I'll work through them and see what happens. Many thanks for you time and input.

You can use FindFirst to automatically navigate to the desired record on the subform.  For instance, this code:

     Forms![Main form name]![Subform name].Form.Recordset.FindFirst "[field1] = 2025"

will navigate you to the first record on the subform where field1 equals 2025.

What if a person didn't renew for 2025?  Then you need DMax() to find the year of his most recent renewal.  The most recent year is the maximum value of the year field, hence the use of DMax().  And the code becomes:

     Forms![Main form name]![Subform name].Form.Recordset.FindFirst "[field1] = " & Nz(DMax( ... ), 0)