Rs recordcount not working?

[JamieWarren09]

Hi guys, I am trying to show the number of records in my database...

how ever this is not working

Set rs = db.OpenRecordset("SELECT * FROM `Leads` WHERE `CurrentStatus` = " & "'" & opened & "' AND `Agent` = " & "'" & Combo1.Text & "'")
Label10.Caption = rs.RecordCount
rs.Close

[dennismcfarland]

Label10.caption requires a STRING. RS.Recordcount is an INTEGER. You will have to convert it to a STRING (str(rs.recordcount))

[techgnome]

No... that's not it at all... it'll do an implicit conversion (it shouldn't have to, you should be doing an explicit conversion, but that's besides the point) but that's not the problem.

Your posts contains two problems. I'll address the first. Saying "this is not working" with out telling us what isn't working does us all a disservice. First it forces us to guess what the problem is, just like dennis did... it also wastes your time reading useless posts like mine. It also wastes our time writing useless posts like this one in an effort to draw out the problem. Do you walk in to the doctor's office and say "it hurts" when they ask you what's wrong? No. Usually you'll go into a story about how you and your friends were biking, you hit a rock, fall off and broke your arm. Just like that's valuable information to the doc, knowing what's wrong with your code is valuable information to us. The fact that the code doesn't work is like saying water's wet. Well Duh... that's obvious... or you wouldn't be posting here. So what does "it doesn't work" mean? Did your toaster suddenly blow up? Did your door fall off the hinges? Did your fingers suddenly fall off? I'm a personal fan of that last one... it's the only thing I can think of for the lack of information in posts like these.

Fortunately, I know what the problem is... you have eels in your hover craft. And even more fortunately, I have a guide in my signature block that explains how to remove those pesky eels.

As for the second problem, I think I suspect what the problem with your code is, but I don't want to waste any more of your time or my time until you can provide more details... for all I know it could be a red herring. Unfortunately I only have a guide for eels, not red herring.

-tg

[VBClassicRocks]

Try rs.MoveLast and rs.MoveFirst before accessing the RecordCount.

BTW, those ` characters around the table & field names are unnecessary unless they have spaces in them.

[JamieWarren09]

Basically when I say it does not work... It means that label11.caption becomes "0"

so it's basically not counting how many records there is.

Thanks

[JamieWarren09]

no worries. fixed it.

[MarkT]

Did it have to do with cursor location?

[DataMiser]

I think it is using DAO, been a long time since I have used DAO but I do remember needing to do a move last to get an accurate recordcount back in VB5.

You really should be using ADO rather than the old DAO routines.

[firoz.raj]

Did it have to do with cursor location?

yes,Try the following way .

rs.cursorlocation=aduseclient
Set rs = db.OpenRecordset("SELECT * FROM `Leads` WHERE `CurrentStatus` = " & "'" & opened & "' AND `Agent` = " & "'" & Combo1.Text & "'")
Label10.Caption = rs.RecordCount
rs.Close

or

rs.cursorlocation=aduseclient
Set rs = db.OpenRecordset("SELECT * FROM `Leads` WHERE `CurrentStatus` = " & "'" & opened & "' AND `Agent` = " & "'" & Combo1.Text & "'")
rs.movefirst
rs.movelast
Label10.Caption = rs.RecordCount
rs.Close

[MarkT]

Yea, I didn't notice he was calling OpenRecordset. That sure ins't ADO.

[dee-u]

Why don't you just use COUNT(*) if you want to count how many record a recordset returns?

[Doogle]

Why don't you just use COUNT(*) if you want to count how many record a recordset returns?

For example:

Set rs = db.OpenRecordset("SELECT COUNT(*) AS OpenLeads FROM `Leads` WHERE `CurrentStatus` = " & "'" & opened & "' AND `Agent` = " & "'" & Combo1.Text & "'")
Label10.Caption = rs![OpenLeads]
rs.Close