Easy - Check why my RecordCount is not working

[Michel Jr]

Hi,

How can I get the number of records in a recordset?
I'm using ADO.

This is my code:

Rst.Open "Select PREmp.*, PRFil.EmpCod, PRFil.FilCod, PRFil.FilIde FROM PREmp INNER JOIN PRFil ON PREmp.EmpCod = PRFil.EmpCod", Connec

If Rst.RecordCount = 0 Then
vString = "No records available"
Combo1.AddItem vString
End If

Combo1.Text = Combo1.List(0) ' Show the first item

Note: the query above also can return an empty result.

When no records are returned, the message "No records avalilabe" should be inserted in the Combo, but it's not.
Is the RecordCount property ok? What's wrong in my code above?

Thanks,

Michel Jr.

[Ah Beng]

The problem is you did not set the cursor type of the recordset.
There are 4 cursor types: adOpenDynamic, adOpenForwardOnly, adOpenKeyset, and adOpenStatic. The default is adOpenForwardOnly.
For dynamic and forward only cursors, the RecordCount will always be -1.

[srikanth]

use < 1 instead of =0

[HeSaidJoe]

If mRS.BOF Then
  vString = "No records available" 
  Combo1.AddItem vString 
End If

[honeybee]

Originally posted by HeSaidJoe

If mRS.BOF Then
  vString = "No records available" 
  Combo1.AddItem vString 
End If

Perhaps checking both .BOF and .EOF ?

If mRS.BOF AND mRS.EOF Then
   MsgBox "No Records Found"
End If

.

[Desmond_P]

You realy need to do a rec.MoveLast here

[Chris]

yap, I agree with Desmond, whereby you need to move the recordset to the last position and then move it back to the first record location. This will ensure you've a correct recordcount.

[honeybee]

Agreed that by moving to the end and then returning to the beginning of the recordset, you get an accurate number of records in the recordset. But if you have a blank recordset, .MoveLast will fail. So you need a method to ascertain that there is at least one record in the recordset so you can safely use .MoveLast and that's when you can check for the .BOF and .EOF properties. When you make sure that the recordset is not empty, you can safely use the .MoveLast and .MoveFirst methods.

The following code would fail:

'There are no records in mRS
'
mRS.MoveLast
mRS.MoveFirst
MsgBox "Records : " & CStr(mRS.RecordCount)

But if you write it like this, it would always work:

If mRS.BOF And mRS.EOF Then
   MsgBox "There are no records"
Else
   mRS.MoveLast
   mRS.MoveFirst
   MsgBox "Records : " & CStr(mRS.RecordCount)
End If

.