Access Database Design and Programming, Second Edition

Finding Records in a Recordset

The method used to search for a record in a recordset is different for indexed table-type recordsets than for other recordsets.

Finding Records in a Table-Type Recordset

To locate a record in an indexed table-type recordset, you use the Seek method. Note that the recordset’s Index property must be set before the Seek method can be used. The syntax of the Seek method is:

TableTypeRecSetVar.Seek comparison, key1, key2,...

where comparison is one of the following strings:

"<"
"<="
"="
">="
">"

and key1, key2,... are values corresponding to each field in the current index.

Notes

• The Seek method searches through the specified key fields and locates the first matching record. Once found, it makes that record current and the NoMatch property of the recordset 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 to (>=), or greater than (>), Seek starts its search at the beginning of the index. If comparison is less than (<) or less than or equal to (<=), Seek starts its search 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.

The code in Example 16.4 uses the Seek method on the Title index of BOOKS to find the first title that begins with the word “On.”

Example 16-4. The Seek Method ...