Working code in 2003 does not run in 2007 (1 Viewer)

[MatMac]

Hi. Hope someone can advise. The code below is an example of a larger set of code blocks used to populate a complicated report. It used to work fine in Access 2003, but after upgrading to 2007 it no longer works – and I haven’t changed anything since it worked fine. I get a Run-time error 13 "Type mismatch" for the line as highlighted. Any help would be much appreciated. M.

Private Sub Report_Activate()

Dim db As Database
Dim rsQuery As QueryDef
Dim rsQuerySet As Recordset
Set db = CurrentDb()
Dim TextResults As String
Dim numResults As Integer
Dim GroupResults As String

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Project ID Number
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Read PublicInvolvementFormLead query and enter Research ID field into resID field
Set rsQuery = db.QueryDefs![PublicInvolvementFormLead]
rsQuery![Forms!Research!resID] = Forms![research]![resID]
rsQuery![Forms!ResearchPI!GrantApplicant] = [Forms]![ResearchPI]![GrantApplicant]
Set rsQuerySet = rsQuery.OpenRecordset()
If IsNull(rsQuerySet("resID")) Then
numResults = Null
Else
numResults = rsQuerySet("resID")
End If
resID = numResults

 

[RuralGuy]

Try disambiguating your Dim statements:
Dim db As DAO.Database
Dim rsQuerySet As DAO.Recordset

Have you made sure you have no bogus MISSING References?

 

[MatMac]

RG - many thanks for that - adding DAO as suggested has fixed the problem.

Just so I understand the fix, can you please explain why adding DAO has solved this - what disambiguating means in this context. It would also be interesting to learn why this was not required when I was using an earlier file format.

Much appreciate your help.

Regards - Mat

 

[namliam]

Just so I understand the fix, can you please explain why adding DAO has solved this - what disambiguating means in this context. It would also be interesting to learn why this was not required when I was using an earlier file format.

Google is your friend...
Basicaly there are two 'simular but different' methods, DAO and ADO

Both have simular objects like Database, QueryDef, Recordset if you have both referenced then Access takes the "first one" it finds in the list (going top down) which in your case is ADO
while
rsQuery.OpenRecordset()
Is DAO speak for opening a recordset, thus causing the problem.

Solution: DisAmbiguate, i.e. SPECIFY which database you mean, ADO.Database or DAO.Database. Disambiguating is a good thing ! Even if you remove the reference to the "wrong" version, which might be a solution as well. You will want to make sure you take the right version

 

[MatMac]

Thanks for the clarification Namliam, very helpful.

I am still wondering though why my ambiguous code worked fine under Access 2003.

M.

 

[namliam]

Possibly because you didnt have ADO referenced or because DAO was "referenced first"

 

[gemma-the-husky]

yes - if you open a code module and then do tools/references (not sure if this is the "route" in A2007) you will see a number of referenced external (DLL) libraries

ADO is ActiveX Data Objects

DAO is DAO object library

both libraries support recordsets, so if you don't disambiguate, the compiler picks whichever library is first named.

this is made partly more awkward in that MS changed their default from DAO to ADO and then back again - so you generally have to check to make sure - as life often isnt long enough to remember to disambiguate everything. (my philosophical view!)