MDAC 2.5 SDK - ADO

Filter and RecordCount Properties Example (VB)

See Also

This example uses the Filter property to open a new Recordset based on a specified condition applied to an existing Recordset. It uses the RecordCount property to show the number of records in the two Recordsets. The FilterField function is required for this procedure to run.

Public Sub FilterX()

   Dim rstPublishers As ADODB.Recordset
   Dim rstPublishersCountry As ADODB.Recordset
   Dim strCnn As String
   Dim intPublisherCount As Integer
   Dim strCountry As String
   Dim strMessage As String

   ' Open recordset with data from Publishers table.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
   Set rstPublishers = New ADODB.Recordset
   rstPublishers.CursorType = adOpenStatic
   rstPublishers.Open "publishers", strCnn, , , adCmdTable

   ' Populate the Recordset.
   intPublisherCount = rstPublishers.RecordCount

   ' Get user input.
   strCountry = Trim(InputBox( _
      "Enter a country to filter on:"))

   If strCountry <> "" Then
      ' Open a filtered Recordset object.
      Set rstPublishersCountry = _
         FilterField(rstPublishers, "Country", strCountry)

      If rstPublishersCountry.RecordCount = 0 Then
         MsgBox "No publishers from that country."
      Else
         ' Print number of records for the original
         ' Recordset object and the filtered Recordset
         ' object.
         strMessage = "Orders in original recordset: " & _
            vbCr & intPublisherCount & vbCr & _
            "Orders in filtered recordset (Country = '" & _
            strCountry & "'): " & vbCr & _
            rstPublishersCountry.RecordCount
         MsgBox strMessage
      End If
      rstPublishersCountry.Close

   End If

End Sub

Public Function FilterField(rstTemp As ADODB.Recordset, _
   strField As String, strFilter As String) As ADODB.Recordset

   ' Set a filter on the specified Recordset object and then
   ' open a new Recordset object.
   rstTemp.Filter = strField & " = '" & strFilter & "'"
   Set FilterField = rstTemp

End Function

Note   When you know the data you want to select, it's usually more efficient to open a Recordset with an SQL statement. This example shows how you can create just one Recordset and obtain records from a particular country.

Public Sub FilterX2()

   Dim rstPublishers As ADODB.Recordset
   Dim strCnn As String

   ' Open recordset with data from Publishers table.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
   Set rstPublishers = New ADODB.Recordset
   rstPublishers.CursorType = adOpenStatic
   rstPublishers.Open "SELECT * FROM publishers " & _
      "WHERE Country = 'USA'", strCnn, , , adCmdText
      
   ' Print current data in recordset.
   rstPublishers.MoveFirst
   Do While Not rstPublishers.EOF
      Debug.Print rstPublishers!pub_name & ", " & _
         rstPublishers!country
      rstPublishers.MoveNext
   Loop

   rstPublishers.Close

End Sub