Finding a Record in a Table-Type Recordset Object

You use the Seek method to locate a record in a table-type Recordset object. To locate a record in a non-table-type Recordset object, use one of the Find methods described in the next section. When you use the Seek method to locate a record, Microsoft Jet uses the table’s current index, as defined by the Index property.

The syntax for the Seek method is:

recordset.Seek comparison, key1, key2 ... key13

The table argument is the table-type Recordset object you’re searching. The Seek method accepts a number of arguments, the first of which is comparison, a string that determines the kind of comparison being performed. The following table lists the comparison strings you can use with the Seek method.

The key arguments are a series of one or more values (up to 13) that correspond to the field or fields that make up the current index of the Recordset object. Microsoft Jet compares these values to values in the corresponding fields of the Recordset object’s records.

The following example opens a table-type Recordset object on the Suppliers table, and uses the Seek method to locate the first record whose SupplierID field matches the value of the intID variable. It then changes the value of the CompanyName field to the value of the strNewName variable and saves the change with the Update method. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset

Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("Suppliers", dbOpenTable)
With rst
	' Define current index.
	.Index = "PrimaryKey"
	' Seek record.
	.Seek "=", intID
	' If SupplierID is found, change company name.
	If Not .NoMatch Then
		.Edit
		!CompanyName = strNewName
		.Update
	Else
		MsgBox "No record found for SupplierID: " & intID
	End If
	.Close
End With

If you use the Seek method on a table-type Recordset object without first setting the current index, a run-time error occurs.

The next 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, strDbPath is the path to the NorthwindTables database, lngOrderID is the value of a record’s OrderID field, and lngProductID is the value of a record’s ProductID field:

Function GetPrice(strDbPath As String, lngOrderID As Long, _
		lngProductID As Long) As Variant
	Dim dbs As Database, rst As Recordset

	Set dbs = OpenDatabase(strDbPath)
	' Open recordset on table.
	Set rst = dbs.OpenRecordset("Order Details", dbOpenTable)
	' Specify index for seek.
	rst.Index = "PrimaryKey"
	' Seek values for both fields in index.
	rst.Seek "=", lngOrderID, lngProductID
	' Check for matching record.
	If rst.NoMatch Then
		GetPrice = Null
		MsgBox "Couldn't find order detail record."
	Else
		GetPrice = rst!UnitPrice
	End If
	rst.Close
	dbs.Close
	Set dbs = Nothing
End Function

In this example, the table’s primary key consists of two fields, OrderID and ProductID. When you call the GetPrice function with a valid (existing) combination of OrderID and ProductID field values, the function returns the unit price from the found record. If the combination of field values you want can’t be found in the table, the function returns a 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 don’t specify all values in the index, you can use only the ">" or "<" comparison operator with the Seek method.