How to: Find a Record in a Table-Type DAO Recordset
|Access Developer Reference|
When you use the Seek method to locate a record, the Microsoft Access database engine uses the table's current index, as defined by the Index property.
|If you use the Seek method on a table-type Recordset object without first setting the current index, a run-time error occurs.|
The following example opens a table-type Recordset object called Employees, and uses the Seek method to locate the record containing a value of lngEmpID in the EmployeeID field. It returns the hire date for the specified employee.
The Seek method always starts searching for records at the beginning of the Recordset object. If you use the Seek method with the same arguments more than once on the same Recordset, it finds the same record.
You can use the NoMatch property on the Recordset object to test whether a record matching the search criteria was found. If the record matching the criteria was found, the NoMatch property will be False; otherwise it will be True.
The following example illustrates how you can create a function that uses the Seek method to locate a record by using a multiple-field index:
In this example, the table's primary key consists of two fields: OrderID and ProductID. When you call the GetFirstPrice function with a valid (existing) combination of OrderID and ProductID field values, the function returns the unit price from the found record. If it cannot find the combination of field values you want in the table, the function returns the Null value.
If the current index is a multiple-field index, trailing key values can be omitted and are treated as Null values. That is, you can leave off any number of key values from the end of a Seek method's key argument, but not from the beginning or the middle. However, if you do not specify all values in the index, you can use only the ">" or "<" comparison string with the Seek method.