Global variables lost when opening a VBA-generated form.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve Sullivan - 19 Apr 2007 17:36 GMT

I lose all of the contents of global variables (variables declared as Public
in code modules) when the VBA-generated form I have created opens.

I needed a form in my Access application that was populated with labels and
checkboxes based on the contents of columns from two separate tables.
Because these contents can change, I could not create the form using the form
designer or wizard; instead, I wrote the VBA code to create the form and
populate it with the desired controls. The form also contains the VBA code
to run global procedures on certain events. The problem is that when the
form is activated, all global variables are "empty". Even when I close the
form, the global variables have no value assigned.

In expermentation, I found that I could generate the form and open it up
and, just before releasing control to the form, list out the contents of
global variables. However, as soon as the form-creating procedure ends
(releasing control to the form itself), none of the global variables are
valid anymore.

Is anyone familiar with this problem?

Signature

Steve Sullivan

May the road rise up to meet you. May the wind always be at your back. May
the sun shine warm upon your face … and … may God hold you in the palm of His
hand.

Reply to this Message

Marshall Barton - 19 Apr 2007 20:47 GMT

>I lose all of the contents of global variables (variables declared as Public
>in code modules) when the VBA-generated form I have created opens.
[quoted text clipped - 13 lines]
>(releasing control to the form itself), none of the global variables are
>valid anymore.

Making a major design change such as creating/modifying a
form often resets the project. The obvious thing to do is
stop doing design time operations in a running application,
especially when you consider all of the potentially
catestrophic things that might go wrong.

Instead of that you can precreate the form wilt a bunch of
invisible controls. Then you can use a form event (Load?)
to determine which controls to display. The code in the
form is actually a little simpler than the code that is
causing your current problem.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Steve Sullivan - 20 Apr 2007 16:40 GMT

> >I lose all of the contents of global variables (variables declared as Public
> >in code modules) when the VBA-generated form I have created opens.
[quoted text clipped - 25 lines]
> form is actually a little simpler than the code that is
> causing your current problem.

Thank you for the idea. I experimented and created a basically blank form
with OK and Cancel buttons that execute code stored with the form. Then, I
used VBA to open the form in design view and create the checkbox controls
which do not have any code. Then it switches to normal view, sets the value
of these controls, and releases control to the user. This did not create any
problem with the global variables that my application already had defined.

I can design my form using either method, but I already have all of the code
that creates and uses the controls. I would have to rewrite to use
pre-existing, prenamed, hidden controls. Will there be a definite advantage
to the latter method?

Again, thanks for the excellent idea.
Steve Sullivan

Reply to this Message

Marshall Barton - 20 Apr 2007 19:44 GMT

>> >I lose all of the contents of global variables (variables declared as Public
>> >in code modules) when the VBA-generated form I have created opens.
[quoted text clipped - 37 lines]
>pre-existing, prenamed, hidden controls. Will there be a definite advantage
>to the latter method?

There are a bunch of advantages to using precreated controls
instead of going into design view. Performing heavy duty
design activities in a running application is a really BAD
PRACTICE! It greatly increases the chances of corrupting
the front end mdb (which might wreak all kinds of havoc in a
running app), causes big time bloat (forcing periododic
front end compact operations), will fail when you reach the
form's lifetime limit on the number of controls and just
plain fails in an mde. The Create... methods are only there
for you to create **design time** single use wizard like
procedures.

The rewrite you need to do is pretty simple. If you name
your controls with a constant prefix and a sequential
numeric suffix (e.g. chk1, chk2, ...), then replace the
CreateControl line with something like

    intCtlNum = intCtlNum +1
    strCtlName = "chk" & intCtlNum
    Me(strCtlName).Top = yy
    Me(strCtlName).Left = xx

The rest of your code will be pretty much the same as it is
now.

If you were also creating attached labels, you can get rid
of that code because they would be precreated along with the
parent controls. Hint: You don't even need to know the
name of an attached label:
    Me(strCtlName).Controls(0).Caption = "some text"

Signature

Marsh
MVP [MS Access]

Reply to this Message

Albert D. Kallal - 21 Apr 2007 00:24 GMT

> I can design my form using either method, but I already have all of the
> code
> that creates and uses the controls. I would have to rewrite to use
> pre-existing, prenamed, hidden controls. Will there be a definite
> advantage
> to the latter method?

The amount of bloat, instiabliry, and problems you encouent will make your
apcpaiton compilet useless. You cannot even being to codiner having runtime
self modifying code in your apcatons.

> Again, thanks for the excellent idea.
> Steve Sullivan

As an another suggestion, you can change the data source of any text box,
check box or control on a form.

And, the form can also have its data source changed at runtime...

so, you can have a check box, and *choose* what field it will be bound to in
code:

check1.ContorlSource = "HasBirthDay"

The above code will bind the control on he form to the underlying data field
called HasBIrthDay.

the above type code is not altering the design of the form, but only the
properties of the existing control on the form (and, you can safely do
this).

So, you can set the data source of the form, and also what fields the
existing controls on the form are bound to.

However, trying to create new controls. or add code that needs to be
compiled at runtime is simply not workable at all..

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message