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

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes how to use the Seek method when your table's primary key or index includes more than one field. Note that when you use the Seek method on multiple fields, the Seek fields must be in the same order as the fields in the underlying table. If they are not, the Seek method will fail.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

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.

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

2. 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
   
   

3. 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."

REFERENCES

For more information about the Seek method, search for "Seek method," and then "Seek Method (DAO)," using the Microsoft Access 97 Help Index.