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.

Start a new project.
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).
Place an ADO Data control, a True DBGrid control and a command button on the form.
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.
Set the TDBGrid1 DataSource to Adodc1.
Change the caption of Command1 to "Clear Filter" and change the Name to cmdClearFilter.
Open the TDBGrid property pages and select the Splits tab. Set the FilterBar property to True.
v

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

Run the program and observe the following:

Your Form should look similar to this:

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.
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.