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