A BOF()/EOF() command that ignores deleted records? 2

[TomLeMes]

I am working with local cursors created from a SQL Server using the SQLEXEC command. Some records are not required and these are deleted. I have SET DELETED ON so that the deleted records do not show up. I have record mover buttons (First|Prev|Next|Last) to allow users to navigate between the records.

The code behind the buttons checks for BOF and EOF:

IF BOF() = .t. THEN
    * disable First and Prev buttons 
ENDIF

IF EOF() = .t. THEN
    * disable Next and Last buttons
ENDIF

However, if the record pointer is on the first visible record, but the first actual record in the cursor has been marked for deletion and then BOF() = .f. and so the buttons remain enabled. Same goes for EOF().

Is there a way to check for BOF/EOF which ignores deleted records? Note that I cannot PACK my cursor to fully get rid of the deleted records as "Function is not supported on remote tables

 

[TomLeMes]

Sorry everyone, I must be half asleep or something. My code is actually checking the RECNO(). e.g.

IF RECNO() = 1 THEN
    *disable First, Prev buttons
ENDIF
IF RECNO() = RECCOUNT() THEN 
    *disable Next, Last buttons
ENDIF

 

[ilyad]

What VFP version are you using?

You may change your statement to

if bof() or deleted()
disable Prev and First buttons
endif

Don't use recno() and reccount()

 

[TomLeMes]

Hi Ilyad, thanks for the reply. I'm using VFP 9.

I want the Prev/First buttons to be disabled when the user moves to the first record (as a visual indicator that they are now on the first record and can't go any further in that direction). But unless I'm missing something, it seems that BOF will not be true when the user is on the first record, only when a further SKIP-1 is issued. Isn't that right?

So effectively the check needs to be "are there any non-deleted records prior to this one?".

 

[Olaf Doschke]

Hi ilyad,

how would deleteed() help in that context? If you have set deleted on and recno()=1 is deleted and you navigate eg with GO TOP to the first record you will not be at recno()=1, nor at bof() nor at a deleted() record.

Tom,what you need to check is, if you get bof()=.T. after you Skip - 1 to check if you are at the first record and if Skip +1 leads to eof()=.t.

local lnRecno, llBegin, llEnd
lnRecno = recno("alias")
if bof("alias")
   llBegin = .T.
else
   skip -1 in alias
   llBegin = bof("alias")
endif
go m.lnRecno in alias

if eof("alias")
   llEnd = .T.
else
   skip 1 in alias
   llEnd = eof("alias")
endif
go m.lnRecno in alias

* enable/disable navbar buttons according to llBegin and llEnd.

That does not only work with SET DELETED ON, it also works when you SET ORDER TO some index.

Bye, Olaf.

 

[ilyad]

Olaf,

You're right. What was I thinking <g> In fact, at my older job that's the code we had to determine the status of these buttons. I may put this code here as well too.

Thanks.

BTW, for VFP9 I was thinking about using SELECT Max(PK), MIN(PK) (WITH BUFFERING) option to check if the record is at the top/bottom, but it is definitely an overkill for this task.

 

[TomLeMes]

Thanks Olaf, spot on!

Tom

 

[Auguy]

Tom, be careful with the above code if there are no records in the selected table. lnRecno will have a value of 1 and the "go m.lnRecno in alias" will give you an error. I use the following code that works even if there are no records in the table.

cRecord = Iif(Eof(), 'BOTTOM', Str(Recno()))
... Other Code as above to check BOF() and EOF() ...
Go &cRecord

Auguy
Sylvania, Ohio

 

[Olaf Doschke]

Auguy, good point.

you might test if Reccount()>0, and if not go bottom or go top. But then, if reccount()=0 you know for sure you are both at begin and end and no further testing is needed.

Bye, Olaf.