How to Save & Restore Record Pointer Without Negative EOF Mark (147169)

MORE INFORMATION

A typical example to save and restore record pointer information is as follows:

LOCAL lnRecNumber
SELECT Labels
**********************
* Save record Number *
**********************
lnRecNumber= IIF(EOF('Labels', -1, RECNO('Labels'))
SELECT * FROM LABELS   &&Or any processing

************************
* Restore record Number*
************************
IF lnRecNumber > 0
   GOTO (lnRecNumber)
   ELSE
   GO BOTTOM
   SKIP
ENDIF
				

In this example, the negative number -1 is used to flag the end of the file (EOF). However, using this approach might not yield the expected results when you work with local views or when buffering is enabled.

When you enable buffering on a table, Visual FoxPro writes the records to a buffer and permits edits until you issue a TABLEUPDATE() command. Every new record is assigned a negative number while the data is buffered. The record number is decremented as you add records. For example, the first record you add has a value of -1, the second is -2, and so on. The following code sample illustrates how Visual FoxPro assigns record numbers when table buffering is enabled.

Sample Code

SET EXCLUSIVE OFF
CREATE TABLE TEST (cName C(10))
SET MULTI ON
=CURSORSETPROP("Buffering",5)
WAIT WINDOW "Appending Data" NOWAIT
? "Record number before the data is committed"
=AddRec("Name 1")
=AddRec("Name 2")
=AddRec("Name 3")
WAIT WINDOW "Commiting the Changes" NOWAIT
=TABLEUPDATE(.T.)
WAIT CLEAR
?
? "Record number after the data is committed"
SCAN
  ? "Record Number:"
  ?? RECNO()
ENDSCAN

FUNCTION AddRec
LPARAMETER cName
INSERT INTO test VALUES (cName)
? "Record Number: "
?? RECNO()
				

Alternative to Using a Negative Number

The alternative to using a negative number to store the end of the file is to use the .NULL. value. In the first code sample, the line that read:

   lnRecNumber= IIF(EOF('Labels', -1, RECNO('Labels'))
				

can be changed to:

   lnRecNumber= IIF(EOF('Labels', .NULL., RECNO('Labels'))
				

You can use the ISNULL() function to verify whether the record pointer is at the end of the file. For more information about ISNULL(), search for ISNULL() in the Help file.