Type mismatch Error after upgrading to Access 2010

[twm07073]

We are in process of upgrading our access XP database to Access 2010. I am testing this one database and getting Runtime error 13 “Type mismatch” when trying to open the form. The error is at "Set varRecSet = dbs.OpenRecordset(strPayQuery)"



“strPayQuery = "SELECT Sum(SYSADM_PAYABLE_LINE.AMOUNT) AS MATERIAL " & _
"FROM SYSADM_PAYABLE LEFT JOIN SYSADM_PAYABLE_LINE ON " & _
"SYSADM_PAYABLE.VOUCHER_ID = SYSADM_PAYABLE_LINE.VOUCHER_ID " & _
"WHERE (((SYSADM_PAYABLE.PAY_STATUS)<>'X') AND ((SYSADM_PAYABLE_LINE.WORKORDER_TYPE)='W') " & _
"AND ((SYSADM_PAYABLE_LINE.WORKORDER_BASE_ID)='" & strSpecNo & "') AND " & _
"((SYSADM_PAYABLE_LINE.WORKORDER_LOT_ID)='SPC' ));"
Set dbs = CurrentDb
Set varRecSet = dbs.OpenRecordset(strPayQuery)

The weir thing is, if I open the same form in Access 2010 without converting database to access 2010 format, its work fine. Also works fine in Access XP.
Any suggestions???
Thanks.

[pbaldy]

How is the recordset declared? You either need to disambiguate it or you have a reference problem.

[twm07073]

Thanks for the reply pbaldy.
Here is the code for declaring the recordset

Dim varRecSet As Recordset

FYI - if I don't convert the database to 2010 then its work fine. but after converting to 2010 it doesn't work.
Thanks again.

[pbaldy]

TryDim varRecSet As DAO.Recordset

[pbaldy]

I don't know why when I'm on an iPad it drops the carriage returns sometimes, but there should be one after "Try".

[twm07073]

That did work. Thanks so much.

[twm07073]

Why it work for some but not for all?
I also have couple other modules with OpenRecset and they work fine

[pbaldy]

When you don't disambiguate the declaration, Access doesn't know if you want a DAO or ADO recordset. It goes through the references in order, and my guess is the ADO reference was first, so it gave you the error. If the DAO reference had been first, it wouldn't have.