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/region. 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 Main()
    On Error GoTo ErrorHandler

    ' 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='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    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
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not rstPublishers Is Nothing Then
        If rstPublishers.State = adStateOpen Then rstPublishers.Close
    End If
    Set rstPublishers = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If

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

'BeginFilter2VB

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

Public Sub Main()
    On Error GoTo ErrorHandler

    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='MySqlServer';" & _
        "Initial Catalog='Pubs';Integrated Security='SSPI';"
    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
    Exit Sub
    
ErrorHandler:
    ' clean up
    If Not rstPublishers Is Nothing Then
        If rstPublishers.State = adStateOpen Then rstPublishers.Close
    End If
    Set rstPublishers = Nothing
    
    If Not Cnxn Is Nothing Then
        If Cnxn.State = adStateOpen Then Cnxn.Close
    End If
    Set Cnxn = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndFilter2VB

See Also

Filter Property | RecordCount Property | Recordset Object