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:

Place a Microsoft ADO Data Control (OLEDB), a True DBGrid OLE control and a command button on a new form.
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.
Set the caption of Command1 to "Clear Filter" and change its name to cmdClearFilter.
Open the TDBGrid property pages and select the Splits tab. Set the FilterBar property to True.

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 TDBGrid1.HoldFields Adodc1.Recordset.Filter = getFilter() TDBGrid1.col = c TDBGrid1.EditActive = True Exit Sub errHandler: 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

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

    TDBGrid1.HoldFields

    Adodc1.Recordset.Filter = getFilter()

    TDBGrid1.col = c

    TDBGrid1.EditActive = True

Exit Sub

 

errHandler:

    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.