Microsoft KB Archive/109710

From BetaArchive Wiki
Knowledge Base


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

Article ID: 109710

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q109710


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


Additional query words: multi-field index

Keywords: kbhowto kbprogramming KB109710