Another Recordset Question.. 1

[cranebill]

When you create a recordset does it automatically move you to the first record?

Or do you have to put:

Rs1.MoveFirst

I have a recordset created that may produce 0 results... there for I get an Eof, BOF error if i use rs1.movefirst ...

What would be the proper way to move around this?

Bill

 

[ejmiller2]

Bill,

Im no expert, but i think if you have a recordset that produced 0 results and try to move to the first record you might get both EOF and BOF. I would try to put an if statement

If rs.EOF and rs.BOF Then

MsgBox "No Records"

Else
rs.MoveFirst

End If

or somethings of that sort. And i also think that for safety sake you will want to always move to the first record. Hope that helps,

eric

 

[nicsin]

Hello again,

I always code like this

with rs
    .Open sql, con
    if not (.eof and .bof) then
        .movefirst
        'do whatever
    else
        'It is empty so act appropriately
    end if
end with

 

[tinymind]

Bill

I would check the recordcount before moving around a recordset. Therefore you can catch the error of the recordset not producing any results at runtime.

'example
if rs1.recordcount > 0 then
'then create a do/loop to move around the recordset

else
'you have cought the recordset with no result and therefore execute some other code - or even disregard it

End if

Hope it helps

Tiny

Perfection is Everything
If it worked first time we wont be here!

 

[nicsin]

i draw your attention on the use of recordcount! Quoting from

http://www.w3schools.com/ado/prop_rs_recordcount.asp:

Note: This property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and -1 or the actual count for a dynamic cursor.

 

[cranebill]

I am not real sure what this means:

If the Recordset object supports AbsolutePosition and AbsolutePage properties or bookmarks (if Supports(adApproxPosition) or Supports(adBookmark) returns true), this property will return the exact number of records in the Recordset.

That part is a little over me at this time in my programming...

What I am trying to do is something like this...

If rs1.EOF = False And rs1.Bof = False then
count = 0
NumOfRec = rs1.recordcount
Do Until count = NumOfRec
Select Case Blah
Case Blah
End Select
counter = counter + 1
Loop
Else
End If

Would this work?

Bill

 

[cranebill]

Yes nicsin is correct....

I tried:

MsgBox Rs1.RecordCount

And it comes up with -1

hmmm...

 

[nicsin]

If you haven't explicitely set your cursor to be static or keyset it won't work. recordcount will return -1. I'd rather use:

If not (rs1.Bof And rs1.Eof) then
    rs1.MoveFirst
    Do While not rs1.Eof
        Select Case Blah
        Case Blah
        End Select
    Loop
Else
End If

which will work anyway.

 

[cranebill]

Using this example....


If not (rs1.Bof And rs1.Eof) then
rs1.MoveFirst
Do While not rs1.Eof
Select Case Blah
Case Blah
Rs1.MoveNext
End Select
Loop
Else
End If

Would this:
Rs1.MoveNext
cause an eof error since there is nowhere for it to go after the last record?

 

[nicsin]

Oops good point. I forgot to include

Rs1.MoveNext

. The only remark is that you should put it after the

end select

statement. To answer your question no it will work just fine. Try it!

 

[cranebill]

Well this is final code....

Seems to work...

If Not (Rs1.BOF And Rs1.EOF) Then
Rs1.MoveFirst
Do Until Rs1.EOF
Select Case Rs1!StatusID

Case 1
.Cells(12, 2).Value = Rs1!Rec 'Open
Total = Rs1!Rec
Case 2
.Cells(15, 2).Value = Rs1!Rec 'Lost
Total = Total + Rs1!Rec
Case 3
.Cells(14, 2).Value = Rs1!Rec 'Won
Total = Total + Rs1!Rec
Case 4
.Cells(13, 2).Value = Rs1!Rec 'Expired
Total = Total + Rs1!Rec
Case 5
.Cells(16, 2).Value = Rs1!Rec 'Closed
Total = Total + Rs1!Rec
Case Else
MsgBox "Status Entered Not In List", vbCritical, "Error"
End Select
Rs1.MoveNext
Loop
.Cells(11, 2).Value = "Total Quotes: " & Total
Else
End If


In this though I changed:
Do While not rs1.Eof
To
Do Until Rs1.EOF

just to play

Is there a difference between the 2?

Bill

 

[nicsin]

No difference at all. The code seems ok but won't you code for the possibility that the recordset is empty (I mean the else block)?

 

[tinymind]

nicsin

I happy to be corrected as I am still young and I am always looking to better myself with new methods of working ... thanks for the link and I will take that onboard ...



Tiny

Perfection is Everything
If it worked first time we wont be here!

 

[nicsin]

Hey tiny,

I didn't mean to correct you as I have used your suggestion some times. I only wanted to draw everybody's attention because I remember the first time I faced this problem and spent hours just to discover that I used a different cursortype (usually use dynamic cursor).

Take care!

 

[cranebill]

Yes there will be something in the else... but that part is easy

What is all this cursor stuff... how do you set it... what is it used for... Just curious for future reference

Bill

 

[nicsin]

An ADO recordset has many attributes and methods and basically specifies the behaviour of the recordset, which you can find and learn here (it is too big a subject to explain in a few words):

http://www.w3schools.com/ado/ado_ref_recordset.asp

Another interesting attribute of the recordset, which wasn't mention in this thread, is the LockType. It can also have different types to accommodate different needs.

 

[cranebill]

Thanx again... that website has alot of good information

Bill