Manipulating ADO Recordsets - Using the Filter property of the recordset
(Page 4 of 5 )
In this case the Recordset's Filter property will be used. The Microsoft Jet 4.0 OleDB provider does not support the IViewFilter Interface used by ADO to filter, but the ADO engine will use the adUseClient automatically.
There is a Filter property associated with a Form, Report, or Table in MS Access; they are not the same as the Filter property of ADO's Recordset. All the forms in this tutorial are just a canvas for placing controls.
The filtering is based on a criteria such as "City='London'". The syntax of the criteria will be <FieldName> Operator<Value>. In this case, the <FieldName> will be City, the operator is the "=" sign and the value will be 'London'. The value, if it is a string, is in single quotes as shown; for dates it will be the # sign, and for the numbers it will be without any decoration. After the filtering action, the recordset will be the Current Cursor, and some of the properties such as AbsolutePosition, AbsolutePage, RecordCount, and PageCount will all be referenced to the filtered set, not the original recordset.
Create a new form and add a few controls as shown. For the ListBox set the RowSourceType property to 'ValueList'. When the form loads, you will see all the records; enter the name of a city in the textbox and click on the button. This applies the filter as shown in the code. The line where the filter property usage can be seen is highlighted. The city value is passed from the textbox to the variable 'strFilter' as shown. You can see that the record counts before and after the filter are different (just remove the comments for the message boxes). The rest of the code after the filter is used to call the poplist() procedure which populates the ListBox.
Option Compare Database
Private cn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Command0_Click()
Dim strFilter
Text3.SetFocus
strFilter = Text3.Text
'MsgBox (rst.RecordCount)
rst.Filter = "City= '" & strFilter & "'"
'MsgBox rst.RecordCount
Call poplist
End Sub
Private Sub Form_Load()
Dim str As String
str = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJayMy Documents
Retrieve.mdb;" & _
"Persist Security Info=False"
Dim strSql As String
'strSql = "Employees"
Set cn = New ADODB.Connection
cn.Open str
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
strSql = "Select LastName, FirstName, City,
HireDate from Employees"
rst.Open strSql, cn, adOpenKeyset
'MsgBox (rst.Fields.Count)
Set Me.Recordset = rst
Dim strrec
strrec = ""
While Not rst.EOF
strrec = strrec & rst.Fields.Item(0).Value & _
":" & rst.Fields.Item(1).Value & ":" & rst.Fields.Item(2).
Value & _
":" & rst.Fields.Item(3).Value & ";"
rst.MoveNext
'MsgBox (strrec)
Wend
Me.List1.RowSource = strrec
End Sub
Sub poplist()
Dim strg
strg = ""
While Not rst.EOF
strg = strg & rst.Fields.Item(0).Value & ":" & _
rst.Fields.Item(1).Value & ":" & rst.Fields.Item(2).Value & _
":" & rst.Fields.Item(3).Value & ";"
rst.MoveNext
Wend
Me.List1.RowSource = strg
End Sub
The next picture shows the result of running this form. The name 'London' has been typed into the textbox. As in the case of 'Sorting' you could also have used the Combo Box to show a sorted list of cities instead of a text box.
Next: Recordset's Find() method >>
More Database Articles
More By Jayaram Krishnaswamy
