Global variable losing value for no apparent reason

[teddysnips]

Weird.

I have taken over responsibility for a legacy application, Access 2k3,
split FE/BE.

The client has reported a problem and I'm investigating. I didn't
write the application.

The AutoExec macro calls a function "InitApplication" which, in turn,
calls a function to set the value of a global string variable
"MyStrVar" (yes, I know, globals....)

The main form has a "Quit" button, which examines the value of this
global variable and performs an operation depending on the value.
However, a breakpoint at this point in the code shows that the
variable is empty (hence the problem).

After a good deal of trial and error, I have found out what is
happening, but not why! The following step-by-step guide shows what
I've found (I've added either MsgBox or breakpoints to allow me to
figure out what's going on).

1. Autoexec for the first time - "MyStrVar" is emptly
2. Call function to load variable - "MyStrVar" correctly loaded
3. Call function to quit application - "MyStrVar" empty - PROBLEM
4. Application quits.

Repeat the above but with slight variation

1. Autoexec for the first time - "MyStrVar" is emptly
2. Call function to load variable - "MyStrVar" correctly loaded
3. Call function to quit application - "MyStrVar" empty - PROBLEM
4. Press RESET button in code window and close form by flipping to
design mode - "MyStrVar" still empty
5. Run Autoexec for the second time - "MyStrVar" is empty
6. Call function to load variable - "MyStrVar" correctly loaded
7. Call function to quit application - "MyStrVar" now correctly
loaded - WHY?

At no point in the program is there an assignment to "MyStrVar" except
in the function to load the value. The operations that I'm performing
manually are simply to run the AutoExec macro, and pressing the "Quit"
button on the form. So my questions are:

a) Why is it "losing" the value between setting it and calling the
function to quit?

b) Why does this behaviour change if I press RESET and run it again?

In case it might help here is the relevant code:

' Function to load the variable
Public Sub GetAllQueries()

Dim rcd As DAO.Recordset

