Filter and RecordCount Properties Example (VB)

This example open a Recordset on the Publishers table in the Pubs database. It then uses the Filter property to limit the number of visible records to those publishers in a particular country. The RecordCount property is used to show the difference between the filtered and unfiltered recordsets.

'BeginFilterVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub FilterX()

    ' recordset variables
   Dim rstPublishers As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim SQLPublishers As String
   
    ' criteria variables
   Dim intPublisherCount As Integer
   Dim strCountry As String
   Dim strMessage As String

    ' open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
   
   ' open recordset with data from Publishers table
   Set rstPublishers = New ADODB.Recordset
   SQLPublishers = "publishers"
   rstPublishers.Open SQLPublishers, strCnxn, adOpenStatic, , adCmdTable

   intPublisherCount = rstPublishers.RecordCount

   ' get user input
   strCountry = Trim(InputBox("Enter a country to filter on (e.g. USA):"))

   If strCountry <> "" Then
      ' open a filtered Recordset object
      rstPublishers.Filter = "Country ='" & strCountry & "'"

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

   End If

    ' clean up
   rstPublishers.Close
   Cnxn.Close
   Set rstPublishers = Nothing
   Set Cnxn = Nothing

End Sub

'EndFilterVB

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.

'BeginFilter2VB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

Public Sub FilterX2()

   Dim rstPublishers As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strSQLPublishers As String
   Dim strCnxn As String

    ' open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn
   
   ' open recordset with criteria from Publishers table
   Set rstPublishers = New ADODB.Recordset
   strSQLPublishers = "SELECT * FROM publishers WHERE Country = 'USA'"
   rstPublishers.Open strSQLPublishers, Cnxn, adOpenStatic, adLockReadOnly, adCmdText
      
   ' print recordset
   rstPublishers.MoveFirst
   Do While Not rstPublishers.EOF
      Debug.Print rstPublishers!pub_name & ", " & rstPublishers!country
      rstPublishers.MoveNext
   Loop

    ' clean up
   rstPublishers.Close
   Cnxn.Close
   Set rstPublishers = Nothing
   Set Cnxn = Nothing

End Sub
'EndFilter2VB

Filter and RecordCount Properties Example (VB)

See Also