[arnelgp]

look at that I already posted similar code to help an OP in sept-2:

Public Function fncList(ByVal p_id As Long, ByVal p_max_rows As Integer) As Integer
    '
    ' arnelgp
    ' 02-sep-2019
    '
    ' p_id      autonumber value or -99.
    '           if -99 is passed, it will reset the collection object.
    ' purpose:
    '
    ' group records in Query
    '
    ' syntax: "select table1.*, fncList([ID]) As Grouping From table1;"
    '
    Const NO_ERROR      As Integer = 0
    Static col_values   As VBA.Collection
    Static int_counter  As Integer
    Static m_max_row    As Integer
    Dim int_ret         As Integer
    If (p_id = -99) Then
        Set col_values = New VBA.Collection
        int_counter = 0
        Exit Function
    End If
    If (col_values Is Nothing) Or (p_max_row <> m_max_row) Then
        Set col_values = New VBA.Collection
        m_max_row = p_max_row
        int_counter = 0
    End If
    On Error Resume Next
    int_ret = col_values(p_id & "")
    If err.Number <> NO_ERROR Then
        err.Clear
        On Error GoTo 0
        int_counter = int_counter + 1
        col_values.Add (int_counter \ (p_max_rows + 1)) + 1, p_id & ""
        int_ret = (int_counter \ (p_max_rows + 1)) + 1
    End If
    fncList = int_ret
End Function

Public Function fncResetList()
Call fncList(-99)
End Function