RecordCount error

merry_fay

Registered User.
Local time
Yesterday, 18:41
Joined
Aug 10, 2010
Messages
54
I'm running the below code:

Dim db As Database
Dim rst As Recordset
Dim Counter As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Field] FROM tbl_Imports WHERE [Use] Is Not Null", dbOpenDynaset)
Counter = rst.RecordCount
MsgBox Counter

& the MsgBox is coming back with a result of 1. The SQL returns 7 records so this is what I'm trying to set the counter to. Can anyone see where I'm going wrong? Hugely perplexed!!

Thanks
merry_fay
 
Try this:

Code:
Dim db As Database
Dim rst As Recordset
Dim Counter As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Field] FROM tbl_Imports WHERE [Use] Is Not Null", dbOpenDynaset)
if rst.eof then
   Counter = 0
Else
   rst.movefirst
   rst.movelast
   Counter = rst.RecordCount
End if
MsgBox Counter
 
If all you are using the recordset for is the count you could use dcount.

Code:
NZ(dcount("[Field]","tbl_Imports","[Use] Is Not Null"),0)
 

Users who are viewing this thread

Back
Top Bottom