Since you declared your variable inside the OpenDefaults procedure, it
ceases to exist as soon as the procedure exits. Its lifetime is just a few
microseconds in your example. The variable is also invisible to anything
else outside this procedure, i.e. its scope is just the procedure you
declared it in.
You can give it wider scope and a longer lifetime if you declare it in the
General Declarations section of your module (at the top, with the Option
statements.) If this is a standard module, its lifetime will last until you
close Access or reset the code. If it is in the module of a form, its
lifetime ceases when you close the form.
Doing that also changes the scope of the variable. In a standard module, it
will default to public scope, i.e. you can use it in any code in this
database. In a form's module, you can use the variable in any procedure in
that module.
Of course, that could have unintended consequences. If you use public
variables, you have to be really careful not to alter the value
inintentionally, and you don't know when you come to use it if some other
process may have altered it. It gets even more confusing if another
module/procedure uses the same name! VBA permits that, so which one is now
being accessed? Which value is being returned from the variable? And which
one is being changed? If you don't use Option Explicit, you now have a
*real* debugging nightmare on your hands!
At a practical level, code that relies on public variables is messy to
develop. You frequently reset during development, so they keep losing their
value, so you can't trust them to hold what you expect. At minimum, you
problaby need another public boolean variable just to hold the value of
whether they are initialized or not, and code to re-initialize them.
To answer your specific questions:
1. It is possible, but not advisable to use variables with a wide scope and
long lifetime.
2. Initializing a control on the form in Form_Open, and then reading the
value of the control when you need it would be faster than repeatedly
opening a recordset to get the same value over and over.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jim M" <ma*****@rci.rutgers.eduwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
>I rarely deal with recordsets directly with code, since I usually use
Access queries, so be patient with this question. I want to open a
recordset with various default variables used by my program. I tried:
Public Sub OpenDefaults()
Dim db As dao.Database
Dim globalRst As dao.Recordset
Set db = CurrentDb()
Set globalRst = db.OpenRecordset("tblDefaults")
End Sub
I am able to pull variable I need when I'm IN this module. e.g.
boolUseFormA = globalRst!bFrmA
Whoever, I am not able to get the variables after executing the module.
I thought the recordset stayed open until I did: 'Set globalRst =
nothing'. I wanted to open the recordset when I started up my
application, then put variables from it as needed.
My questions are:
1) Am I doing something wrong? Should I be able to pull data from the
recordset until I reset it to nothing?
2) If this is impossible, which would be best for performance:
a) Opening a form with the variables and pulling them from
the form, e.g.
boolUseFormA = Forms!globalForm!bFrmA
b) Running the above code with the additional line to pull
the data I need and also additional codes to unset the database and
recordset objects?
Thanks!
Jim M
