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

ID: Q109710

The information in this article applies to:

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

SUMMARY

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

This article shows you 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).

   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

   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.

Additional query words: multi-field index

Keywords          : kbprg
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 20, 1998