ComponentOne True DBGrid Pro 8
Filtering Data in Recordsets

In some cases, you might want to filter the underlying recordset by limiting the number of items in a given field or fields. By using the FilterBar property, and entering the filter text appropriately, you can reduce the number of field entries. When the FilterBar property of a TDBGrid control is set, a blank row with a gray separator line appears directly above the uppermost data row in the grid.

To set the filter bar, perform the following steps:

  1. Place a Microsoft ADO Data Control (OLEDB), a True DBGrid OLE control and a command button on a new form.

  2. Connect the ADODC to the C:\Program Files\ComponentOne Studio\Common\TDBG8DEMO.MDB database table and set the RecordSource property to Composers. Then connect the True DBGrid to this data control by choosing it in the DataSource property.

  3. Set the caption of Command1 to "Clear Filter" and change its name to cmdClearFilter.

  4. Open the TDBGrid property pages and select the Splits tab. Set the FilterBar property to True.

  5. Add the following code to cmdClearFilter:

    Example Title
    Copy Code
    Dim col As TrueOleDBGrid80.Column
    Dim cols As TrueOleDBGrid80.Columns
    Private Sub cmdClearFilter_Click()
        'Clears filter from grid.
        For Each col In TDBGrid1.Columns
            col.FilterText = ""
        Next col
        Adodc1.Recordset.Filter = adFilterNone
    End Sub
    Private Sub TDBGrid1_FilterChange()
        'Gets called when an action is performed on the filter bar.
        On Error GoTo errHandler
        Set cols = TDBGrid1.Columns
        Dim c As Integer
        c = TDBGrid1.col
        Adodc1.Recordset.Filter = getFilter()
        TDBGrid1.col = c
        TDBGrid1.EditActive = True
    Exit Sub
        MsgBox Err.Source & ":" & vbCrLf & Err.Description
        Call cmdClearFilter_Click
    End Sub
    Private Function getFilter() As String
        ' Creates the SQL statement in adodc1.recordset.filter and only filters
        ' text currently. It must be modified to filter other data types.
        Dim tmp As String
        Dim n As Integer
        For Each col In cols
            If Trim(col.FilterText) <> "" Then
                n = n + 1
                If n > 1 Then
                    tmp = tmp & " AND "
                End If
                tmp = tmp & col.DataField & " LIKE '" & col.FilterText & "*'"
            End If
        Next col
        getFilter = tmp
    End Function

Note the use of the FilterChange event to manipulate the underlying recordset. When you run the program you will see a form similar to this one.

If you type a "B" into the filter bar cell located above the Last column, you will see that the underlying recordset is limited to just composers whose last names start with the letter B as in the example below. Had we extended the filter to "BR", the list of composers would have been reduced to only those whose last names started with BR.

Next, type a "G" into the filter bar cell located above the Country column. You will see that the underlying recordset is limited to just composers whose last names start with the letter B and who were born in countries starting with the letter G, as in the following illustration.

Finally, since the SQL statement in this example was set up to handle text only, if you press the Clear Filter button and then type in the filter box located below the Birth column, you get an error message from the provider because the filter bar is set to filter only text. To filter dates or any other type of data, you must alter the code.

For more information, see Tutorial 29.



Copyright (c) GrapeCity, inc. All rights reserved.

Product Support Forum  |  Documentation Feedback