Declaring Variables at Module level and Procedure level

[Graeme]

I have a sub procedure that will only work when I declare two variables that
are used in the code. These are:

Dim db As Database
Dim rs As Recordset

I am confused because I didn't think I needed to declare these variables at
Procedure level because they are also defined at Module level.

Can anyone advise if my understanding is correct?

 

[Albert D. Kallal]

Well, the defines question here is what module level.

we have the standard code modules (that you see on the modules tab),and then
we have for each form a code module is also available.

So, if you declare as you have:

Dim db As Database
Dim rs As Recordset

The above two variables would be available to all code in THAT MODULE ONLY.
So, if it was a forms code module, then all code, events, and anything you
do in that form, those variables would be available to all code in that
forms module. However, other forms, and those other code modules would NOT
be able to see those variables. And, course keep in mind when the form
closes, then those values a re gone, and can't be used by any other
routines.

Now, if we moved the above two vars to a standard code module, then the two
vars would be available to ALL of the routines in THAT ONE code module.

If you wanted variables to be availing everywhere, then you declare them as
public

public db As Database
public rs As Recordset

If you move the above declares back to the forms module, and use the
"public" keyword, then actually all programs and all code CAN access those
variables, but you must tell the code what form the variables are in (if the
code is OUTSIDE of the forms module code).

eg:
inside our forms code module:

msgbox "last name is = " & rst!Lastname

code outside the forms code module:
msgbox "last name is = " & forms!frmCustomers.form.rst!LastName

and, of course if the form is closed..then you can't use those vars.

If you moved the two variables back to the standard code module (as public),
then you have true defined global variables.

eg:
inside our forms code module:

msgbox "last name is = " & rst!Lastname

code outside the forms code module:
msgbox "last name is = " & rst!LastName

So, if you define public in a standard code module, the vars can be used
anywhere, and anytime.

Do keep in mind that any un-handled code error will re-set both local, and
global var values. this is one good reason as to why you want to distribute
a mde to your end users (a un-handled error does NOT re-set the value of all
variables).

Note that if you declare the variables in a sub, or function, then when you
exit the sub, then the variables are gone. And, at a forms module, when you
exit the form..then those variables are gone. The ones in a module don't go
away. This common concept is referred to as "scope".

The most easy way to think of this is that each higher level you go, you are
throwing a card on top of a deck. And, as you exit out of routines and
modules...you pulling the cards back. this is important, since you can
actually declare the same variables a code module, forms modules, and then
each sub inside of the module. Each higher level you go takes precedence
over the previous.

 

[Stefan Hoffmann]

hi Graeme,

I have a sub procedure that will only work when I declare two variables that
are used in the code. These are:

Dim db As Database
Dim rs As Recordset

Use the FQN, e.g. DAO.Recordset or ADODB.Recordset.

Are you using Option Explicit?

I am confused because I didn't think I needed to declare these variables at
Procedure level because they are also defined at Module level.

This is normaly true, but without your code, who knows?



mfG
--> stefan <--

 

[Graeme]

Yes there is an option explicit declared at the start of the module.

It is a legacy thing and truth be told I'm not too sure as to its function.

 

[Albert D. Kallal]

Yes there is an option explicit declared at the start of the module.

It is a legacy thing and truth be told I'm not too sure as to its
function.

No! Option explicit is likely one of the MOST important settings you can
have in a database.

While in the code editor, go

tools->options->editor tab

There is a option called:

[x] Require Variable Declaration

the box by default is not check. this is the FIRST thing you do, just like
getting into a car and putting on the seat belt.

The checking of the above option does NOT do anything until you add/insert a
new module (or forms module). The checking of that option means ms-access
will place a option explicti in each of your code modules (but, it does NOT
touch/change existing ones).

That open means that you MUST declare all of you variables. If you don't use
the option, then you can just type in any old viable name, and it does not
have be declare. This means that if you miss-spell, or miss-type a variable
name..your code will STILL compile. The only way your find those errors is
when a customer calls you up with a expensive phone support call.

You ALWAYS want option explicit. it is your GREATEST friend in ms-access,
and checking the box means that ms-access will check ALL of your code and
make sure you not accident using a miss-typed, or miss spelled variable
name.

Critical to have this option set....

In the older access 97, it was set by default. In a2000, it was assumed that
new users will not developers or programmers, and thus the need to force
developers to declare variables by default was changed. For casual users,
they don't know much about this stuff, but that option explicit is very good
friend to have.

 

[Graeme]

Thanks Albert

It shall remain active!

Yes there is an option explicit declared at the start of the module.

It is a legacy thing and truth be told I'm not too sure as to its
function.

No! Option explicit is likely one of the MOST important settings you can
have in a database.

While in the code editor, go

tools->options->editor tab

There is a option called:

[x] Require Variable Declaration

the box by default is not check. this is the FIRST thing you do, just like
getting into a car and putting on the seat belt.

The checking of the above option does NOT do anything until you add/insert a
new module (or forms module). The checking of that option means ms-access
will place a option explicti in each of your code modules (but, it does NOT
touch/change existing ones).

That open means that you MUST declare all of you variables. If you don't use
the option, then you can just type in any old viable name, and it does not
have be declare. This means that if you miss-spell, or miss-type a variable
name..your code will STILL compile. The only way your find those errors is
when a customer calls you up with a expensive phone support call.

You ALWAYS want option explicit. it is your GREATEST friend in ms-access,
and checking the box means that ms-access will check ALL of your code and
make sure you not accident using a miss-typed, or miss spelled variable
name.

Critical to have this option set....

In the older access 97, it was set by default. In a2000, it was assumed that
new users will not developers or programmers, and thus the need to force
developers to declare variables by default was changed. For casual users,
they don't know much about this stuff, but that option explicit is very good
friend to have.