Manipulating ADO Recordsets - Filtering the Recordset (Page 3 of 5 )
The worst crime you can commit is to get all the server data to the client application. In order to minimize your call on resources it is always recommended that you get just what you need. That is, you should try to Filter your data; try to focus. The filter property does just that. For example, in our Employees table there are people coming from five different cities, and there are 11 people. This means there is more than one person coming from one or two of the cities. Let's say you just want to find those coming from "London." In this case you will filter so that, those not coming from "London" will be removed from the list.
Using the Where Clause in the query
This tutorial gives you only the basics for using the filter. There are other values such as the FilterGroupEnum enumeration that can be provided for the filter property. These will not be considered here.
In ADO you can do filtering two ways. You can do filtering using the "Where" clause in your original recordset retrieval process. This will bring in what you want, ready to use. Create a form and drop in controls as shown; the code that follows will be based on these controls.
The Combo0's click event will fill the comb with a sorted list of Cities, with no city being repeated. This is read-only and fills up the Combo Box fast. The "Distinct" keyword removes the duplicates from the list (London and Seattle would appear only once) and the Order By sorts the cities in the default ascending order. To initiate this event, double click on the Combo Box's label. Then click on the Combo Box's down arrow to reveal the cities as shown.
Now pick a city and click on the button at the bottom. This retrieves the recordset from a parametric query, which is highlighted, and uses the Combo Box's text as the argument. The rest of the code is just retrieving the recordset and stuffing it into a ListBox. Additionally a textbox (unbounded; see the design view) has been added to show the name of the (filtered) city that is chosen.
Option Compare Database
Private cn As ADODB.Connection
Private rst As ADODB.Recordset
Private Sub Combo0_DblClick(Cancel As Integer)
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 distinct City from Employees
order by City"
rst.Open strSql, cn, adOpenReadOnly
MsgBox (rst.Fields.Count)
Set Me.Recordset = rst
Dim strg
strg = ""
While Not rst.EOF
strg = strg & rst.Fields.Item(0).Value & ";"
rst.MoveNext
Wend
Me.Combo0.RowSource = strg
'rst.Close
'cn.Close
End Sub
Private Sub Command2_Click()
Combo0.SetFocus
cmbtxt = Combo0.Text
'MsgBox (Combo0.Text)
Me.List5.RowSource = ""
Text7.SetFocus
Text7.Text = cmbtxt
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, HireDate, " & _
"City from Employees where City = " & "'" & cmbtxt & "'"
MsgBox (strSql)
rst.Open strSql, cn, adOpenDynamic
Set Me.Recordset = rst
strinfo = ""
While Not rst.EOF
strinfo = strinfo & rst.Fields.Item(0).Value & " : " & _
rst.Fields.Item(1).Value & " : " & _
"rst.Fields.Item(2).Value & " : " & rst.Fields.Item(3).
Value & ";"
rst.MoveNext
MsgBox (strinfo)
Wend
Me.List5.RowSource = strinfo
rst.Close
cn.Close
End Sub
The result of this code is shown in the next picture where the value "Seattle" was picked in the Combo Box, and the Command Button "Employee Info by City" was clicked.
