A97 to A02

[JKB]

I spent months and months and books and books and hours and hours learning A97 and VBA until I got to the point that I could write an application that was usable by my staff. Works pretty well, though I’m sure it's not perfect. Now I’ve got Win XP Pro, Access 02. Have converted my A97 database and can open it and use it in many areas. However, I use a lot of “research forms” to obtain the data I need. What follows is a portion of code I have used. In A02, when I get to the point to querying for the requested data, I get “User-defined type not defined" error. I have tried adding DAO 2.5/3 and DAO 3.51 and DAO 3.6 references but they return a “Type Mismatch” error. Must I relearn an entirely new VBA code or is there something simple that I am overlooking that I can change easily? I just don’t have the time to relearn an entirely new programming language? Thanks.


Dim MyDB As Database
Dim MyQDef As QueryDef
Dim MyDyna As Recordset
Set MyDB = CurrentDb()
Set MyQDef = MyDB.QueryDefs("QDoor&FramePriceLookUp")

'set the value of the parameter
MyQDef![Forms!FrmDoor&FramePriceResearch!txtSubstrate#] = Forms![FrmDoor&FramePriceResearch]![txtSubstrate#]
MyQDef![Forms!FrmDoor&FramePriceResearch!txtDescription#] = Forms![FrmDoor&FramePriceResearch]![txtDescription#]

'Create the recordset
Set MyDyna = MyQDef.OpenRecordset()
If MyDyna.RecordCount = 0 Then
MsgBox "Revise Your Selections!", vbCritical, "No Matching Records Were Found"
Else
MyDyna.MoveLast

'open the form to view the results

stDocName = "FrmDoor&FramePriceResultsBrief"
DoCmd.OpenForm stDocName, , , stLinkCriteria

MyDyna.Close
MyQDef.Close

 

[Tim K.]

If you don't remove the ADO from the References, you'll get the error. There are 2 ways to correct this.

First move the DAO Ref above the ADO in the References.

or explicitly define the dim like this.

Dim MyDB As DAO.Database
Dim MyQDef As DAO.QueryDef
Dim MyDyna As DAO.Recordset
...