Access Forgets Global Variable!!

[Mike Turco]

This is a strange one.

I have an app that needs to know who is using the program but there's no
need for security. When the program opens a form comes up with a listbox.
The user double-clicks his name, the data is stuffed into a variable that is
dimmed in a module, and the user is "logged in".

From time to time, though, Access forgets the value! There is no other place
in the database where the variable is written. This is happening in 2002,
but I've seen this problem in 97 too.

The work-around is no problem. I just stuff the user name into a table when
the user logs in and get the data from there. But it just blows me away that
this variable comes up blank every once in a while.

Anybody else seen this? What could be the problem?

Thanks!

Mike

[Rick Brandt]

"Mike Turco" <mi*******@yaho o-no-spam-4-me.com> wrote in message
news:K10Zd.1088 42$bu.39614@fed 1read06...

This is a strange one.

I have an app that needs to know who is using the program but there's no need
for security. When the program opens a form comes up with a listbox. The user
double-clicks his name, the data is stuffed into a variable that is dimmed in
a module, and the user is "logged in".

From time to time, though, Access forgets the value! There is no other place
in the database where the variable is written. This is happening in 2002, but
I've seen this problem in 97 too.

The work-around is no problem. I just stuff the user name into a table when
the user logs in and get the data from there. But it just blows me away that
this variable comes up blank every once in a while.

Anybody else seen this? What could be the problem?

If you have an unhanded error in an MDB (not an MDE) then variables will often
lose their values.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Albert D. Kallal]

If your application (your code) encounters a error, then all global (and in
fact all) variables are re-set. You loose their values.

There is two solutions to this problem:

1) make sure you always have error handling code for each routine

2) Since I don't always follow #1, then distribute your application as a
mde, and un-handled errors will NOT reset your variables (both local, and
global).

So, while it is standard recommend and practice to distribute a mde so users
can't change, and mess with things, using a mde also have many other added
benefits in terms of reliability of your application.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl************* ****@msn.com
http://www.members.shaw.ca/AlbertKallal

[Mike Turco]

"Albert D. Kallal" <ka****@msn.com > wrote in message
news:B21Zd.6662 92$6l.393911@pd 7tw2no...

If your application (your code) encounters a error, then all global (and
in fact all) variables are re-set. You loose their values.

There is two solutions to this problem:

1) make sure you always have error handling code for each routine

Interesting! Is "on error resume next" at the top of the sub not good
enough? What about something like the following?

Sub Test()

On Error Goto Test_err

docmd.this
docmd.that
exit sub

Test_err:
select case err.number
case 1:
msgbox "Oops, you typed that 300 digit number wrong"
resume next
case 2:
resume next
case else
msgbox err.number & ": " & err.description
resume next
end select
2) Since I don't always follow #1, then distribute your application as a
mde, and un-handled errors will NOT reset your variables (both local, and
global).

This particular app is going to be distributed run-time anyway, so I might
as well distribute the mde. I'm still a little surprised, though. I didn't
know that encountering an error reset all your variables and its even more
of a surprise that I've only seen this issue crop up twice in the past ten
or so years.

Mike

[Bob Quintal]

"Mike Turco" <mi*******@yaho o-no-spam-4-me.com> wrote in
news:d83Zd.1093 35$bu.7432@fed1 read06:

"Albert D. Kallal" <ka****@msn.com > wrote in message
news:B21Zd.6662 92$6l.393911@pd 7tw2no...

If your application (your code) encounters a error, then all
global (and in fact all) variables are re-set. You loose
their values.

There is two solutions to this problem:

1) make sure you always have error handling code for each
routine

Interesting! Is "on error resume next" at the top of the sub
not good enough? What about something like the following?

Sub Test()

On Error Goto Test_err

docmd.this
docmd.that
exit sub

Test_err:
select case err.number
case 1:
msgbox "Oops, you typed that 300 digit number
wrong" resume next
case 2:
resume next
case else
msgbox err.number & ": " & err.description
resume next
end select

2) Since I don't always follow #1, then distribute your
application as a mde, and un-handled errors will NOT reset
your variables (both local, and global).

