Recordset not counting (1 Viewer)

[Wolfgang]

Hi,

My query is as follows;

Dim dbs As Database, rst As Recordset, rst1 As Recordset, iCount As Integer, iCount1 As Integer
' Return reference to current database.
Set dbs = CurrentDb
' Open table-type Recordset object.
Set rst = dbs.OpenRecordset("Bug")
Set rst1 = dbs.OpenRecordset("select count (closed)as count1 from bug where closed = -1")
'("SELECT count Bug FROM Bug WHERE (((Bug.Closed)=True))")

iCount = rst.RecordCount
iCount1 = rst1.RecordCount
txttotal.value = iCount
intCompleted.value = iCount1
rst.Close
Set dbs = Nothing

I am only looking to rst1, the problem is that the result is 0 or 18 (the total no of records).

Any advice?

Thanks for a great forum

Wolfgang

 

[Jon K]

It's better to type the select statement for rst1 properly.
Assuming intCompleted is a text box, change the last few lines of code to:-

-------------------------------
Set rst1 = dbs.OpenRecordset("select count(closed) as count1 from bug where closed = -1")

txttotal = rst.RecordCount
intCompleted = rst1!count1 'the # returned by the select statement
rst.Close
rst1.Close
Set dbs = Nothing
-------------------------------

Note that recordset rst1 returns at most one record when there are records with Closed=-1, so rst1.RecordCount is either 0 or 1.

 

[Wolfgang]

Dear Jon,

Cool, thanks for the speady reply!

It worked like a dream