By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,186 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Persistent recordset error 91

P: n/a

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.OpenRecordset("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.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.com" <fo***@AccessMonster.com> wrote in message
news:51***********@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.OpenRecordset("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.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

Nov 13 '05 #2

P: n/a

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
Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a
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.com" <fo***@AccessMonster.com> wrote in message
news:51***********@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.

Nov 13 '05 #5

P: n/a

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.
openrecordset 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.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1
Nov 13 '05 #6

P: n/a

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.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1
Nov 13 '05 #7

P: n/a
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

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.