Maintaining a History of Clicked Records in the DataRepeater Control

A common task while using the DataRepeater control is to maintain a history of viewed records. The history can be displayed in a ListBox control. Any time the user clicks on an item in the history, the DataRepeater control displays that record in its client area. As with other data-aware controls (such as the DataGrid control), you (the developer) must store and retrieve bookmarks for each record you wish to track.

Important   Bookmarks are valid only for the life of a recordset, and are not persistable or interchangeable across recordsets.

The following step-by-step creates a history by first storing the bookmarks of viewed records in an array. A value associated with the record is also added to a ListBox control. When the user clicks an item in the ListBox control, the ListIndex of the clicked item is used to retrieve the bookmark of the record. Setting the VisibleRecords property to the bookmark causes the record to appear in the DataRepeater control.

The step-by-step builds upon the project created in "Using the DataRepeater Control."

To create a history of viewed records

1. Add a Reference to the Microsoft ActiveX Data Objects 2.0 Library.

   On the Project menu, click References to open the References dialog box. Check Microsoft ActiveX Data Objects 2.0 Library, and click OK to close the dialog box.

2. In the Declarations section of the form's code module, declare an array of variants, and an object variable for the ADODB Recordset object. Use the ADODB recordset to navigate.

   Option Explicit
   Private varBookmarks() As Variant
   Private rsProducts As ADODB.Recordset

3. In the form's Load event, use the ReDim statement to initialize the array. Set the recordset object variable to the recordset of the ADO Data Control.

   Private Sub Form_Load()
   ReDim varBookmarks(0)
   Set rsProducts = adodc1.Recordset
   End Sub

4. Draw a ListBox control on the form, and change its name from Listbox1 to lstHistory.
   
   
5. Add the following code to the DataRepeater control's CurrentRecordChanged event:

   Private Sub DataRepeater1_CurrentRecordChanged()
   ' The CurrentRecordChanged event occurs immediately after the form 
   ' is opened. At that point, the UBound(varBookmarks) = 0, and no 
   ' records have been added. Thus you must add the first record to 
   ' the history list, and its bookmark to the array. Then increment 
   ' the array index to 1 and exit the sub. The next time a record is 
   ' clicked, this code will be ignored.
   Dim iUpper As Integer ' Upper bound of bookmark array.
   Dim i As Integer      ' Counter for ListBox
   If UBound(varBookmarks) = 0 Then
   lstHistory.AddItem rsProducts!ProductName
   varBookmarks(0) = DataRepeater1.CurrentRecord
   ReDim Preserve varBookmarks(1)
   Exit Sub
   End If
   
   ' The code below now executes when the user clicks a record.
   ' First check the listbox to ensure the clicked record hasn't
   ' already been added to the history list. If so, exit.
   For i = 0 To lstHistory.ListCount - 1
   If lstHistory.List(i) = rsProducts!ProductName _
   Then Exit Sub
   Next I
   
   ' If no duplicates, then add the record to the history, and
   ' cache the bookmark in the array.
   lstHistory.AddItem rsProducts!ProductName
   varBookmarks(UBound(varBookmarks)) = DataRepeater1.CurrentRecord
   ' Increment the array.
   ReDim Preserve varBookmarks(UBound(varBookmarks) + 1)
   End Sub

6. The code below sets the VisibleRecords property to a cached bookmark. Paste the code into the form's code module.

   Private Sub lstHistory_Click()
   Debug.Print lstHistory.Text, lstHistory.ListIndex
   DataRepeater1.VisibleRecords(1) = _
   varBookmarks(lstHistory.ListIndex)
   End Sub

7. Run the project. Scroll through the recordset using the DataRepeater control's scrollbars. Click on any record to add it to the history. Click any item in the listbox to return to that record.