Building my own Collection(s)

[Rawb]

The story thus far: Our Hero, having slain the dragon.... What? Oh, wrong story. Sorry! Let's try this again...



I've been working on "user-customizable sorting" functionality for lists in one of my programs. And, I've decided that these functions should use a custom Collection to handle the data.

So, I'm attempting to create a Collection that does the following:
Stores multiple objects, each one "indexed" in a manner that allows you to loop through it both forwards and backwards.
In each Object, stores all the data necessary to build a SQL ORDER BY Clause on-the-fly:

• Name (String): A "human readable" alias for the Field identified by the collection item. Required, Unique.
• FieldName (String): The actual Field name. For Joined Query support, must allow for both [Table].[Field] and [Field] naming coventions. Required, Unique.
• SortOrder (Long Integer): The type of sort (Ascending or Descending) to be performed on the field. Required.
  
  • 1 (Constant tSortAsc) = Ascending Sort
  • 2 (Constant tSortDesc) = Descending Sort
  
  
• SortPriority (Long Integer): The location of this field in the ORDER BY Clause (so 1 would mean it gets added to the clause first). If no value is explicitly assigned, it's added to the end of the "stack." Otherwise, it is inserted into the "stack" at whatever location is assigned and everything after it, shifted down. Unique.

I've been able to figure out everything except the "indexing" (for looping through the Collection) and Adding a new item with a SortPriority that requires shifting already existing Collection items "down"

Can anyone help me out here? I'm thinking of using the SortPriority Item as the de facto index. I just don't know how to put it into the coding...

At the risk of looking like a newb (which, admittedly, I am when it comes to Classes and Collections) here's my code so far:

Option Compare Database
Option Explicit

Public Enum tSortOrder
  tSortAsc = 1
  tSortDesc = 2
  [_First] = 1
  [_Last] = 2
End Enum

Private tSortItem As New Collection

Sub Add(FieldName, FriendlyName, SortPriority, Optional SortType As tSortOrder = tSortAsc, Optional Priority)
  Dim si As New tSortItem

  Dim i As Long

  ' Setting up our values
  si.FieldName = FieldName
  si.Name = FriendlyName
  si.SortOrder = SortType

  If IsMissing(Priority) Then
    ' Stick it at the end
    si.Priority = si.Count
  Else
    ' Update any tSortItems with an existing SortPriority of "Priority" or
    ' higher by adding 1. Then set this tSortItem's SortPriority to "Priority"
  End If
End Sub

[Rawb]

Obviously, I haven't gotten to the other Member Methods yet. Eventually, there will be ways to modify and delete data from the Collection too.

[Rawb]

OK, I've been looking around and I've figured out at least one issue: I was trying to create a Collection of custom objects without defining the custom objects first (in other words, I didn't define my class!)

So my modified code now looks like this:

Class code - tSortItem

Option Compare Database
Option Explicit

Public Enum tSortType
  tSortAsc = 1
  tSortDesc = 2
  [_First] = 1
  [_Last] = 2
End Enum

Public tIndex As Long
Public tFieldName As String
Public tName As String
Public tSortOrder As tSortType

' FieldName is read/writeable
Property Get FieldName() As String
  FieldName = tFieldName
End Property

Property Let FieldName(Value As String)
  tFieldName = Value
End Property

' Index is read/writeable
Property Get Index() As Long
  Index = tIndex
End Property

Property Let Index(Value As Long)
  tIndex = Value
End Property

' Name is read/writeable
Property Get Name() As String
  Name = tName
End Property

Property Let Name(Value As String)
  tName = Value
End Property

' SortOrder is read/writeable
Property Get SortOrder() As tSortType
  SortOrder = tSortOrder
End Property

Property Let SortOrder(Value As tSortType)
  tSortOrder = Value
End Property

Collection Code - tSortList

Option Compare Database
Option Explicit

Private tName As String
Private tCount As Long

Private tSortList As Collection
Private tmpSortItem As tSortItem

' Name is read/writeable
Property Get Name()
  Name = tName
End Property

Property Let Name(Value)
  tName = Value
End Property

' Count is read-only!
Property Get Count()
  Count = tCount
End Property

Public Sub Class_Initialize()
  Set tSortList = New Collection
End Sub

While it still has problems (for one thing, there's still no way to properly handle changing indexes), that all will come later. What I'm looking for now is: How do I write my Collection's Add Sub?