Home

Using the Filter Property

This sample project demonstrates how to use the ADO 2.1 Filter property with a criteria string to filter data in a Recordset.

Download a copy of the project.

Making the ADO Filter Project

  1. Create a DSN referring to the Northwind database. For information on creating DSNs, see these two MSDN Help topics:
  2. Open a new project in Visual Basic 6.0, and add a reference to the Microsoft ActiveX Data Objects 2.1 Library.
  3. Add two command buttons, a listbox, two textboxes and two label controls to the form. Set properties on the controls as listed in the following table.

    Object Property Value
    1st command button Name cmdDisplay
    1st command button Caption Display
    2nd command button Name cmdClear
    2nd command button Caption Clear
    1st text box Name txtUnitsInStock
    2nd text box Name txtUnitsOnOrder
    1st label Name lblUnitsInStock
    2nd label Name lblUnitsOnOrder
    listbox Name lstFilterResults
    3rd label Name lblFilterResults
    3rd label Caption ProductNames with UnitsInStock

    When you're done, the form might look like this.

    screen shot

  4. Copy the following code into the form. Option Explicit

    Private Sub cmdDisplay_Click()
        Dim cnNWind As Connection
        Dim rsNWind As Recordset

        'Open a recordset using the Execute method of the Connection object.
        Set cnNWind = New Connection
        cnNWind.Open "northwind"
        Set rsNWind = cnNWind.Execute("products", , adCmdTable)

        'Build the filter string using textbox entries.
        rsNWind.Filter = "UnitsInStock > " & txtUnitsInStock.Text & " AND UnitsOnOrder > " & txtUnitsOnOrder.Text

        'Connect the listbox to the recordset.
        Set lstFilterResults.DataSource = rsNWind

        'Display filtered contents in listbox.
        Do Until rsNWind.EOF
           lstFilterResults.AddItem rsNWind("ProductName") & Chr$(9) & rsNWind("UnitsInStock")
        rsNWind.MoveNext

        'Cleanup
        rsNWind.Close
        cnNWind.Close
        Set rsNWind = Nothing
        Set cnNWind = Nothing
    End Sub

    Private Sub cmdClear_Click()
        'Clear the listbox.
        lstFilterResults.Clear
    End Sub

    Private Sub Form_Load()
        'Set label captions.
        lblUnitsInStock.Caption = "UnitsInStock greater than..." & vbCrLf & "(Suggested value = 10)"
        lblUnitsOnOrder.Caption = "UnitsOnOrder greater than..." & vbCrLf & "(Suggested value = 10)"
    End Sub
  5. Save the project, then run it.
Last changed 9/20/00