Global variable loses its value

Question

On this Access form I am working on I have a global variable that take its value from another form on its Form_Load event. For some reason unknown to me the variable "loses its value" (becomes = "") after some time elapses or some event occurs. I have not been able to notice anything in particular that triggers this behaviour. Are global variables reset after some time of "inactivity" on the form ?

Here is how I set the global variables I am talking about:

Private Sub Form_Load()       
    '...
    Set prev_form = Form_Identification.Form
    PasswordSybase = prev_form.Password.Value & vbNullString
    UserSybase = prev_form.UserID.Value & vbNullString
    '...
End Sub

[What is the lifetime of a global variable in excel vba ?](http://stackoverflow.com/questions/7041138/what-is-the-lifetime-of-a-global-variable-in-excel-vba) — Santosh, Jul 11 '13 at 19:40

score 9 · Answer 1 · answered Jul 12 '13 at 05:05

An alternate solution (Only 2007 and onwards) I've started using is TempVars instead of globals in the odd situation I "needed" something global. It's a collection and it persists for the duration of the application unless you explicitly release it. So in some cases I feel its more useful than globals and in some cases worse.

TempVars.Add myVarName, myVarValue ' To initialize
TempVars.Item(myVarName) = newVarValue ' To reference and assign a new value
TempVars.Remove(myVarName) ' To release

Quick search should show you more lot references, but I've included link to a basic one

http://blogs.office.com/b/microsoft-access/archive/2010/09/27/power-tip-maximize-the-user-of-tempvars-in-access-2007-and-2010.aspx

I upvoted but cannot accept since I have to use Access 2000 (sorry I did not specify this earlier). Great answer for 2007+ though. I will definitely keep a mental note of this. — ApplePie, Jul 12 '13 at 14:57
Old Post but still useful, TempVars also persist after Application.Quit (all those unload events :D) but before complete closure which Globals do not. — L Riley, Dec 21 '17 at 12:44

score 6 · Answer 2 · answered May 18 '16 at 17:26

6

I do hope that visitors see this post, as it provides an important additional piece.

Even if you declare a variable globally, it appears that - in the event that you set that variable's value in a form module - that value is lost when you UNLOAD the form.

The solution (in my case) was as simple as replacing:

Unload Me

...with...

Me.Hide

The variables (and objects) that I set in that code module then retained their values through the entire lifetime of the application instance.

answered May 18 '16 at 17:26

John Joseph

766
1
6
15

1

Thanks for this - I'd been Closing my main form manually during testing (something regular users can't do) and was wondering why my global variables kept unsetting. Will be more careful with this going forward! – Steve Taylor Aug 23 '17 at 11:04

score 2 · Answer 3 · answered Dec 23 '14 at 04:06

This may help: https://accessexperts.com/blog/2011/01/12/multi-session-global-variables/ Juan Soto explains how to use a local table to keep variables and how to call them when needed. It may serve your purpose in 2000 since TempVars isn't an option. You could always delete the variables "on close" of the database so that UID and PWD aren't kept.

score 1 · Answer 4 · answered Jul 13 '13 at 09:04

You can create a "fake" global variable by

creating a form (e.g. named frmGlobal)
make sure the form is always open but hidden
create a TextBox for each global variable you want (e.g. tVar1)
in your code, reference as e.g. Form_frmGlobal.tVar1

The disadvantage is that an unbound text box may not give you a specific data type you want

The two ways around that are

in your code, explicitly convert the textbox to the data type when referencing the global variable e.g Clng(Form_frmGlobal.tVar1)
another option is create a one-row table and bind your textboxes on your hidden form to the table, so your data types are enforced

A bonus of this method is you can use for persistent storage between sessions Caveat: make sure this table is local to a single user only, in the front end database file (don't want to put it in the back end database because of multi-users over-writing each other). This assumes you are using front end + back end separated databases, with distribution of front end to each user's workstation.

Johnny Bones · Answer 5 · 2013-07-11T19:50:41.017

0

I see nothing in that statement that tells me it's a global variable. You set global variables above ALL Subs/Functions and below an Options Compare statement in a module, by stating:

PUBLIC X as string

Any other variable is only good until the sub or function has completed.

Also, Global variables MUST be declared on a PROPER MODULE. You can't declare them on a form's module.

edited Jul 11 '13 at 19:50

answered Jul 11 '13 at 19:40

Johnny Bones

8,271
6
39
99

That "Global" keyword does not seem to exist. I put it at the top and declared it like such : `Dim PasswordSybase as String` – ApplePie Jul 11 '13 at 19:44
Try PUBLIC PasswordSybase as String. Leave out the Dim – Johnny Bones Jul 11 '13 at 19:47
`Global` is a legacy from earlier versions of VB and is superseded by `Public`. It still accepts it though (capitalizing the G if you type it in lower-case) but if you click into this word and press F1 it takes you to the Help for the `Public Statement`. – Andy G Jul 11 '13 at 19:59

Global variable loses its value

5 Answers5

Linked