Problem Declaring Recordset - "Variable Not Defined" message

[TryTryAgain]

Occasionly I try to open a recordset in a module, and I get a "variable not defined" message on the "dbopendynaset". My open statement is Set rs = CurrentDb.OpenRecordset ("tablename", dbOpenDynaset). Its as if Access doesn't recognize the statement. The table name is spelled correctly. I use the same statement that works in other modules, just change the table name. Does anyone know what causes this?

 

[petrosky]

Hi there,

I usually encounter this error when I have forgotten to declare the variable.

eg. Dim rs as recordset

HTH.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[VBslammer]

I wouldn't use the CurrentDb() function like that. The documentation explains that you should always declare a separate variable to hold the reference to CurrentDb() when used in a Set statement so you don't get the "Object invalid or no longer set" error.

Example:
[tt]
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Table1", dbOpenTable)
[/tt]

For the "Variable Not Defined" error - do you have "Option Explicit" turned on for this module but not in other modules?

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[TryTryAgain]

Yes Option Explicit is turned on, as it is in the other modules.

When I try to declare "Set db as DAO.Database", I get a "User Defined Type not defined".

When I turn off Option Explicit I get "Invalid Argument" on the "Set rs =....." statement.

When I leave Option Explicit turned on, I get the "variable not defined" error and the dbOpenDynaset is highlighted.

Here is my simple code and I'm getting "variable not defined".

Option Compare Database
Option Explicit

Public Sub DoMath()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Gas Control Employee Questionnaire", dbopendynaset)
MsgBox qa1
End Sub

 

[RoyVidar]

Ensure you have a reference to Microsoft DAO 3.# Object Library (in VBE - Tools | References), then also explicitly declare your DAO variables:

[tt]dim rs as dao.recordset
dim db as dao.database
dim qd as dao.querydef
...[/tt]

Roy-Vidar

 

[Tranman]

TryTry,
It sounds to me like you are running a newer version of Access, and you have references to both the DAO and ADO in your project. When you do that, you need to declare your recordset as a certain type:

Dim rs as DAO.Recordset
or
Dim rs as New ADO.Recordset

(the same with any other data access-type objects).

If you don't do that, and just say,
Dim rs as Recordset
then Access will sometimes create a DAO Recordset, and sometimes create an ADO Recordset (I have never figured out how it decides).

The rub is that if you have the wrong type of recordset, then some of the methods/properties that apply to one type are invalid for the other type.

Just for instance, "OpenRecordset" IS valid for DAO objects, but with ADO, you need to use the "Open" method to accomplish the same thing.

Hope that helps,
Tranman

 

[TryTryAgain]

Thanks - it was the reference - I was missing a couple of key ones!

 

[Lamprey79]

Personally, I like declaring my recordsets as Object. For some reason I get fewer headaches that way (I habitually use ADO, is DAO that much better?)