AbsolutePosition Property Example

The following example uses the SQL Server Pubs database to illustrate use of the AbsolutePosition property as a secondary letter index to a set of rows. The program begins by fetching the name and ID of all publishers into a dropdown ComboBox control. Initially, and as a specific publisher is chosen from the ComboBox, the set of titles for this publisher is fetched from the Titles table. This is accomplished by creating a query using the concatenation method and setting the RemoteData control's SQL property to this query. A DataGrid control is bound to the RemoteData control, so it reflects the current set of titles based on the publisher chosen. In the process of populating the result set, the first letter of each title is placed in an array along with the AbsolutePosition value for the row. When a letter is chosen and the MoveToRow button is clicked, the RemoteData control's AbsolutePosition property is set to the value associated with the letter.

Dim cn As rdoConnection
Dim en As rdoEnvironment
Dim rs As rdoResultset
Dim LetterIndex() As Long

Private Sub Form_Load()
Dim Li As Integer
'
'   Open the connection. This is a DSN-less Connection
'
Set en = rdoEnvironments(0)
Set cn = en.OpenConnection(dsName:="", _
    Prompt:=rdDriverNoPrompt, _
    Connect:="uid=;pwd=;driver={SQL Server};" _
        & "server=SEQUEL;database=pubs;")

MsRdc1.Connect = cn.Connect

'
'   Fill Publishers list combo box.
'
Set rs = cn.OpenResultset _
    ("Select distinct Pub_Name, Pub_ID" _
   & " from Publishers",  _
   rdOpenStatic, rdConcurReadOnly)
Do Until rs.EOF
    If rs(0) = Null Then
    Else
        PubList.AddItem " " _
         & rs!Pub_ID & ":" & rs!Pub_Name
    End If
    rs.MoveNext
Loop
PubList.ListIndex = 1
rs.Close

PubList_Click ' Make the first query

End Sub

Sub GetLetterIndexes()
'
'   Build an index array for the first
'   occurance of a letter in the list
'   of titles. Save an AbsolutePosition
'   for each letter.

ReDim LetterIndex(122) As Long
Screen.MousePointer = vbHourglass

Set rs = MsRdc1.Resultset
Do Until rs.EOF
    Li = Convert(Left$(rs!Title, 1))
    If LetterIndex(Li) = 0 Then
        LetterIndex(Li) = rs.AbsolutePosition
    End If
    rs.MoveNext
Loop

Screen.MousePointer = vbDefault
End Sub
'
'   Position the RemoteData control's 
'   rdoResultset to the first row of the letter
'   chosen based on the AbsolutePosition
'
Private Sub MoveToRow_Click()
Dim i
i = Convert(LetterWanted)
If LetterIndex(i) > 0 Then
    MsRdc1.Resultset.AbsolutePosition = LetterIndex(i)
Else
    LetterWanted = "(Not Found)"
    Beep
    For i = i + 1 To Asc("z")
        If LetterIndex(i) > 0 Then
            MsRdc1.Resultset.AbsolutePosition = _                   LetterIndex(i)
            LetterWanted = Chr(i + 64)
            Exit For
        End If
    Next i
End If
End Sub

Private Function Convert(Li As String) As Integer
Dim i As Integer
i = Asc(Li) ' Only references first letter
Select Case i
    Case Is < 65: Convert = 0
    Case Is > 122: Convert = 58
    Case Else: Convert = i - 64
End Select
End Function
'
'   Fetch List of Titles for this 
'   publisher
'
Private Sub PubList_Click()
Dim PubWanted As String
'   Pick off the PUB_ID
'
'   Build the SQL Query based on
'   publisher chosen
'
PubWanted = Trim(Left(PubList,  
   InStr(PubList, ":") - 1))
Screen.MousePointer = vbHourglass

MsRdc1.SQL = "select * from Titles" _
    & " where Pub_ID = '" _
    & PubWanted & "'" _
    & " order by Title"
MsRdc1.Refresh
Screen.MousePointer = vbDefault
If MsRdc1.Resultset.EOF Then
    MoveToRow.Enabled = False
Else
    MoveToRow.Enabled = True
    GetLetterIndexes
    MsRdc1.Resultset.MoveFirst
End If
End Sub