KB109710 - ACC: How to Use Seek Method with Multiple-Field Index

MORE INFORMATION

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.

In Microsoft Access 1.x, multiple-field indexes are named Index1, Index2, Index3, Index4, or Index5, corresponding to the two-field index created in the table. In Microsoft Access 2.0 and later, multiple-field index names can be customized. All versions of Microsoft Access refer to a multiple- field primary key index as PrimaryKey.

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

Open the sample database Northwind.mdb (or NWIND.MDB in version 1.x or 2.0).

Create a new module and type the following procedure:

In Microsoft Access 2.0 and later:

      '********************************************************
      ' Declarations section of the module
      '********************************************************

      Option Compare Database
      Option Explicit

      '**************************************************
      ' This function uses Seek on a two-field PrimaryKey
      '**************************************************

      Function SeekOnMultiFields()
         Dim db As 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

In Microsoft Access 1.x:

      '********************************************************
      ' Declarations section of the module
      '********************************************************

      Option Compare Database
      Option Explicit

      '**************************************************
      ' This function uses Seek on a two-field PrimaryKey
      '**************************************************

      Function SeekOnMultiFields()
         Dim db As Database, tbl As Table
         Set db = CurrentDB()
         Set tbl = db.OpenTable("Order Details")

         tbl.Index = "PrimaryKey" ' Or "Indexn" for other multi-field index

         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

Type the following line in the Debug Window (or Immediate window in version 1.x or 2.0) and then press ENTER:
? SeekOnMultiFields()
Note that you receive the message "The Record is in the table."

ACC: How to Use Seek Method with Multiple-Field Index

SUMMARY

MORE INFORMATION

REFERENCES