how to avoid either eof or bof error

[chandru8]

ho to all
iam retrieving data from the access table when there is no data it saying either EOF or BoF true . how to avoid this.
If IsNull(rs.Fields(0)) = True Then

B = 1
Else
B = rs.Fields(0) + 1
End If

if there is no data b = 1 otherwise i need to increment that value B by 1
thanks to all

[Killer42]

You could try checking .RecordCount first, before using the fields.

[chandru8]

I Tried Record Count For Many Times But It Doesn't Work For Me .
Rs.recordcount Says -1
Is It Correct

[debasisdas]

That means your query is not fetching anything.

[chandru8]

data present in the table also recordcount says -1 only and

for this condition

if isnull(rs.fields(0)) = false then

for the above condition if the table contain data or not it returns only false .

[debasisdas]

Try to execute your query in the backend and check if that returns any record or not .

[VBWheaties]

The reason recordcount is -1 is because it has not traversed the set of records in the recordset. In other words, it doesn't know yet, how many records are in the recordset.

To get the record count:

 rs.Movelast
 rs.Movefirst
 Msgbox "There are " rs.RecordCount  & " records"
 

However, this will be ineffective if there is no data.
The best way to check for no data is to check both BOF and EOF as they will be true if no data exists.

If ((rs.EOF) And (rs.BOF)) Then
   Msgbox "no data"
End If  
 

[dgzo]

Trying

if not rs.eof then
'...
else
'...
end if
 

could work also. So if your query does not return any records, the program does not execute what is inside the if part. And would not show any error.

[Killer42]

The reason recordcount is -1 is because it has not traversed ...

I just wanted to clarify this a bit, in case chandru8 didn't quite get it. When you first execute the query, the record count will be set to either 0 (no data) or 1 (some data). So you can use it as an indicator of whether something was found, but won't know the actual number of records until you go to the end of the recordset.

Very annoying, but it has been that way for a long time.

[chandru8]

thanks to all for your reply
Hi Killer
I tried this one and i fixed the problem by creating a new table . i dont know for that particular table its not working
from the begining the record count is not working for me can you give me some example for that
instead of rs.recordcount i used select count(field name)

thanks.

[Neelesh2007]

I think there is no need for moving Records to get Record count. Refresh the recordset & then use RecordCount. I think it will work.
e.g.
rs.refresh
if rs.recordcount=0 then
.
.
Else
.
.
Endif

[Killer42]

Checking EOF and BOF is probably the better option, anyway.

I don't have any examples handy for recordcount, and don't actually do a lot of work with it. I just remember the problems with the records not being being until they're accessed.