RecordSets

[Hunter666]

I am trying to open a recordset from a query and count the number of records in it. Very simple task you might think. However every which way people tell me to do it dosnt work. The code so far is as follows:

'Note EmailCount is the Query Name

Dim DB As Database
Dim RS As Recordset
Dim MyRecordCount As Integer


Set DB = CurrentDb
Set RS = DB.OpenRecordset(EmailCount)

With RS
.MoveFirst
.MoveLast
MyRecordCount = .Recordcount


MsgBox "there are " & MyRecordCount
End With

With this code i get a Type mismatch on the line ....
Set RS = DB.OpenRecordset(EmailCount)

Can anyone help?

 

[Fizzio]

Try this

Set RS = DB.OpenRecordset("EmailCount")

 

[Hunter666]

Thanks for pointing that out. I have done it like. that i have just made a typo putting it up on the BBS. But still Type Mismatch.

 

[Hunter666]

I cant seem to open any form of recordset. Ive tried both tables and queries and all i get is a Type Mismatch

 

[simongallop]

ARe you using 97 or 2k? Remember that in 2k you are using ADO instead of DAO. Try changing the Dim RS as Recordset to Dim RS as ADODB.Recordset if using 2k

 

[cogent1]

You can use either DAO or ADO provided you have both libraries loaded under Tools/ References. If DAO3.6 is loaded, your code should work

 

[Pat Hartman]

You can count the records without DAO or ADO by referencing the query in a DCount() function.

 

[Hunter666]

Can you tell me more about the Dcount()method??

Paul

 

[Hunter666]

I am using Access2002 XP. Surley there must be a very easy and simple way of counting these records... What i am trying to do is create some sort of Database statistics. i.e. there are so many companies on the database with so many email addresses and so many of the companies havnt been contacted yet and so on..... can anyone please help???

 

[Hunter666]

I am using Access2002 XP. Surley there must be a very easy and simple way of counting these records... What i am trying to do is create some sort of Database statistics. i.e. there are so many companies on the database with so many email addresses and so many of the companies havnt been contacted yet and so on..... can anyone please help???

 

[Fizzio]

Use the DCount() like this

Dim MyRecordCount As Integer
MyRecordCount = DCount("[NameOfIDField]","EMailCount")

MsgBox "there are " & MyRecordCount

HTH