Manipulating ADO Recordsets - Recordset's Find() method (Page 5 of 5 )
Provided you know the criteria, you can use the Find method of the RecordSet to find the first instance (first match) of the row that satisfies the criteria. The syntax for the Find() method, which takes the following four arguments, is as follows with the required argument:
RecordSet.Find CriteriaThe other three arguments are SkipRows, SearchDirection, and Start, which are optional. This method only supports searches on a single column. The criteria is a string which has a column name followed by a comparison operator. The comparison operator includes =, <, >, <=, >=, <=, and 'Like' with pattern matching. Searching backwards and forwards is possible. The variable SkipRows (long) shows how many records to skip before the search begins and the Start (variant) argument is a Bookmark for where the search should start. The example shown uses only the criteria.
Create a new form with a couple of controls as shown in the picture. The recordset in which we will be showing the usage of the Find() method will return four columns, FirstName, LastName, HireDate, and City. We will be doing searches in these fields (columns).
From the Combo Box shown you can pick any one of these columns for searching. The field names are hard coded into the value list for the Combo Box. This can be done by accessing the property window of the Combo Box.
For example, after choosing the FirstName from the Combo Box, the name 'Nancy' was entered (we know that it exists in the table) and then the Find button was clicked. The found recordset values are dumped to the text box. This is shown in the next picture. In this case the "=" operator was used and the usage of the find() method was rst.Find strfind. The strfind was tailored out of values being picked up by the Combo Box and the textbox as strfind = strcmb1 & " = " & "'" & fndstrg & "'". As mentioned previously, other operators can also be used, including the non-relational 'Like'.
Now that you have seen how the user interface works, it is time to look at the code which is shown in the next paragraph. The form's load event creates the recordset, a process that has been carried out a number of times in this and other tutorials. After the form is visible, and when a particular column name is picked from the Combo Box, the string variable strcmb1 will be extracted. Next, when a value is inserted into the textbox, the variable fndstrg will be extracted, and these two variables will be combined to produce the variable strfind in defining the argument for the find() method. If the value you enter into the textbox is not found, then an error handler is written to indicate that the searched element is not found. The highlighted statements are all needed for the find method. For the example shown the usage of the find(0 method reduces to rst.Find "FirstName='Nancy'".
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Private Sub Command0_Click()
On Error Goto Notfound
Dim strcmb1
Combo1.SetFocus
strcmb1 = Combo1.Text
MsgBox ("You have a total of " & rst.RecordCount & " records")
Dim fndstrg
Text5.SetFocus
fndstrg = Text5.Text
Dim strfind
strfind = strcmb1 & " = " & "'" & fndstrg & "'"
MsgBox (strfind)
rst.Find strfind
Me.Text10.Value = rst.Fields("FirstName").
Value & vbCrLf & rst.Fields("LastName").Value & vbCrLf &
rst.Fields("HireDate").Value & vbCrLf & rst.Fields("City").Value
Exit Sub
Notfound:
MsgBox ("Record for " & fndstrg & " Not found")
Resume Next
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, adOpenDynamic
MsgBox (rst.Fields.Count)
End Sub
Summary
This is an ADO related basic tutorial without any bells and whistles. The idea was just to show the basic usage of the properties and the methods used for manipulating the recordset based on the previous tutorials. In order to keep the matter simple, a very simple table with a reduced set of records was chosen. The code snippets presented were all tested on MS Access 2003 and the Windows XP Media Center Edition OS.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
