How to Use Seek and MoveNext to Find a Group/Range of Records
ID: Q110497
|
The information in this article applies to:
-
Microsoft Visual Basic Standard and Professional Editions for Windows, version 3.0
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.
Additional query words:
3.00
Keywords : kbcode
Version : 3.00
Platform : WINDOWS
Issue type :
|