Recordset Type Mismatch

[foxhound]

The following works in a query but I am getting a type mismatch when used in VBA. Can anyone tell me what I am doing wrong? (Using Access 2000, have dimmed the recordset as DAO and "Super" is a number field)

set numSubs = "SELECT DISTINCTROW Count([Employee Snapshot].EmpName) AS SubCount FROM [Employee Snapshot] WHERE ((([Employee Snapshot].Super)=3014912));"

 

[bat17]

I think that you probably have the wrong bit with "Set numSubs"

What data type have you dim'ed numSub as?

Peter

 

[foxhound]

Hi Peter. I have dim'ed as follows:

Dim numSubs As DAO.Recordset

 

[bmacr]

It should be a string.

 

[bat17]

Try something like

Dim dbs As DAO.Database
Dim strSql As String
Dim numSubs As DAO.Recordset

Set dbs = CurrentDb()
strSql = "SELECT DISTINCTROW Count([Employee Snapshot].EmpName) AS SubCount FROM [Employee Snapshot] WHERE ((([Employee Snapshot].Super)=3014912));"
Set numSubs = dbs.OpenRecordset(strSql)

'clean up
Set numSubs = Nothing
Set dbs = Nothing
End Sub


HTH

Peter

 

[foxhound]

Cool...thank you very much!

 

[foxhound]

Oh, got another question...I have "CountofSubs" dim'ed as an integer. If no records exist in this recordset, how can I have "CountofSubs" equal 0 (zero) without giving me the run-time error 3265?

 

[bat17]

use the NZ() function to turn null's into a 0

peter

 

[foxhound]

Hi Peter. Thanks for the reply. I checked this board for examples of how to do a NZ() function and there wasn't anything. I also checked help in access and it didn't make it any clearer for me Would you mind explaing a little more how I can use that function in this scenario?

 

[bat17]

I think that you will want something like

Set numSubs = dbs.OpenRecordset(strSql)
CountofSub = Nz(numSubs("subCount"))

If this causes an error due to no current record then try

Set numSubs = dbs.OpenRecordset(strSql) 
If numSubs.EOF = numSubs.BOF Then ' no records found
   CountofSub = 0
Else
   CountofSub = numSubs("subCount")
End If

HTH

Peter