Recordcount property!!!

[hirenJ]

Hi all,

after much grief -- Ive concluded that the recordcount property doesnt work -- others have said that this is un unreliable property to call. Unfortunately I cant count my recordset using the SQL count function, hence ive written the code below ---
whats wrong with it??!!!!

the command object is set to an access query that uses a parameter object. i is a counter that loops through the records and then returns the count....

_______________________________________________________
cmd.Parameters.Append prmRespCode
Set rst = cmd.Execute

Dim i As Integer ' counts the records
If Not (rst.BOF And rst.EOF) Then ' recordset not empty
rst.MoveFirst
While Not rst.EOF
i = i + 1
rst.MoveNext
Wend
Else
MsgBox ("Recordset Null&quot
End If

Get_Contacts = CStr(i)
______________________________________________________


ANy ideas?

thankyou all

Hiren

)

 

[jebry]

Hi Hiren!

A couple of things come to me:

First, you should initialize i before going into your loop. Second, You should set your function inside the if statement so after the loop set your function to i and after the message box set your function to 0.

I would probably use the following code:

If rst.BOF = True And rst.EOF = True Then
MsgBox ("Recordset Null&quot
Get_Contacts = 0
Else
rst.MoveLast
rst.MoveFirst
Get_Contacts = rst.RecordCount
End If

Usually the movelast and movefirst will work to get you an accurate recordcount.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[hirenJ]

thanks for your help man, but it still isnt working!

Im using a parameter and command object that trigger an access query -- with this in mind the recordset is populated with

Set rst = cmd.Execute

Ive been told to set the cursortype of rst to static or keyset -- when do I need to do this? at the moment my recordset is still returning a null value...

Hj

 

[markphsd]

if you want to count records, just try a text box with this in it.
=count(*)