This particular app is going to be distributed run-time
anyway, so I might as well distribute the mde. I'm still a
little surprised, though. I didn't know that encountering an
error reset all your variables and its even more of a surprise
that I've only seen this issue crop up twice in the past ten
or so years.

Mike

There is another way of holding the user's ID. Keep the form
open, and refer to the form's control.You make the form invisible
instead of closing it. It's how I handle this situation.
--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

"Mike Turco" <mi*******@yaho o-no-spam-4-me.com> wrote in
news:K10Zd.1088 42$bu.39614@fed 1read06:

I have an app that needs to know who is using the program but
there's no need for security. When the program opens a form comes
up with a listbox. The user double-clicks his name, the data is
stuffed into a variable that is dimmed in a module, and the user
is "logged in".

From time to time, though, Access forgets the value! There is no
other place in the database where the variable is written. This is
happening in 2002, but I've seen this problem in 97 too.

Why not hide the form that the user double-clicks on and instead of
referring to the global variable, either refer to that form, or, if
you worry about performance, then use a function, something like
this (which is self-healing if the global variable is reset):

Public Function GetUser() As String
Static strUser As String

If Len(strUser) = 0 Then
strUser = Forms!dlgUserNa me!lstUsers
End If

GetUser = strUser
End If

You'll note that it will look it up from the hidden form only if the
Static variable gets reset. I prefer this method to using global
variables when the variable is set only once or can be retrieved
from some other source (such as a lookup or a Windows API call).

Of course, you'll want to handle the case where the dialog form gets
closed.

Another alternative if you're intending to store it in a table (I'd
never do that -- I might consider storing it as a custom property of
the MDB file, though) is to have this function do the table (or
customer property) lookup if the static variable has been reset.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Mike Turco]

"David W. Fenton" <dX********@bwa y.net.invalid>

Another alternative if you're intending to store it in a table (I'd
never do that -- I might consider storing it as a custom property of
the MDB file, though) is to have this function do the table (or
customer property) lookup if the static variable has been reset.

The variable does go into a table, anyway, because I want the database to
remember the variable between sessions. Rather than refer to the variable
directly I call a function. If the variable is empty then the function looks
up the value in the table. The variable only dumps out occasionally so its
not a performance issue.

[Steve Jorgensen]

On Sun, 13 Mar 2005 23:19:37 -0800, "Mike Turco"
<mi*******@yaho o-no-spam-4-me.com> wrote:

"David W. Fenton" <dX********@bwa y.net.invalid>

Another alternative if you're intending to store it in a table (I'd
never do that -- I might consider storing it as a custom property of
the MDB file, though) is to have this function do the table (or
customer property) lookup if the static variable has been reset.

The variable does go into a table, anyway, because I want the database to
remember the variable between sessions. Rather than refer to the variable
directly I call a function. If the variable is empty then the function looks
up the value in the table. The variable only dumps out occasionally so its
not a performance issue.

Well, you've stumbled across a very good answer for handling cached values in
VBA. Populate the variable unless it's already populated. That way, if you
ever reset the program, the program will recover transparently.

[Mike Turco]

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:i9******** *************** *********@4ax.c om...

On Sun, 13 Mar 2005 23:19:37 -0800, "Mike Turco"
<mi*******@yaho o-no-spam-4-me.com> wrote:

"David W. Fenton" <dX********@bwa y.net.invalid>

Another alternative if you're intending to store it in a table (I'd
never do that -- I might consider storing it as a custom property of
the MDB file, though) is to have this function do the table (or
customer property) lookup if the static variable has been reset.

The variable does go into a table, anyway, because I want the database to
remember the variable between sessions. Rather than refer to the variable
directly I call a function. If the variable is empty then the function
looks
up the value in the table. The variable only dumps out occasionally so its
not a performance issue.

Well, you've stumbled across a very good answer for handling cached values
in
VBA. Populate the variable unless it's already populated. That way, if
you
ever reset the program, the program will recover transparently.

That works well for me.

I was trying to create that mde, by the way. Turns out I can't do it because
I'm using A2002 and the file needs to stay in A2000 format. Since I'm
distributing the run-time for some of the computers, I hope there are no
other little surprises :-)

Mike