ACC2000: How to Use the Seek Method with a Multiple-Field Index (210445)

MORE INFORMATION

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

When you use the Seek method to find a record using the primary key, you need to supply a value for each field in the primary key. If you cannot supply values for all the fields in the primary key, use ">=" instead of "=" for the Comparison argument.

NOTE: "PrimaryKey" can refer to a multiple-field primary key. Multiple-field index names can be customized.

The following example demonstrates how to use the Seek method on a table with a multiple-field primary key.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new module and type the following procedure:

   '********************************************************
   ' Declarations section of the module
   '********************************************************
   
   Option Compare Database
   Option Explicit
   
   '**************************************************
   ' This function uses Seek on a two-field PrimaryKey
   '**************************************************
   
   Function SeekOnMultiFields()
      Dim db As DAO.Database, tbl As Recordset
      Set db = CurrentDB()
      Set tbl = db.OpenRecordset("Order Details")
   
      tbl.Index = "PrimaryKey"
      tbl.Seek "=", 10300, 68
      ' If you are only supplying one value, the statement above
      ' becomes:  tbl.Seek ">=", 10300
   
      If tbl.NoMatch Then
         MsgBox "Not a record. Try another"
      Else
         MsgBox "The Record is in the table"
      End If
      tbl.Close
   End Function
   
   					

3. Type the following line in the Immediate window, and then press ENTER:

   ? SeekOnMultiFields()

   Note that you receive the message "The Record is in the table."