Tutorial 29 - Filter Bar

In this tutorial you will utilize the FilterBar property in True DBGrid. Using the Filter Bar feature of True DBGrid, you can narrow the number of items in a given table by filtering the underlying recordset.

1.   Start a new project.

2.   From the Visual Basic Project menu, select Components, then check the boxes labeled ComponentOne True DBGrid Pro 8.0 (OLEDB) and Microsoft ADO Data Control (OLEDB).

3.   Place an ADO Data control, a True DBGrid control and a command button on the form.

4.   Next we will connect Adodc1 to the TDBG8Demo database.

Display the custom property pages for Adodc1. Click the General tab and select the Use Connection String option. Click Build. Choose the Microsoft Jet 4.0 OLE DB Provider option. Click Next. Enter the datasource name by pressing the Ellipsis button  and locating the database (TDBGDEMO.MDB). Test the connection to make sure it works. Press OK to close the dialog window. Press the Apply button.

      Choose the Recordsource tab. Set the Command Type to 2 – adCmdTable and the Table or Stored Procedure Name to Composer. Press the OK button to accept the selections and close the properties page.

5.   Set the TDBGrid1 DataSource to Adodc1.

6.   Change the caption of Command1 to "ClearFilter" and change the Name to cmdClearFilter.

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

8.   Add the following code to cmdClearFilter:

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

Run the program and observe the following:

Your Form should look similar to this:

1.   Type B in the filter box located below Last header and type A in the filter box located below the Country header. You will notice that TDBGrid filters the two columns alphabetically. You now have a list of just the composers whose names start with B and who live in a country starting with A.

2.   Next, press the Clear Filter button and try typing 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.

Send us comments about this topic.
Copyright © GrapeCity, inc. All rights reserved.