>
Seek Method
Applies To
Recordset Object, Table-Type
Recordset Object.
Description
Locates the record in an indexed table-type Recordset
object that satisfies the specified criteria for the current
index and makes that record the current record.
Syntax
table.Seek comparison, key1,
key2...
The Seek method syntax has the following
parts.
Part |
Description |
|
table |
The name of an existing table-type Recordset
object that has a defined index as specified by the Recordset
object's Index property. |
comparison |
One of the following string expressions:
<, <=, =, >=, or >. |
key1, key2... |
One or more values corresponding to
fields in the Recordset object's current index, as
specified by its Index property setting. |
Remarks
You must set the current index with the Index
property before you use Seek. If the index identifies a
nonunique key field, Seek locates the first record that
satisfies the criteria.
Caution
If you edit the current record, be sure you save
the changes using the Update method before you move to
another record. If you move to another record without using Update,
your changes are lost without warning.
The Seek method searches through the
specified key fields and locates the first record that satisfies
the criteria specified by comparison and key1. Once
found, it makes that record current and the NoMatch
property is set to False. If the Seek method fails
to locate a match, the NoMatch property is set to True,
and the current record is undefined.
If comparison is equal (=), greater than or
equal (>=), or greater than (>), Seek starts at the
beginning of the index. If comparison is greater than
(<) or greater than or equal (<=), Seek starts at
the end of the index and searches backward unless there are
duplicate index entries at the end. In this case, Seek
starts at an arbitrary entry among the duplicate index entries at
the end of the index.
You must specify values for all fields defined in
the index. If you use Seek with a multi-column index, and
you don't specify a comparison value for every field in the
index, then you cannot use the equal (=) operator in the
comparison. That's because some of the criteria fields (key2,
key3, and so on) will default to NULL, which will probably not
match. Therefore, the equal operator will work correctly only if
you have a record which is all NULL except the key you're looking
for. It's recommended that you use the greater than or equal
operator instead.
The key1 argument must be of the same field
data type as the corresponding field in the current index. For
example, if the current index refers to a number field (such as
Employee ID), key1 must be numeric. Similarly, if the
current index refers to a Text field (such as Last Name), key1
must be a string.
There doesn't have to be a current record when you
use Seek.
You can use the Indexes collection to
enumerate the existing indexes.
Notes
- Always check the value of the NoMatch property
setting to determine whether the Seek method has
succeeded. If it fails, NoMatch is set to True
and the current record is undefined.
- To locate a record in a dynaset- or snapshot-type Recordset
that satisfies a specific condition, use the Find
methods. To include all records, not just those that
satisfy a specific condition, use the Move methods
to move from record to record.
You can't use the Seek method on an attached
table of any type because attached tables must be opened as
dynaset- or snapshot-type Recordset objects, which don't
support the Seek method. However, if you use the OpenDatabase
method to directly open an installable ISAM database, you can use
Seek on tables in that database.
See Also
AbsolutePosition Property; BOF, EOF
Properties; FindFirst, FindLast, FindNext,
FindPrevious Methods; Index Object; Index Property;
Move Method; MoveFirst, MoveLast, MoveNext,
MovePrevious Methods; NoMatch Property;
OpenDatabase Method.
Example
This example uses Seek to locate the first
record in the Publishers table where the PubID field is 3, using
the existing primary key index.
Dim dbsBiblio As Database, rstPublishers As Recordset
' Open a database.
Set dbsBiblio = DBEngine.Workspaces(0).OpenDatabase("Biblio.mdb")
' Open a table.
Set rstPublishers = dbsBiblio.OpenRecordset("Publishers")
rstPublishers.Index = "PrimaryKey" ' Define current index.
rstPublishers.Seek "=", 3 ' Seek record.
If rstPublishers.NoMatch Then...
This example uses the OpenDatabase method to
directly open an installable ISAM database and then uses Seek
to locate a record in a table in that database.
Dim dbsFoxData as Database, rstParts as Recordset
Dim varSaveHere as Variant
Set dbsFoxData = OpenDatabase("C:\FoxData", False, False,"Fox 2.5")
Set rstParts = dbsFoxData.OpenRecordset("PARTS.dbf", dbOpenTable)
' Choose record order and Seek index.
rstParts.Index = "PartNameIndex"
varSaveHere = rstParts.BookMark ' Save current location.
' Search for first instance of a chosen part.
rstParts.Seek "=", "Framis Lever"
If rstParts.NoMatch then ' Test for success.
rstParts.BookMark = varSaveHere ' Seek not successful.
...
Else ' Seek worked; use current record.
Debug.Print rstParts!PartName
End If
Example (Microsoft Access)
The following example creates a new Index
object on an Employees table. The new index consists of two
fields, LastName and FirstName. The procedure then uses the Seek
method to find a specified record.
Sub NewIndex()
Dim dbs As Database, tdf As TableDef, idx As Index
Dim fldLastName As Field, fldFirstName As Field, rst As Recordset
' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!Employees
' Return Index object that points to new index.
Set idx = tdf.CreateIndex("FullName")
' Create and append index fields.
Set fldLastName = idx.CreateField("LastName", dbText)
Set fldFirstName = idx.CreateField("FirstName", dbText)
idx.Fields.Append fldLastName
idx.Fields.Append fldFirstName
' Append Index object.
tdf.Indexes.Append idx
' Open table-type Recordset object.
Set rst = dbs.OpenRecordset("Employees")
' Set current index to new index.
rst.Index = idx.Name
' Specify record to find.
rst.Seek "=", "Fuller", "Andrew"
If rst.NoMatch Then
Debug.Print "Seek unsuccessful!"
Else
Debug.Print "Seek successful."
End If
End Sub
Example (Microsoft Excel)
This example opens PRODUCT.DBF (a dBASE IV table
located in the \Program Files\Common Files\Microsoft
Shared\MSquery folder), locates a record, and then copies the
values into cells B2:C2 on Sheet1. (On Windows NT™,
PRODUCT.DBF is located in the WINDOWS\MSAPPS\MSQUERY folder.)
Const sourceDir = "C:\Program Files\Common Files\Microsoft Shared\" _
& "MSquery"
Dim db As Database, rs As Recordset
Sheets("Sheet1").Activate
Set db = OpenDatabase(sourceDir, False, False, "dBASE IV")
Set rs = db.OpenRecordset("PRODUCT.DBF", dbOpenTable)
rs.Index = "PRODUCT"
rs.Seek "=", "1"
If rs.NoMatch Then
MsgBox "Couldn't find any records"
Else
ActiveSheet.Cells(2, 2) = rs.Fields("CATEGORY").Value
ActiveSheet.Cells(3, 2) = rs.Fields("PROD_NAME").Value
End If
rs.Close
db.Close