Public vs. private variables

[Brian]

I have a Boolean variable called "CompleteFlag" that I use privately in each
form's module. It's name is probably self-explanatory: I just set it to False
in BeforeUpdate (and other events), then run through a procedure that checks
the form for completeness and sets the value to True. This prevents updates
to incomplete records while not exposing the system messages to the user.

Since I use the same variable name in each module but it is not actually
shared among modules, I have debated making it a public variable just so I
don't have to declare it in each mod's declarations but can instead declare
it once globally.

Good idea / bad idea?

 

[Albert D. Kallal]

Good idea / bad idea?

It likely a bad idea. The reasons are:


* if you have to copy the form to another application, you have to hunt down
the spot were you declared the variable.
or remember to go and declare the variable.

* if you have more then one form open, then they will trip over each other
as both forms will be trying to set the global variable. These types of bugs
are VERY hard to determine.

* If you modify some code, you want the changes to be LOCAL to your current
code. Anytime you use global, you risk damaging other code, and that makes
your code less reliable (it means you can't freely modify code without
worrying that other parts use the variable also).

* ms-access allows multiple instances of the SAME form to be opened. And
this double applies to sub-forms...again, more then one instance of the form
could be used as a sub-form.

I could likely type on for several more pages. The goal in software is make
stand alone bits of code that you FREELY modify without worrying that some
other routine, other code, or other forms uses that same variable. In 1 year
from now..it going to be hard to remember that other forms, or other code
might also be using that global var.

As really great general development tip, you want to make efforts to avoid
using global vars when not needed.....

 

[David W. Fenton]

As really great general development tip, you want to make efforts
to avoid using global vars when not needed.....

The general principle I use is:

If it needs to be modified after app startup, it shouldn't be in a
global variable.

That allows you to use global variables for things that are
initialized from a persistent data source at application startup,
and it allows you to use constants.

But anything else should not be a global variable.

And you should especially avoid using them for communicating between
forms/reports.

 

[Brian]

Thanks, David. I have just a followup question about global variables in a
specific type of usage.

There are many cases where want to do this:

1. Create header-type records on a continuous form frmHeader (because, for
example, there are many records but few fields, so single view is not
justified).
2. Create related detail information on frmDetail accessed via drilldown
from frmHeader.

In the past, I have done two things on frmDetail:
1. Filtered the Record Source on FieldX to =[frmHeader]![ControlX] where
ControlX is the control on frmHeader bound to its PK, which is the FK
relating tblHeader to tblDetail.
2. Set the default value of hidden ControlX on frmDetail to
[frmHeader]![ControlX] which is bound to the relating key.

This works well, except that I now need to open frmDetail from frmOther.

So...I set up a public variable to pass the value to the RecordSource of
frmDetail and a procedure to get that value (so I can use it in
frmDetail.ControlX.DefaultValue). This allows me to control both the filter &
default value without a direct reference to frmHeader or frmOther.

Would it be preferable to just open a hidden form when the app opens and
have controls on that form that I populate as needed to carry the values
between forms?

Or is there some better method overall?

 

[John Spencer]

If you are opening frmDetail through code, you can pass the value using
the OpenArgs argument of DoCmd.OpenForm.

Then you can use the OpenArgs property of the form to retrieve the value.

OR

You can use the whereCondition argument of DoCmd.OpenForm to limit
(specify) which record(s) to display when the form is opened.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks, David. I have just a followup question about global variables in a
specific type of usage.

There are many cases where want to do this:

1. Create header-type records on a continuous form frmHeader (because, for
example, there are many records but few fields, so single view is not
justified).
2. Create related detail information on frmDetail accessed via drilldown
from frmHeader.

In the past, I have done two things on frmDetail:
1. Filtered the Record Source on FieldX to =[frmHeader]![ControlX] where
ControlX is the control on frmHeader bound to its PK, which is the FK
relating tblHeader to tblDetail.
2. Set the default value of hidden ControlX on frmDetail to
[frmHeader]![ControlX] which is bound to the relating key.

This works well, except that I now need to open frmDetail from frmOther.

So...I set up a public variable to pass the value to the RecordSource of
frmDetail and a procedure to get that value (so I can use it in
frmDetail.ControlX.DefaultValue). This allows me to control both the filter &
default value without a direct reference to frmHeader or frmOther.

Would it be preferable to just open a hidden form when the app opens and
have controls on that form that I populate as needed to carry the values
between forms?

Or is there some better method overall?

The general principle I use is:

If it needs to be modified after app startup, it shouldn't be in a
global variable.

That allows you to use global variables for things that are
initialized from a persistent data source at application startup,
and it allows you to use constants.

But anything else should not be a global variable.

And you should especially avoid using them for communicating between
forms/reports.

 

[Brian]

Thanks, John. I had never used OpenArgs before. I does what I need in a
form-to-form situation.

I also use a couple of public variables combined with public functions that
get the values to carry values from forms to action queries. Is there another
way to do this? I know I can just do RunSQL, but there are times when the
graphical query builder just saves time.

If you are opening frmDetail through code, you can pass the value using
the OpenArgs argument of DoCmd.OpenForm.

Then you can use the OpenArgs property of the form to retrieve the value.

OR

You can use the whereCondition argument of DoCmd.OpenForm to limit
(specify) which record(s) to display when the form is opened.



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks, David. I have just a followup question about global variables in a
specific type of usage.

There are many cases where want to do this:

1. Create header-type records on a continuous form frmHeader (because, for
example, there are many records but few fields, so single view is not
justified).
2. Create related detail information on frmDetail accessed via drilldown
from frmHeader.

