Using Recordset.State Property

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I use Recordset.State Property in module - OK. In form code I have an
error - object variable is not set. I Have declareed the recordset: Public Rs
As Recordset.
Please help: what is the right way to find in form code the state of
recordset: open or closed
 
I have set Recordset inside procedure of module as follows:
Set DfltRs = New ADODB.Recordset.
The DfltRs.State has value 0 or 1 when DfltRs Close or open only inside any
procedure of module .
Outside of module, in form code it gives an error when DfltRs closed. Why?
 
* Where did you declare the Recordset? In a Standard Module or a Class
Module?

* Did you make sure the Set is executed before you check the State?
 
I have executed Set statement before checking .State of Recordset. I have not
created class module for declare the recordset: thinking the Recordset is the
object by itself (i. e. by Microsoft) and if I delcare it as a Public one,
then it would be accessible across the Project. What is the problem? Thanks
for your involvment.
--
Ilya


Van T. Dinh said:
* Where did you declare the Recordset? In a Standard Module or a Class
Module?

* Did you make sure the Set is executed before you check the State?


--
HTH
Van T. Dinh
MVP (Access)
Click to expand...
 
So is it in a Standard Module?

Worked fine for me when I declared the rs as Public in a Standard Module and
checked the State in another Module (Form / Class Module).

Check the Module where you accessing the State. You might have another
DfltRs declared in this Module (Module-level scope) which will take
precedence over the Public Var. The same thing happens if you declare a
Procedure-level-scope variable of the same name.
 
Thanks for advice to check multiples. I do not have another declaration, but
I may have multiple execution of procedure where Recordset is Set and Open.
So, I may have several instances of recordset. How Microsoft distinguish them
or it keeps only one, closing previous one? Does multiple Set and Open have
anything to do with my problem? Thanks in Advance.
--
Ilya


Van T. Dinh said:
So is it in a Standard Module?

Worked fine for me when I declared the rs as Public in a Standard Module and
checked the State in another Module (Form / Class Module).

Check the Module where you accessing the State. You might have another
DfltRs declared in this Module (Module-level scope) which will take
precedence over the Public Var. The same thing happens if you declare a
Procedure-level-scope variable of the same name.
Click to expand...
 
I think opening one automatically close the previous one but I always close
a recordset explicitly before opening another one using the same name.
 
In this situation, you do not have multiple instances, there is only one
instance. If you Set and Open it in two different procedures, and then
attempt to access it in a third procedure, what you get will depend on which
of the two Set and Open procedures was called most recently.

If the recordset is being used for two or more distinct purposes, returning
two or more distinct subsets of data, it would probably be better to have a
separate recordset for each subset of data.

--
Brendan Reynolds (MVP)

Ilya said:
Thanks for advice to check multiples. I do not have another declaration,
but
I may have multiple execution of procedure where Recordset is Set and
Open.
So, I may have several instances of recordset. How Microsoft distinguish
them
or it keeps only one, closing previous one? Does multiple Set and Open
have
anything to do with my problem? Thanks in Advance.
Click to expand...
 
You still haven't said explicitly where your declaration is.
Is it in the module which belongs to a form? (Listed in the Database window
under Forms)
Or is it in a Standard module? (One listed under Modules)
Ilya said:
I have executed Set statement before checking .State of Recordset. I have not
created class module for declare the recordset: thinking the Recordset is the
object by itself (i. e. by Microsoft) and if I delcare it as a Public one,
then it would be accessible across the Project. What is the problem? Thanks
for your involvment.
Click to expand...
 
Here are my answers: Before my first post the Recordset was declared As
Public in standard module (not in form module) and Procedure in which
Recordset was Set and Open was in the same module. Then after some responces
I have moved procedure (only procedure, not declaration) into the form
module. So far it works.
Multiple procedure execution (same procedure, not another one) is not
intentional, will be avoided in future, but can not be 100% excluded.
What are the basic rules on:
1. How declare and Open ADO recordset to be accessible across the project
I 'v done it many times but some time struggled.
2. What I do not know at all: How to check if Recordset is Open or even not
Set without having Errors. Recordset.State is limited to only if it was Set,
is'nt it?
I'm looking for function similar to IsNull(Var),: IsSetAndOpen(RecordsetName).
 
Also check to see if you have an
End
statement.
(Not End function, End If etc, just End)

End, by itself, will clear all variables.

(david)
 
Back
Top