EOF 1

[dvannoy]

I am trying to display a message box to the user when there are no more records in the recordset. I cant get the message to display. I have tryed several different ways.


If rs.BOF = True And rs.EOF = True Then
MsgBox "You are Currently at the Last Record!..", vbExclamation, _
"End of Recordset Error"
Exit Sub
ElseIf Not rs.EOF = True And Not rs.BOF = True Then
rs.MoveNext
End If

Thanks in advance

 

[Thei]

Hi dvannoy.

If I'm not mistaken, BOF means Begin Of File. So how can you be in the BOF and EOF at the same time?

I quote: "If rs.BOF = True And rs.EOF = True Then"


Try something like this:

If rs.EOF = True Then
'your statement
End If

 

[BobWman]

Is this a process involving removing(deleting) records from the recordset? If so, you probably need to refresh or requery the recordset before testing for the empty condition.

 

[lavomar1]

If there's no record in your recordset...
both rs.BOF and rs.EOF will be True

If you want to know when your at the last record...
only rs.EOF will be True

When you're at the first record...
only rs.BOF will be True

So you must write :
********************************************************
If (rs.EOF = True) Then
MsgBox "You are Currently at the Last Record!..",
vbExclamation, _
"End of Recordset Error"
Exit Sub
ElseIf Not (rs.EOF = True) Then
rs.MoveNext
End If
********************************************************

 

[CajunCenturion]

One thing that I have done in some cases is to have two buttons on the form - 1 to MoveNext, and the other to MovePrevious. I then use the following snippet of code to disable these buttons when at the beginning or the end of the recordset.

If (fRst_PlanMaster.EOF = False) Then
fRst_PlanMaster.MoveNext
btnObserve(PB_ACP_NEXT).Enabled = Not fRst_PlanMaster.EOF
fRst_PlanMaster.MovePrevious
Else
btnObserve(PB_ACP_NEXT).Enabled = false
End If

If (fRst_PlanMaster.BOF = False) Then
fRst_PlanMaster.MovePrevious
btnObserve(PB_ACP_PREV).Enabled = Not fRst_PlanMaster.BOF
fRst_PlanMaster.MoveNext
Else
btnObserve(PB_ACP_PREV).Enabled = False
End If

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dvannoy]

Believe me, I have tryed those already and it will not display the message box or exit sub. I keep getting the microsoft error saying "Either EOF or BOF is true or current record has been deleted"

 

[CajunCenturion]

Breaking down your code

If rs.BOF = True And rs.EOF = True Then

At Thei has already stated, This can only happen if you have an empty record set - you cannot be at the Beginning and the End at the Same Time

ElseIf Not rs.EOF = True And Not rs.BOF = True Then

This may also cause problems (but not a runtime error)because if you're at the Beginning of the set, but not at the end, then you will not move because (Not rs.BOF) will return a false because BOF is True

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dvannoy]

What I did was use the rs.RecordCount.

If rs.RecordCount > 1 then
cmdNext.Enabled = True
cmdPrevious.Enabled = True
else
cmdNext.Enabled.False
cmdPrevious.Enabled = False
End If

That works..

 

[CajunCenturion]

The downside of that logic, is that recordcount value does not indicate your current position within the recordset.

You may have better success with the AbsolutePosition property.

cmdNext.Enabled = (rs.AbsolutePosition < rs.RecordCount)
cmdPrev.Enabled = (rs.AbsolutePosition > 1)



Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dvannoy]

Yaa, i thought of the same thing..and then I still will run into the problem of the user clicking the button and generating that same error message. I have never had this problem in the past. it should work with a simple if then statement.

 

[CajunCenturion]

You need to check the following two statements

cmdNext.Enabled = (rs.AbsolutePosition < rs.RecordCount)
cmdPrev.Enabled = (rs.AbsolutePosition > 1)

after every move done in the recordset.

Maybe you could try something like this Replace every MoveNext with the following function call

DidMove = MoveInRecordSet (rs, &quot;NEXT&quot

and every MovePrevious with

DidMove = MoveInRecordSet (rs, &quot;PREV&quot


Function MoveInRecordSet (rs as RecordSet, dir as String) as boolean

dim didmove as boolean

didmove = false
if (dir = &quot;NEXT&quot Then
if (rs.EOF = False) then
rs.movenext
didmove = true
end if
else
if (rs.BOF = False) then
rs.moveprevious
dodmove = true
end if
end if

cmdNext.Enabled = (rs.AbsolutePosition < rs.RecordCount)
cmdPrev.Enabled = (rs.AbsolutePosition > 1)

MoveInRecordSet = didmove

End Function

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CCLINT]

You cannot do it that way.
AbsolutePosition is Zero based.....

And rs.EOF is After the last record and rs.BOF is Before the first record.

cmdNext.Enabled = rs.AbsolutePosition < rs.RecordCount - 1
cmdPrev.Enabled = rs.AbsolutePosition > 0

 

[dvannoy]

Thank You !! I like that logic alot more then just a standard message box.

Worked great !

I will use that alot more.

 

[CajunCenturion]

One thing to note dvannoy, the provider that you are using may or may not support the absoluteposition property. You can't assume that it will always be available to you.

This is why I use the following blocks

If (fRst_PlanMaster.EOF = False) Then
fRst_PlanMaster.MoveNext
btnObserve(PB_ACP_NEXT).Enabled = Not fRst_PlanMaster.EOF
fRst_PlanMaster.MovePrevious
Else
btnObserve(PB_ACP_NEXT).Enabled = false
End If

If (fRst_PlanMaster.BOF = False) Then
fRst_PlanMaster.MovePrevious
btnObserve(PB_ACP_PREV).Enabled = Not fRst_PlanMaster.BOF
fRst_PlanMaster.MoveNext
Else
btnObserve(PB_ACP_PREV).Enabled = False
End If

instead of

cmdNext.Enabled = (rs.AbsolutePosition < rs.RecordCount)
cmdPrev.Enabled = (rs.AbsolutePosition > 1)

Clint, According to MSDN,
[i/Like the AbsolutePage property, AbsolutePosition is 1-based and equals 1 when the current record is the first record in the Recordset. You can obtain the total number of records in the Recordset object from the RecordCount property.[/i]
However, I would not take that to the bank, you may be right in that some providers don't support the property, and others may implement it as zero based.



Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CCLINT]

I forgot - my mistake.

AbsolutePosition is zero based in DAO.

In ADO it is as you say. I program alot in both and these differences are non-forgiving.

 

[dvannoy]

i am using sql as my DB. is that what you mean by provider? so far so good. does this not work using access JET as a provider?

Thanks

 

[CCLINT]

ADO and DAO will use Jet to access a MDB. So the question is (in this case) not the native language(Jet for an MDB) but the provider (ADO or DAO), which are wrappers for the native language.

You are probaly using ADO and therefore the AbsolutePosition is 1 based.