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 weird 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.

 

[PHV]

How is dimmed varRecSet ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[twm07073]

Thanks for your response,

Dim varRecSet As Recordset

after I changed that to
Dim varRecSet As DAO.Recordset
it worked (suggestion from other user)

Why it worked with some but not with all.
I have couple other modules which had OpenRecordset and they work fine.

Thanks again.

 

[PHV]

Why it worked with some but not with all
Depend on the order of the references.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886