MORE INFORMATION
The Seek and find methods differ in performance and in the type of
recordsets to which they apply:
- The find methods (FindFirst, FindLast, FindNext, and FindPrevious) apply
to Dynasets and Snapshots but not to Table objects. Conversely, the Seek
method is available only on the Table object.
- The Seek method is significantly faster than the find methods. It is
also more flexible because you can change the Index property of the
Table object to change the order of the Seek. For intensive searches,
you may want to create a Table object so that you can use the Seek
method along with the find methods on the open Dynasets.
FindFirst, FindLast, FindNext, FindPrevious Methods
The FindFirst, FindLast, FindNext, and FindPrevious methods locate the
first, last, next, or previous record, respectively, that satisfies the
specified criteria and makes that record the current record. These methods
are referred to as the find methods and have the following syntax:
<recordset>.FindFirst <criteria>
where <recordset> and <criteria> are defined as follows:
<recordset> is the Recordset property of a data control or an object
variable identifying a Dynaset or Snapshot.
<criteria> is a string expression specifying the records that you
want. The string is the WHERE clause in an SQL string without the
word WHERE.
If the recordset contains more than one record that matches the criteria,
FindFirst locates the first occurrence, FindNext locates the next
occurrence, and so on. You can follow a find method with a move method,
such as MoveNext, which moves to the next record regardless of whether it
matches any criteria. If no matching records are found, the NoMatch
property is True and the current record remains the same as it was before
the find method was used.
NOTE: With a data control, if an Edit or AddNew operation is pending when
you use one of the find or move methods, the Update method is automatically
invoked if not intercepted during the Validate event.
CAUTION: In the Professional Edition of Visual Basic, if you are not using
a data control and use one of the find or move methods while an Edit or
AddNew method is pending, any existing changes will be lost and no error
will occur. An Edit or AddNew will be pending until an Update occurs. For
more information, see the Update method in the Help menu.
Example Code for FindFirst Method
The following example creates a Dynaset, and then uses FindFirst to locate
the first record satisfying the title condition:
Sub Form_Load ()
Dim MyCriteria As String, MyDB As Database, MySet As Dynaset
MyCriteria = "State = 'NY'" ' Define search criteria.
Set MyDB = OpenDatabase("BIBLIO.MDB")
' Create a Dynaset based on the Publishers table:
Set MySet = MyDB.CreateDynaset("Publishers")
' Find first matching record:
MySet.FindFirst MyCriteria
If Not MySet.NoMatch Then
MsgBox "match was found"
Else
MsgBox "match was not found"
End If ' For a data control, you can use Data1.Recordset.NoMatch
' Find next matching record:
MySet.FindNext MyCriteria
If Not MySet.NoMatch Then
MsgBox "match was found"
Else
MsgBox "match was not found"
End If
End Sub
Seek Method
The Seek method locates a record that meets the specified criteria for the
current index in an indexed table and makes it the current record. The Seek
method has the following syntax:
<table>.Seek <comparison>, <key1>, <key2>, ...
where the arguments are defined as follows:
<comparison> is one of the following string expressions:
<, <=, =, >=, >, or <>
<key1>, <key2>, ... one value for each field in the table's current
index.
To use the Seek method, you must first use the OpenTable method to create
an object variable for the table.
Seek searches through the specified Table using the current index and
locates the first record satisfying the criteria specified by comparison
and the key values (key1, key2...) and makes it the current record.
You must set the current index with the Index property before you use Seek.
If the index identifies a non-unique-key field, Seek locates the first
record satisfying the criteria.
When the comparison is =, >=, >, or <>, Seek starts at the beginning of the
index and searches forward. When the comparison is <= or <, Seek starts at
the end of the index and searches backward.
If <table> doesn't refer to an open table, or if there is no current index,
an error occurs.
Always inspect the value of the NoMatch property of the recordset to
determine whether each Seek method has succeeded. If Seek fails, NoMatch is
True and the current record is unchanged.
Example Code for Seek Method
The following example uses Seek to locate the first record in the
Publishers table where the PubID field is 3, using the existing primary key
index:
Sub Form_Load ()
Dim MyDB As database, MyTable As table
Set MyDB = OpenDatabase("BIBLIO.MDB") ' Open a database.
Set MyTable = MyDB.OpenTable("Publishers") ' Open a table.
MyTable.Index = "PrimaryKey" ' Define current index.
MyTable.Seek "=", 3 ' Seek record.
If MyTable.NoMatch Then
MsgBox "match was not found"
Else
MsgBox "match was found"
End If
End Sub