How to Use Seek and MoveNext to Find a Group/Range of Records (110497)

SUMMARY

The Seek method can search for a value only in an indexed field. Seek can find only one record at a time. The Seek method alone cannot find all duplicate field values.

After doing a Seek, to find a group of records that have indexed field values that are duplicates or within a given range, you must do a series of move methods (MoveNext or MovePrevious). After each move method in a loop, you must check the indexed field value until your criteria is exceeded. The indexed field values are automatically in alphabetical or numerical order.

The sample program below uses a Seek method, then uses MoveNext in a loop to roughly emulate the FindNext method.

NOTE: FindNext applies only to Dynasets or Snapshots. The Seek method applies only to Table object variables.

MORE INFORMATION

The Seek method is very fast, but doesn't support SQL or wildcard searches to find groups of articles. Seek is mainly useful for finding one, single record that matches or exceeds a given value.

You can use one of the following methods instead of the Seek method to find a group of records:

CreateDynaset method.
CreateSnapshot method.
Find methods (FindFirst, FindLast, FindNext, and FindPrevious), which work only on a Dynaset or Snapshot.
Move methods (MoveFirst, MoveLast, MoveNext, MovePrevious), which work on a Table object variable, Dynaset, or Snapshot.

The Seek method requires you to first set the current index with the Index property. This orders the records alphabetically or numerically.

Seek can use only the following comparison operators: >, >=, <=, <, =, and <>. When the comparison is =, >=, >, or <>, Seek starts at the beginning of the index and searches forward. When the comparison is <= or <, Seek starts at the end of the index and searches backward. Thus, if three or more records have duplicate values in the current index, the Seek method cannot locate the middle records. Seek can locate only the first or last of those records, depending upon the comparison operator used. A move method is required to locate those middle records. A MoveNext always moves forward one record from the current record found by a Seek, independent of the comparison operator that Seek used. MovePrevious moves one record previous.

Example: How to Use Seek and MoveNext to Find a Group of Records

The following sample program finds all records for which the PubID field is 2 in the BIBLIO.MDB database (9 records). The program uses one Seek to find the first record for which PubID is 2. The NoMatch property is False if the first match is found. From there onwards, the program uses MoveNext and tests MyTable!PubID in a loop to find all remaining records where PubID is

You could also modify this program to find a range of PubID field values.
Start a new project in Visual Basic. Form1 is created by default.

Add the following to the Form Load event code:

   Sub Form_Load ()

   form1.Show   ' In Load event, must Show form to make Print visible.
   Dim MyDB As Database, MyTable As Table, testval As Long
   ' Several duplicates exist in BIBLIO.MDB for PubID = 2 in Titles table.
   ' testval is the key value for which you want to Seek all duplicates:
   testval = 2
   Set MyDB = OpenDatabase("BIBLIO.MDB")      ' Open a database.
   Set MyTable = MyDB.OpenTable("Titles") ' Open a table.
   ' Sort the Titles table by the PubID indexed field, which is designed
   ' with duplicates OK:

   MyTable.Index = "PubID"
   MyTable.Seek "=", testval  ' Seek a record with PubID key = testval.
   If MyTable.NoMatch Then
      MsgBox "Match for " & testval & " was not found"
   Else
      Do
         Print MyTable!PubID & ": " & MyTable!Title
         x = MsgBox("Match was found. PubID = " & MyTable!PubID & ": ", 1)
         If x = 2 Then End  ' End if user clicks Cancel on message box.
         MyTable.MoveNext   ' Move to next record.
         If MyTable!PubID <> testval Then Exit Do  'Stop when past testval.
      Loop
   End If

Start the program (or press F5). Click OK multiple times to see all record titles where PubID is 2. Choose Cancel if you want to abort the MoveNext loop. Close the form to end the program.