Annoying Problem (Needs a Simple Solution)

[thegentleman]

I'm trying to create a simple recordset function. Here is the code:

Function CreateCityString(GroupId As String)

Dim TheDB As Database
Dim RS As Recordset
Dim strSQL As String
Dim CityString As String
Dim City As String

Set TheDB = CurrentDb

strSQL = "SELECT Airport_Code FROM dbo_Airport_Gping WHERE Loc_Gp_Code = '" & GroupId & "';"
Set RS = TheDB.OpenRecordset(strSQL)
While Not RS.EOF
City = RS(Airport_Code)
CityString = CityString & ", " & City
RS.MoveNext
Wend

CreateCityString = CityString

End Function

Whenever I run this however I get the following error:

User-defined type not defined

And Access Higlights the following line:

Dim TheDB As Database

Please help me!!!

tg

 

[raymondo]

Do you have the DAO and/or ADO libraries checked? If using DAO then make sure the library is checked then try

Dim TheDB as DAO.Database

 

[DonQuichote]

If you're using Access 2000, do you have a reference to DAO?

 

[oharab]

I am assuming you are using Access 2k or above.
The problem is A2k uses ADO as its default engine as opposed to DAO used by A97.
Your example is using DAO objects.
What you need to do is add a reference to DAO3.6 in tools->references, then mark your DAO objects explicitly as DAO, so

Dim TheDB As DAO.Database
Dim RS As DAO.Recordset

that way Access knows which engine to use.

HTH

Ben
----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[mdillon]

Remove the declarations "Dim theDB as Database" and "Set TheDB = CurrentDb". "Set RS = TheDB.OpenRecordset(strSQL)" now becomes "Set RS = currentDB.OpenRecordset (strSQL)". This worked for me.

 

[thegentleman]

You are all a God-send! Thank you!

tg