Persistent recordset error 91

[Bill R via AccessMonster.com]

I get the error msg "Object variable or With block variable not set" when
closing the "persistent " recordset I opened when the application opened.

I have the rs variable declared in the declarations section of a module:

Public rsAlwaysOpen As DAO.Recordset
Then, when the switchboard form loads I set the variable:

Dim dbs As DAO.Database

Set dbs = CurrentDb
Set rsAlwaysOpen = dbs.OpenRecords et("tblGlobals" )

Presumably, the rs will now stay open until I close it in the Unload or Close
event of the switchboard form, but, instead, I get the above error and the rs
appears to be closed (I can't get any values in the Immediate window).

It's only a problem if I open the db with the shift key. Users don't get any
such msgs.

Bill
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Wayne Morgan]

There is a KB article on this. If the dbs goes out of scope, then you may
also lose the subsequent object variables as well. The example you give
isn't exactly the same as the article, but I suspect the problem is the same
one.

http://support.microsoft.com/default...b;en-us;200592

--
Wayne Morgan
MS Access MVP
"Bill R via AccessMonster.c om" <fo***@AccessMo nster.com> wrote in message
news:51******** ***@AccessMonst er.com...

I get the error msg "Object variable or With block variable not set" when
closing the "persistent " recordset I opened when the application opened.

I have the rs variable declared in the declarations section of a module:

Public rsAlwaysOpen As DAO.Recordset
Then, when the switchboard form loads I set the variable:

Dim dbs As DAO.Database

Set dbs = CurrentDb
Set rsAlwaysOpen = dbs.OpenRecords et("tblGlobals" )

Presumably, the rs will now stay open until I close it in the Unload or
Close
event of the switchboard form, but, instead, I get the above error and the
rs
appears to be closed (I can't get any values in the Immediate window).

It's only a problem if I open the db with the shift key. Users don't get
any
such msgs.

Bill
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Bill R via AccessMonster.com]

Wayne,

Thanks for your suggestion, but if I understand the article correctly, it
would appear that my code already implements their suggested solution.
I set currentdb to a variable, dbs, and then assign my rs to a dbs.
openrecordset table.

Bill

Wayne Morgan wrote:

There is a KB article on this. If the dbs goes out of scope, then you may
also lose the subsequent object variables as well. The example you give
isn't exactly the same as the article, but I suspect the problem is the same
one.

http://support.microsoft.com/default...b;en-us;200592

I get the error msg "Object variable or With block variable not set" when
closing the "persistent " recordset I opened when the application opened.

[quoted text clipped - 21 lines]

Bill

--
Message posted via http://www.accessmonster.com

[bruce]

I suspect that Wayne is correct in that the problem is that the
database variable you're using (dbs) goes out of scope but I don't
think CurrentDB has anything to do with it. See VBA help on the
Recordset object:

"Note If you use variables to represent a Recordset object and the
Database object that contains the Recordset, make sure the variables
have the same scope, or lifetime. For example, if you declare a public
variable that represents a Recordset object, make sure the variable
that represents the Database containing the Recordset is also public,
or is declared in a Sub or Function procedure using the Static
keyword."

If I were you I'd declare a public database variable and keep my
recordsets private or static.

HTH,
Bruce

[Wayne Morgan]

I suspect the problem is that your "rst" variable is global but your "dbs"
variable is only valid in the procedure where it runs. When that procedure
is done, "dbs" goes out of scope and you lose the value of "rst" at the same
time.

--
Wayne Morgan
MS Access MVP
"Bill R via AccessMonster.c om" <fo***@AccessMo nster.com> wrote in message
news:51******** ***@AccessMonst er.com...

Wayne,

Thanks for your suggestion, but if I understand the article correctly, it
would appear that my code already implements their suggested solution.
I set currentdb to a variable, dbs, and then assign my rs to a dbs.
openrecordset table.

[Bill R via AccessMonster.com]

AHA! Comes the dawn..

Well. I tried makeing both the rs and dbs public, but I'm still getting the
same msg. But, I'm sure I'm on the right track now. I'll let you know how I
make out.

Bill

Bill R wrote:

Wayne,

Thanks for your suggestion, but if I understand the article correctly, it
would appear that my code already implements their suggested solution.
I set currentdb to a variable, dbs, and then assign my rs to a dbs.
openrecordse t table.

Bill

There is a KB article on this. If the dbs goes out of scope, then you may
also lose the subsequent object variables as well. The example you give

[quoted text clipped - 8 lines]

Bill

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Bill R via AccessMonster.com]

Got it!

I had my code in the unload or close event of the form. Unfortunately, those
events take place after a docmd.quit command in a cmd button on the same form.
So by the time my code ran, the db was already closed, although intuition
would argue against that, given that the event code is still running in the
db that is closed.

Thanks for pointing me in the right direction.

Bill
--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[bruce]

Actually, DoCmd.Quit shuts down Access immediately. Once this command
has executed, no other lines in the event procedure will be processed,
nor will control be passed back to any calling routine. Everything
simply stops, right then and there.

Bruce