Error Type Mismatch : Runtime error 13

[rburna]

ok this makes no sence at all, I have the following Sub Routine running
and for some reason on the 'Set rst' line I get an error telling me I
have a type mismatch, but if I run the query in Access Query Window it
runs as expected.

Private Sub cboTypeService_Change()
Dim rst As Recordset
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT BDR.[OCPJAN],
BDR.[OCPFEB], BDR.[OCPMAR], BDR.[OCPAPR], BDR.[OCPMAY], BDR.[OCPJUN],
BDR.[OCPJUL], BDR.[OCPAUG], BDR.[OCPSEP], BDR.[OCPOCT], BDR.[OCPNOV],
BDR.[OCPDEC], BDR.[OBUJAN], BDR.[OBUFEB], BDR.[OBUMAR], BDR.[OBUAPR],
BDR.[OBUMAY], BDR.[OBUJUN], BDR.[OBUJUL], BDR.[OBUAUG], BDR.[OBUSEP],
BDR.[OBUOCT], BDR.[OBUNOV], BDR.[OBUDEC], BDR.[PBUOCT], BDR.[PBUNOV],
BDR.[PBUDEC], BDR.[PBUJAN], BDR.[PBUFEB], BDR.[PBUMAR], BDR.[PBUAPR],
BDR.[PBUMAY], BDR.[PBUJUN], BDR.[PBUJUL], BDR.[PBUAUG], BDR.[PBUSEP]
FROM BUDGET_DIRECTCOSTS_R AS BDR")

Do Until rst.EOF
Me.txtPBU_OCT = rst![PBUOCT]
Me.txtPBU_NOV = rst![PBUNOV]
Me.txtPBU_DEC = rst![PBUDEC]
Me.txtPBU_JAN = rst![PBUJAN]
Me.txtPBU_FEB = rst![PBUFEB]
Me.txtPBU_MAR = rst![PBUMAR]
Me.txtPBU_APR = rst![PBUAPR]
Me.txtPBU_MAY = rst![PBUMAY]
Me.txtPBU_JUN = rst![PBUJUN]
Me.txtPBU_JUL = rst![PBUJUL]
Me.txtPBU_AUG = rst![PBUAUG]
Me.txtPBU_SEP = rst![PBUSEP]
Loop
End Sub

What am I doing wrong???

Thanks in advance,
Richard

 

[Douglas J Steele]

Short answer:

Dim rst As DAO.Recordset

Long answer:

I assume you're using Access 2000 or newer.

Both the DAO and ADO models have Recordset objects in them.

You're trying to use DAO (your use of CurrentDb.OpenRecordset proves this).
Since you're not getting an error referring to CurrentDb, you must have a
reference set to DAO. By default, Access 2000 and 2002 only have a reference
set to ADO, so when you add a reference to DAO, it's lower in precedence
that the reference to ADO. Access 2003 has a reference set to both, but the
DAO reference is lower in precendence than the reference to ADO.

Whenever there's a reference to an object in an external reference, VBA
stops searching for the object after finding the first occurrence. Since the
ADO reference has higher precedence, simply referring to Recordset, and not
qualifying which reference to look, means that you'll get an ADO recordset.
(To be guaranteed you're getting an ADO recordset, you'd want to use Dim rst
As ADODB.Recordset)

 

[rburna]

I figured it out....


Dim rst As DAO.Recordset

Need to specify it as a DAO recordset