Filter, RecordCount Properties Example

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.

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 = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set rstPublishers = New ADODB.Recordset
	rstPublishers.CursorType = adOpenStatic
	rstPublishers.Open "publishers", strCnn

	' 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)

		With rstPublishersCountry
			If .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 & .RecordCount
				MsgBox strMessage
			End If
			.Close
		End With

	End If

End Sub

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.

Sub FilterX2()

	Dim rstPublishers As ADODB.Recordset
	Dim strCnn As String

	' Open recordset with data from Publishers table.
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set rstPublishers = New ADODB.Recordset
	rstPublishers.CursorType = adOpenStatic
	rstPublishers.Open "SELECT * FROM publishers " & _
		"WHERE Country = 'USA'", strCnn
		
	' Print current data in recordset.
	With rstPublishers
		.MoveFirst
		Do While Not .EOF
			Debug.Print !pub_name & ", " & !country
			.MoveNext
		Loop
	End With

	rstPublishers.Close

End Sub

© 1997 Microsoft Corporation. All rights reserved. Terms of Use.