DOCERR: More Info & Doc Fixes for FindFirst & Seek Methods (113903)

SUMMARY

The status of database objects after a search operation (FindFirst or Seek) is not clearly documented. Data Controls, Dynasets, and Snapshots all maintain a valid record while Table objects actually have "No Current Record" after an unsuccessful Seek (NoMatch = true).

In addition, there are two documentation errors:

The "Language Reference" manual gives incorrect information regarding the FindFirst, FindLast, FindNext, and FindPrevious methods in the second paragraph on Page 200:
If no matching records are found, the NoMatch property is TRUE and the current record remains the same as before the Find method was used.
The "Language Reference" manual gives incorrect information regarding the Seek method in the first paragraph on Page 504:
NOTE: Always inspect the value of the NoMatch property of the recordset to determine whether the SEEK method has succeeded. If it fails, NoMatch is TRUE and the current record is unchanged"

Both statements are incorrect with regard to the position of the current record. The behavior of the current record depends on the type of object that has been used in the search method (FindFirst or Seek). The behavior of Visual Basic data objects is outlined below.

MORE INFORMATION

FindFirst - Data Controls and Dynasets

By in large these objects behave as would be expected. When the Find method fails to find a match the NoMatch property of the RecordSet is set to TRUE. However, the current record does change. It is set to the first record in the recordset.

Seek - Table Objects

When the Seek method fails to find a match, the NoMatch property of the RecordSet is set to TRUE, as it is with the Find method. However, the current record also changes. In fact it becomes invalid, so that it returns the error message, "No Current Record" if you attempt to access it.

Steps to Reproduce Behavior

Start a new project in Visual Basic. Form1 is created by default.
Add three command buttons (Command1, Command2, and Command3) to the lower-right corner of Form1 and change their Caption properties to: Create DB, Seek, and Find.

Add the following code to the Command1_Click event procedure:

   Sub Command1_Click ()
      Const DB_LANG_GENERAL = ";LANGID=0x0809;CP=1252;COUNTRY=0"
      Const DB_DOUBLE = 7
      Const DB_TEXT = 10

      Dim db As Database
      Dim tb As table
      Set db = CreateDatabase(App.Path + "\NUMBERS.MDB", DB_LANG_GENERAL)

      Dim td As New tabledef
      Dim F1 As New field
      Dim F2 As New field

      td.Name = "Amounts"

      F1.Name = "Desc"
      F1.Type = DB_TEXT
      F1.Size = 35
      td.Fields.Append F1

      F2.Name = "Num1"
      F2.Type = DB_DOUBLE
      td.Fields.Append F2

      db.TableDefs.Append td

      Dim TempIndex As New Index
      TempIndex.Name = "test"
      TempIndex.Fields = "num1"
      db.TableDefs("Amounts").Indexes.Append TempIndex

      Set tb = db.OpenTable("Amounts")

      For I = 0 To 10
         tb.AddNew
         tb.Fields("Desc") = "hello" & I
         tb.Fields("Num1") = CDbl(I * 10)
         tb.Update
      Next I

      tb.Close
      db.Close
   End Sub

Add the following code to the Command2_Click event procedure:

   Sub Command2_Click ()
      On Error GoTo eh
      Dim db As Database
      Dim tb As table

      Set db = OpenDatabase(App.Path + "\NUMBERS.MDB")
      Set tb = db.OpenTable("Amounts")

      Print "====Test of Seek Method===="
      tb.Index = "test"
      tb.MoveFirst
      Print "MoveFirst  Values= "; tb(0); tb(1)
      tb.Seek "=", 50      ' Finds valid record.
      Print "Seek = 50   NoMatch="; tb.NoMatch; " Values= "; tb(0); tb(1)
      Print

      tb.MoveNext
      Print "MoveNext   Values= "; tb(0); tb(1)
      tb.Seek "=", 500     ' Doesn't find valid record.
      Print "Seek = 500  NoMatch="; tb.NoMatch; " Values= "; tb(0); tb(1)

      tb.Close
      db.Close
      Exit Sub

      eh:
         Print
         Print "*** Error Occurred displaying Current Record ***"
         Print
         Resume Next
   End Sub

Add the following code to the Command3_Click event procedure:

   Sub Command3_Click ()
      Dim db As Database
      Dim ds As Dynaset    ' OR Dim ds As snapshot

      Set db = OpenDatabase(App.Path + "\NUMBERS.MDB")
      Set ds = db.CreateDynaset("Amounts")
      ' OR Set ds =    db.CreateSnapshot("Amounts")

      Print "====Test of FindFirst Method===="
      ds.MoveFirst
      Print "MoveFirst      Values= "; ds(0); ds("Num1")
      ds.FindFirst "num1 = 50"

      ' Enter the following two lines as one, single line:
      Print "FindFirst 50   NoMatch="; ds.NoMatch; " Values= ";
       ds(0); ds("Num1")
      Print

      ds.MoveNext
      Print "MoveNext       Values= "; ds(0); ds("Num1")
      ds.FindFirst "num1 = 500"

      ' Enter the following two lines as one, single line:
      Print "FindFirst 500  NoMatch="; ds.NoMatch; " Values= ";
       ds(0); ds("Num1")
      Print

      ds.Close
      db.Close
   End Sub

Press the F5 key to run the program. Click the Command1 button to create the NUMBER.MDB database. Then click the Command2 button to perform a Seek on the NUMBERS.MDB database, and click the Command3 button to perform a FindFirst on the NUMBERS.MDB database. Review the results that are printed on Form1.