In the past, I have done two things on frmDetail:
1. Filtered the Record Source on FieldX to =[frmHeader]![ControlX] where
ControlX is the control on frmHeader bound to its PK, which is the FK
relating tblHeader to tblDetail.
2. Set the default value of hidden ControlX on frmDetail to
[frmHeader]![ControlX] which is bound to the relating key.

This works well, except that I now need to open frmDetail from frmOther.

So...I set up a public variable to pass the value to the RecordSource of
frmDetail and a procedure to get that value (so I can use it in
frmDetail.ControlX.DefaultValue). This allows me to control both the filter &
default value without a direct reference to frmHeader or frmOther.

Would it be preferable to just open a hidden form when the app opens and
have controls on that form that I populate as needed to carry the values
between forms?

Or is there some better method overall?

As really great general development tip, you want to make efforts
to avoid using global vars when not needed.....
The general principle I use is:

If it needs to be modified after app startup, it shouldn't be in a
global variable.

That allows you to use global variables for things that are
initialized from a persistent data source at application startup,
and it allows you to use constants.

But anything else should not be a global variable.

And you should especially avoid using them for communicating between
forms/reports.

 

[Albert D. Kallal]

I also use a couple of public variables combined with public functions

that
get the values to carry values from forms to action queries. Is there
another
way to do this? I know I can just do RunSQL, but there are times when the
graphical query builder just saves time.

I often go:

dim strSql as string
dim strWhere as string
strSql = CurrentDb.QueryDefs(strSearchQuery).SQL
' get rid of the ";"

strSql = Left(MySql, InStr(MySql, ";") - 1)
strWhere = "Where city = 'Edmonton'"

currentdb.Execute strSql & strWhere

That way, you can still use the query builder, but add parameters via code.

Another way is to put actual parameters in he sql, and then set them as:

Dim qryWithFriends As QueryDef

Set qryWithFriends = currentdb.QueryDefs("qryWithFriends")
qryWithFriends.Parameters(0) = "Edmonton"
qryWithFriends.Execute


So, in most cases again you don't want to put "global" functions in the sql,
and also have to declare global functions...

 

[David W. Fenton]

Thanks, David. I have just a followup question about global
variables in a specific type of usage.

There are many cases where want to do this:

1. Create header-type records on a continuous form frmHeader
(because, for example, there are many records but few fields, so
single view is not justified).
2. Create related detail information on frmDetail accessed via
drilldown from frmHeader.

In the past, I have done two things on frmDetail:
1. Filtered the Record Source on FieldX to =[frmHeader]![ControlX]
where ControlX is the control on frmHeader bound to its PK, which
is the FK relating tblHeader to tblDetail.
2. Set the default value of hidden ControlX on frmDetail to
[frmHeader]![ControlX] which is bound to the relating key.

This works well, except that I now need to open frmDetail from
frmOther.

So...I set up a public variable to pass the value to the
RecordSource of frmDetail and a procedure to get that value (so I
can use it in frmDetail.ControlX.DefaultValue). This allows me to
control both the filter & default value without a direct reference
to frmHeader or frmOther.

I never bind my reports to a particular open form. I would pass the
criteria to the form in its Open command in most cases, but
sometimes I will open a filter form as a dialog in the report's
OnOpen event and write the report's recordsource on-the-fly. In
other cases, I'll use a class module for storing filtering criteria.
The advantage of a class module is that it can be instantiated
multiple times, with each instance having its own unique values.

In a certain sense, a class module is a very complicated global
variable. But it's the differences between the class module and the
global variable that make the former OK and the latter inadvisable.

Would it be preferable to just open a hidden form when the app
opens and have controls on that form that I populate as needed to
carry the values between forms?

That would just put you in the same boat as a global variable.

Or is there some better method overall?

See above for the various methods I use.

 

[Brian]

Thank you again, Albert. Sound advice, as usual.

Since I absolutely abhor putting complex queries in VBA (not because it's a
bad idea - just because I'm lazy & like to use the query builder so I can
spend more time on other things), the .Parameters options is a perfect fit.

 

[Brian]

Thanks, David.

The reason this whole issue arose is that I'm migrating away from references
to specific forms. My baby step (which turned out to be a false step) ws the
global variables. I don't think I'm quite up to class modules, but I have
begun passing these in via the Where clause and now OpenArgs.

Thanks, David. I have just a followup question about global
variables in a specific type of usage.

There are many cases where want to do this:

1. Create header-type records on a continuous form frmHeader
(because, for example, there are many records but few fields, so
single view is not justified).
2. Create related detail information on frmDetail accessed via
drilldown from frmHeader.

In the past, I have done two things on frmDetail:
1. Filtered the Record Source on FieldX to =[frmHeader]![ControlX]
where ControlX is the control on frmHeader bound to its PK, which
is the FK relating tblHeader to tblDetail.
2. Set the default value of hidden ControlX on frmDetail to
[frmHeader]![ControlX] which is bound to the relating key.

This works well, except that I now need to open frmDetail from
frmOther.

So...I set up a public variable to pass the value to the
RecordSource of frmDetail and a procedure to get that value (so I
can use it in frmDetail.ControlX.DefaultValue). This allows me to
control both the filter & default value without a direct reference
to frmHeader or frmOther.

I never bind my reports to a particular open form. I would pass the
criteria to the form in its Open command in most cases, but
sometimes I will open a filter form as a dialog in the report's
OnOpen event and write the report's recordsource on-the-fly. In
other cases, I'll use a class module for storing filtering criteria.
The advantage of a class module is that it can be instantiated
multiple times, with each instance having its own unique values.

In a certain sense, a class module is a very complicated global
variable. But it's the differences between the class module and the
global variable that make the former OK and the latter inadvisable.

Would it be preferable to just open a hidden form when the app
opens and have controls on that form that I populate as needed to
carry the values between forms?

That would just put you in the same boat as a global variable.

Or is there some better method overall?

See above for the various methods I use.