Set rcd = CurrentDb.OpenRecordset("SELECT tblDatabaseQueries.QryID
FROM tblDatabaseQueries;")
If rcd.RecordCount > 0 Then
With rcd
.MoveFirst
Do
MyStrVar = MyStrVar & "*" & !QryID & "*"
.MoveNext
Loop While Not (rcd.EOF)
End With
End If

End Sub

'Function to quit
Private Sub CloseDatabase_Click()
Dim Ref As Reference
Dim bar As CommandBar
Dim Qry As QueryDef

For Each Ref In References
If Ref.Name = "Word" Then References.Remove Ref
Next Ref
For Each bar In Application.CommandBars
bar.Enabled = True
Next bar
Call subfrmManageDatabaseQueries_Enter

For Each Qry In CurrentDb.QueryDefs
If InStr(MyStrVar , "*" & Qry.Name & "*") > 0 Then
CurrentDb.QueryDefs.Delete (Qry.Name)
End If
Next Qry
DoCmd.DeleteObject acTable, "tblEditedQueries"
DoCmd.Quit
End Sub

 

[a a r o n _ k e m p f]

bottom line is that you've gotten bit by an Access bug.

Maybe if you weren't choosing to use a depecrated DAL; then maybe you
would have more success.
Move to ADO.. 'things just work' there.

-Aaron

 

[Bob Larson]

Oh little girl Aaron - you aren't man enough to figure out how to tie your
shoes.

bottom line is that you've gotten bit by an Access bug.

Maybe if you weren't choosing to use a depecrated DAL; then maybe you
would have more success.
Move to ADO.. 'things just work' there.

-Aaron

 

[strive4peace]

Hi Teddy,

the value of global variables will be lost if your application has an
unhandled error.

Add an error handler to each procedure in your code

put this at the top of your program, right after the procedure
declaration (skip a line first for better readability)

then come the statements of your procedure

then the lines at the bottom -- be sure to replace ProcedureName

'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err
'~~~~~~~~~~~~~~~~~~~~~~

... then your statements


put this at the end of the procedure

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any -- ie:
' if Not rst is Nothing then
' rst.close
' set rst = Nothing
' end if
Exit Function 'or Exit Sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
'~~~~~~~~~~~~~~~~~~~~~~

where
ProcedureName is the name of your procedure so you can identify what
code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err, I like to use the
same ones all the time -- they only have to be unique within a procedure.

if you get an error, press CTRL-BREAK when the message box pops up,
Enter to dismiss the dialog box

this takes you into the code

right-click on the *Resume* statement
from the shortcut menu, choose --> Set Next Statement

then press F8 to resume with the statement that caused the problem --
you can fix it!
pressing F8 executes one statement at a time

press F5 to continue execution automatically


~~~
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Resume goes back to
the offending line. When code Stops, press F8 to execute one statement
at a time.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[a a r o n . k e m p f]

store your variables in a _TABLE_ instead of off in la-la land.

that's how I do it-- because JET is too flaky.

-Aaron

 

[JvC]

So the code used to work, and it doesn't now. If you look at the code
you posted below, I think your line:
MyStrVar = MyStrVar & "*" & !QryID & "*"
could be the problem. If you somehow got a dreaded NULL in !QryID, this
entire loop will evaluate to NULL. Is the tblDatabaseQueries table
being reloaded at some point, that would cause this behavior?

Look at your table, and find the record where !QryID is Null and fix
it. Then redesign the table so it doesn't allow Nulls in that field.
Also, if the table is being reloaded at some point, make sure all of
the records are cleared.

Good luck!

John

It happens that (e-mail address removed) formulated :

 

[Wayne-I-M]

Sorry Aaron but you are not correct here.

Best bet would be to do a combination of what Crystal and Salad (strange
name) say and to source either an object or a control from the variable "and"
to include an error handeling set which can't be run directly from the
AutoExec - this could be included in the tblDatabaseQueries as an unbound
calculation via a subquery feeder to tblDatabaseQueries

 

[BruceM]

One reason a global variable will lose its value is because of an unhandled
error.

I assume you declared MyStrVar in the declarations section of the form's
code module or a standard module, but that is not clear. Further, as I
understand you have to say in the use of the global variable.

I would add error handling to all of the functions involved in this process.
For another thing, set a watch when you step through the code. This may let
you determine exactly what happens to the variable. You may have done this,
but again it is not clear.

Ignore Aaron, if you are not already doing so.

 

[teddysnips]

Weird.

[...]

Thanks for all the comments, helpful or not! Yes, ADO is "better"
than DAO, but there's no budget to replace it. Yes, using various
tricks to link the front and back ends is a good idea, but I've
already done that. Sure, hidden fields on hidden forms are a good
wheeze (I've spent the last six years doing this in ASP.NET!) Where
feasible, I've added error handling but, as I said, there's no budget
to make the whole thing watertight.

I now know what the problem is, and it was a sort of error, but a
silent one. The application automates Word but for various reasons,
the reference to Word cannot be static and is set dynamically at start
up and removed during the closing routine:

For Each Ref In References
If Ref.Name = "Word" Then References.Remove Ref
Next Ref

All I had to do was to change the order of the various operations in
AutoExec - specifially, to make sure that the reference was added
BEFORE the assignment of the global variable.

At some point in the future I'll suggest to my client that the code
could do with a serious going-over but, despite them being one of the
most successful companies in the UK, they are unlikely to stump up for
this until they really have to.

Thanks all

Edward

 

[BruceM]

There is a utility here that can add error handling at the click of a
button. You may want to customize the error handling, but it is very easy
to put in place:
http://www.mztools.com/v3/mztools3.aspx
Now error handling is feasible everywhere.

Just curious as to the basis for your statement that "ADO is 'better' than
DAO."

Weird.

[...]

Thanks for all the comments, helpful or not! Yes, ADO is "better"
than DAO, but there's no budget to replace it. Yes, using various
tricks to link the front and back ends is a good idea, but I've
already done that. Sure, hidden fields on hidden forms are a good
wheeze (I've spent the last six years doing this in ASP.NET!) Where
feasible, I've added error handling but, as I said, there's no budget
to make the whole thing watertight.

I now know what the problem is, and it was a sort of error, but a
silent one. The application automates Word but for various reasons,
the reference to Word cannot be static and is set dynamically at start
up and removed during the closing routine:

For Each Ref In References
If Ref.Name = "Word" Then References.Remove Ref
Next Ref

All I had to do was to change the order of the various operations in
AutoExec - specifially, to make sure that the reference was added
BEFORE the assignment of the global variable.

At some point in the future I'll suggest to my client that the code
could do with a serious going-over but, despite them being one of the
most successful companies in the UK, they are unlikely to stump up for
this until they really have to.

Thanks all

Edward

 

[Ken Sheridan]

John:

Nulls do not propagate in concatenation operations in fact where the &
operator is used. They do in arithmetical operations, so if the +
arithmetical operator had been used what you say would be true. This can be
useful for suppressing unwanted characters, e.g. (FirstName + " ") &
(MiddleName + " ") & LastName, where the parenthesised expressions would
evaluate to Null if FirstName or LastName are Null, and suppress the
redundant space character.

Ken Sheridan
Stafford, England

 

[Klatuu]

I disagree.
I find DAO much easier to code and peforms better.
Even after pushing ADO for a few years, Microsoft has given up on it and
gone back to making DAO the default. There has been no work done on ADO
since (I think) 2000.

Access using DAO is a very stable and reliable platform (unlike Aaron).
--
Dave Hargis, Microsoft Access MVP

Weird.

[...]

Thanks for all the comments, helpful or not! Yes, ADO is "better"
than DAO, but there's no budget to replace it. Yes, using various
tricks to link the front and back ends is a good idea, but I've
already done that. Sure, hidden fields on hidden forms are a good
wheeze (I've spent the last six years doing this in ASP.NET!) Where
feasible, I've added error handling but, as I said, there's no budget
to make the whole thing watertight.

I now know what the problem is, and it was a sort of error, but a
silent one. The application automates Word but for various reasons,
the reference to Word cannot be static and is set dynamically at start
up and removed during the closing routine:

For Each Ref In References
If Ref.Name = "Word" Then References.Remove Ref
Next Ref

All I had to do was to change the order of the various operations in
AutoExec - specifially, to make sure that the reference was added
BEFORE the assignment of the global variable.

At some point in the future I'll suggest to my client that the code
could do with a serious going-over but, despite them being one of the
most successful companies in the UK, they are unlikely to stump up for
this until they really have to.

Thanks all

Edward

 

[Troll Chaser]

Aaron the troll is wrong again. Global variables are often dropped when an
application has an error in the code. You may not notice it if error
handling is turned off, or if you use "On error resume next" in your code.

Try using a hidden form to hold the temporary values of global variables.

bottom line is that you've gotten bit by an Access bug.

Maybe if you weren't choosing to use a depecrated DAL; then maybe you
would have more success.
Move to ADO.. 'things just work' there.

-Aaron

 

[bcap]

Read here the words of a dickhead:


message
store your variables in a _TABLE_ instead of off in la-la land.

that's how I do it-- because JET is too flaky.

-Aaron

 

[bcap]

ADO isn't better than DAO, it's just different. Ignore anything the
troll-brat Kempf says.

You don't need to buy ADO, it's already there in Access 2003, and you could
just start using it if you really wanted to. You can even mix it with DAO
in the same application, so long as you disambiguate.

Can't imagine why you'd want to, though, as it's Microsoft's most orphaned
data access technology. DAO has had a major revamp for Access 2007 and, as
you are clearly aware, the brave (newish) world of dotnet is all ADO.Net,
which shares nothing with ADO except three letters. So, despite what the
troll-brat Kempf might claim, ADO is on the road to nowhere.

Glad you got it sorted!

Weird.

[...]

Thanks for all the comments, helpful or not! Yes, ADO is "better"
than DAO, but there's no budget to replace it. Yes, using various
tricks to link the front and back ends is a good idea, but I've
already done that. Sure, hidden fields on hidden forms are a good
wheeze (I've spent the last six years doing this in ASP.NET!) Where
feasible, I've added error handling but, as I said, there's no budget
to make the whole thing watertight.

I now know what the problem is, and it was a sort of error, but a
silent one. The application automates Word but for various reasons,
the reference to Word cannot be static and is set dynamically at start
up and removed during the closing routine:

For Each Ref In References
If Ref.Name = "Word" Then References.Remove Ref
Next Ref

All I had to do was to change the order of the various operations in
AutoExec - specifially, to make sure that the reference was added
BEFORE the assignment of the global variable.

At some point in the future I'll suggest to my client that the code
could do with a serious going-over but, despite them being one of the
most successful companies in the UK, they are unlikely to stump up for
this until they really have to.

Thanks all

Edward

 

[a a r o n _ k e m p f]

how am I incorrect?

can MS Access _NOT_ store variables in a table?

 

[a a r o n _ k e m p f]

I disagree.

DAO is pointless. Any DAL that makes you clean up your variables-- is
just flat out-- not useful to me.

I disagree.
I find DAO much easier to code and peforms better.
Even after pushing ADO for a few years, Microsoft has given up on it and
gone back to making DAO the default.  There has been no work done on ADO
since (I think) 2000.

Access using DAO is a very stable and reliable platform (unlike Aaron).
--
Dave Hargis, Microsoft Access MVP

Weird. [...]

Thanks for all the comments, helpful or not!  Yes, ADO is "better"
than DAO, but there's no budget to replace it.  Yes, using various
tricks to link the front and back ends is a good idea, but I've
already done that.  Sure, hidden fields on hidden forms are a good
wheeze (I've spent the last six years doing this in ASP.NET!)  Where
feasible, I've added error handling but, as I said, there's no budget
to make the whole thing watertight.

I now know what the problem is, and it was a sort of error, but a
silent one.  The application automates Word but for various reasons,
the reference to Word cannot be static and is set dynamically at start
up and removed during the closing routine:

For Each Ref In References
    If Ref.Name = "Word" Then References.Remove Ref
    Next Ref

All I had to do was to change the order of the various operations in
AutoExec - specifially, to make sure that the reference was added
BEFORE the assignment of the global variable.

At some point in the future I'll suggest to my client that the code
could do with a serious going-over but, despite them being one of the
most successful companies in the UK, they are unlikely to stump up for
this until they really have to.

Thanks all

Edward

 

[a a r o n _ k e m p f]

DAO did not get a major revamp... it is merely a new badge.

ADO has been through many incarnations over the past decade.
DAO wasn't included with Windows or Office for 5 years.

-Aaron

 

[a a r o n _ k e m p f]

DAO is nowhere a decade ago!

 

[bcap]

A dickhead writes:

DAO did not get a major revamp... it is merely a new badge.

ADO has been through many incarnations over the past decade.
DAO wasn't included with Windows or Office for 5 years.

-Aaron