The following example sets the index for a table-type Recordset object to the primary key. "PrimaryKey" is the default name of an Index object if the index corresponds to the primary key set in table Design view. Next, the procedure asks the user for a value to search on and locates the record with a matching key field value. Note that the current index must be set before certain operations, such as the Seek method, can be performed on a table-type Recordset object.
Sub UsePrimaryKey()
Dim dbs As Database, rst As Recordset
Dim fld As Field, strInput As String
' Return reference to current database.
Set dbs = CurrentDb
' Create table-type Recordset object.
Set rst = dbs.OpenRecordset("Orders", dbOpenTable)
' Set current index.
rst.Index = "PrimaryKey"
strInput = InputBox("Enter the OrderID on which to search.")
' Locate record.
rst.Seek "=", strInput
If Not rst.NoMatch Then
For Each fld in rst.Fields
Debug.Print fld.Name; " "; fld.Value
Next fld
End If
rst.Close
Set dbs = Nothing
End Sub