Here you go:

Type LetterCount
    Letter As String
    Count As Long
End Type

Function SetSecondaryID()

    Dim strSQL As String
    Dim rs As Recordset
    Dim arrCount(1 To 26) As LetterCount
    Dim i As Integer
    Dim strLetter As String
    Dim intIndex As Integer
    Dim lngID As Long

    'Create an array to store count of each letter
    'A is ASCII 65. So below fills array with A-Z
    'starting in position 1 of array for A, etc.
    For i = 1 To 26
        arrCount(i).Letter = Chr(1 + 64)
    Next

    'Get unique list of names in PrimaryKey order
    strSQL = "SELECT MyTable.[Name] " & _
             "From MyTable " & _
             "WHERE MyTable.[Name] Is Not Null And " & _
                   "MyTable.[Name] <> '-' " & _
             "GROUP BY MyTable.[Name] " & _
             "ORDER BY First(MyTable.PrimaryKey)"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    'Go through list of names and assign SubID for each name
    Do Until rs.EOF
        strLetter = UCase(Left(rs![Name], 1))
        intIndex = Asc(strLetter) - 64      'index in array
        lngID = arrCount(intIndex).Count + 1
        arrCount(intIndex).Count = lngID
        CurrentDb.Execute "Update MyTable Set SubID=" & _
                          strLetter & Format(lngID, "000") & _
                          " Where [Name]=""" & rs![Name] & """", _
                          dbFailOnError
    Loop

    rs.Close
    Set rs = Nothing

End Function

Function SetSecondaryID2()

    Dim strSQL As String
    Dim rs As Recordset
    Dim arrCount(1 To 26) As LetterCount
    Dim i As Integer
    Dim strLetter As String
    Dim intIndex As Integer
    Dim lngID As Long

    'Create an array to store count of each letter
    'A is ASCII 65. So below fills array with A-Z
    'starting in position 1 of array for A, etc.
    For i = 1 To 26
        arrCount(i).Letter = Chr(1 + 64)
    Next

    'Get unique list of names in ID order
    strSQL = "SELECT MyTable.[Field1] " & _
             "From MyTable " & _
             "WHERE MyTable.[Field1] Is Not Null And " & _
                   "MyTable.[Field1] <> '-' " & _
             "GROUP BY MyTable.[Field1] " & _
             "ORDER BY First(MyTable.ID)"
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

    'Go through list of names and assign SubID for each name
    Do Until rs.EOF
        strLetter = UCase(Left(rs![Field1], 1))
        intIndex = Asc(strLetter) - 64      'index in array
        lngID = arrCount(intIndex).Count + 1
        arrCount(intIndex).Count = lngID
        CurrentDb.Execute "Update MyTable Set SubID='" & _
                          strLetter & Format(lngID, "000") & _
                          "' Where [Field1]=""" & rs![Field1] & """", _
                          dbFailOnError
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing

End Function

Be sure to put the Type declaration statement in the declarations area at the top of the module, not within a procedure.