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
On Jul 28, 5:30*am, [e-mail] wrote:

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

[Bob Larson]

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

"a a r o n _ k e m p f" <aa*********@hotmail.comwrote in message
news:fe**********************************@k36g2000 pri.googlegroups.com...
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
On Jul 28, 5:30 am, [e-mail] wrote:

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

[Salad]

te********@hotmail.com wrote:

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

If something doesn't work, even if you didn't write it, it might make
sense to change it so it does.

This would be an excellent time to make it better. If you have a split
database, from info at Tony Toew's site and others, the recommendation
is that you create a hidden form that's sole purpose is to create a link
between the front end and back end that is called when you open the
application. This can help speed up the system.

Docmd.Openform "PersistentLink",,,,acHidden

With this form you could create a textbox. When you run the autoexec,
store the value of the string to the textbox. Then reference the
textbox, not the variable.

There shouldn't be a problem with globals unless you have an error that
is not trapped. But since you do have a problem with globals, change
your direction.

[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 :)
*


te********@hotmail.com wrote:

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]

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
On Jul 28, 5:30*am, [e-mail] wrote:

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

[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 te********@hotmail.com formulated :

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

[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.

"a a r o n _ k e m p f" <aa*********@hotmail.comwrote in message
news:fe**********************************@k36g2000 pri.googlegroups.com...
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
On Jul 28, 5:30 am, [e-mail] wrote:

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

[bcap]

Read here the words of a dickhead:
"a a r o n . k e m p f @ g m a i l . c o m" <aa*********@gmail.comwrote in
message
news:86**********************************@z26g2000 pre.googlegroups.com...
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]

Sorry but you're wrong there. It's the "+" concatenation operator that
evaluates to Null if one of the operands is null. The "&" operator treats
the Null operand as a zero-length string.

"JvC" <jo******@earthlink.netwrote in message
news:DE*****************@newsfe09.iad...

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 te********@hotmail.com formulated :

> 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

[Linq Adams via AccessMonster.com]

aaron, pleaseeeeeeeeee close your eyes and go play in heavy traffic!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200807/1

[JvC]

Yup! Just tested it, and I got it backwards! Thanks!

bcap brought next idea :

Sorry but you're wrong there. It's the "+" concatenation operator that
evaluates to Null if one of the operands is null. The "&" operator treats
the Null operand as a zero-length string.

"JvC" <jo******@earthlink.netwrote in message
news:DE*****************@newsfe09.iad...

>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 te********@hotmail.com formulated :

>> 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

[Keith Wilby]

"bcap" <bc**@nospam.nowherewrote in message
news:48**********************@news.zen.co.uk...

More dickheadery:

[Scrubs' Dr Cox]
I ... don't know ... who you are ... but thank you <kisses forehead>
[/Scrubs' Dr Cox]

;-)

[Bob Quintal]

Good morning Teddy,

essentially, the why of it happening is that somewhere in the code
there is an error that is incorrectly handled, perhaps just glossed
over with a resume next, that causes Access to clear all the global
variables. perhaps the issue is with a query that cannot be deleted,
or something of that nature.

The second time the routine is run, the error doesn't occur, so your
variable is present.

Q

te********@hotmail.com wrote in
news:80d1339e-791a-44e6-8653-746bd409fca0
@k13g2000hse.googlegroups.co
m:

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

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[Bob Quintal]

a a r o n _ k e m p f <aa*********@hotmail.comwrote in
news:fefeb4e9-bc9a-4a53-b291-70054c7c6ed0
@k36g2000pri.googlegroups.co
m:

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

No the line is Aaron Kempf needs a spanking on the bottom, an to be
told to go away because he's wrong again.

>

On Jul 28, 5:30*am, [e-mail] wrote:

>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.Qr

yID

>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

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[sgmaxmp11]

"Bob Quintal" <rq******@sPAmpatico.caà¢Õ¹à»ç¹¢éͤÇÒÁ
news:Xn**********************@66.175.223.2...

[lyle fairfield]

Are you saying that Resume Next results in pointers to Global
Variables being released, ie a Global String Variable length is now
zero?

I've never experienced that.

Here is some code that indicates it is not so.

Global SomeGlobal$
' or Public SomeGlobal

Public Sub SomeTest()

SomeGlobal = "SomeString"
Debug.Print "Before Error SomeGlobal = " & SomeGlobal

On Error Resume Next
Err = 0
Debug.Print 3 / 0
If Err.Number 0 Then Debug.Print _
Err.Description & vbNewLine & "There was an error!"
On Error GoTo 0

Debug.Print "After Error SomeGlobal = " & SomeGlobal

End Sub

'Before Error SomeGlobal = SomeString
'Division by zero
'There was an error!
'After Error SomeGlobal = SomeString

I seldom use seldom Global Variables, preferring Modularly Scoped
Variables which are referenced by Public Functions, Registry Settings
and CurrentProject Properties. The Public Functions can meet the
"value lost" case by beginning with
If len(SomeGlobal) = 0 Then InitializeSomGlobal
where InitializeSomeGlobal is a procedure that does exactly that.

On Jul 28, 9:44*am, Bob Quintal <[e-mail]:

Good morning Teddy,

essentially, the why of it happening is that somewhere in the code
there is an error that is incorrectly handled, perhaps just glossed
over with a resume next, that causes Access to clear all the global
variables. perhaps the issue is with a query that cannot be deleted,
or something of that nature.

The second time the routine is run, the error doesn't occur, so your
variable is present.

Q

[e-mail] wrote in
news:80d1339e-791a-44e6-8653-746bd409fca0
@k13g2000hse.googlegroups.co
m:

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

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

[Bob Quintal]

lyle fairfield <ly************@gmail.comwrote in
news:a47f0afd-7f81-47a0-aa2d-4f5594c0f5ea@
34g2000hsh.googlegroups.com
:

Are you saying that Resume Next results in pointers to Global
Variables being released, ie a Global String Variable length is
now zero?

There exists and has to my recollection always existed a bug where
global variables are reset on some or all unhandled errors. I do
remember having a similar problem with an error that was handled by
a resume next, many years ago, (Access '97, maybe even Access 2).
Perhaps that was fixed.

How I wrote code years ago is different than how I code today.
That comes from experience gained on using the tools and features of
the language. I seldom use globals any more and pay more attention
to error handlers.

>
I've never experienced that.

Here is some code that indicates it is not so.

Global SomeGlobal$
' or Public SomeGlobal

Public Sub SomeTest()

SomeGlobal = "SomeString"
Debug.Print "Before Error SomeGlobal = " & SomeGlobal

On Error Resume Next
Err = 0
Debug.Print 3 / 0
If Err.Number 0 Then Debug.Print _
Err.Description & vbNewLine & "There was an error!"
On Error GoTo 0

Debug.Print "After Error SomeGlobal = " & SomeGlobal

End Sub

'Before Error SomeGlobal = SomeString
'Division by zero
'There was an error!
'After Error SomeGlobal = SomeString

I seldom use seldom Global Variables, preferring Modularly Scoped
Variables which are referenced by Public Functions, Registry
Settings and CurrentProject Properties. The Public Functions can
meet the "value lost" case by beginning with
If len(SomeGlobal) = 0 Then InitializeSomGlobal
where InitializeSomeGlobal is a procedure that does exactly that.

On Jul 28, 9:44*am, Bob Quintal <[e-mail]:

>Good morning Teddy,

essentially, the why of it happening is that somewhere in the
code there is an error that is incorrectly handled, perhaps just
glossed over with a resume next, that causes Access to clear all
the global variables. perhaps the issue is with a query that
cannot be deleted, or something of that nature.

The second time the routine is run, the error doesn't occur, so
your variable is present.

Q

[e-mail] wrote in
news:80d1339e-791a-44e6-8653-746bd409fca0
@k13g2000hse.googlegroups.co
m:

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

--
Bob Quintal

PA is y I've altered my email address.
** Posted fromhttp://www.teranews.com**

--
Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

[The Frog]

I just create a class module, and instantiate it when the application
starts. It has all the code necessary to handle the proper set up of
global vars and I find it keeps things neat and clean. Never had a
problem with it resetting to 'nothing'. If I want to know a value, I
just check the associated property :-)

Cheers

The Frog

[Tony Toews [MVP]]

te********@hotmail.com wrote:

>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:

Good trouble shooting.

However I would suggest an alternative approach to the Word reference issue.

Late binding means you can safely remove the reference and only have an error when
the app executes lines of code in question. Rather than erroring out while starting
up the app and not allowing the users in the app at all. Or when hitting a mid, left
or trim function call.

This also is very useful when you don't know version of the external application
will reside on the target system. Or if your organization is in the middle of moving
from one version to another.

For more information including additional text and some detailed links see the "Late
Binding in Microsoft Access" page at http://www.granite.ab.ca/access/latebinding.htm

As far as ADO vs DAO goes they both work just fine in Access and I see absolutely no
reason to convert from one to another.

And please ignore anything Aaron Kempf states. He is rather monomaniacial.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[rkc]

On Jul 31, 7:49 am, Bob Quintal <[e-mail]:

lyle fairfield <[e-mail] in
news:a47f0afd-7f81-47a0-aa2d-4f5594c0f5ea@
34g2000hsh.googlegroups.com
:

Are you saying that Resume Next results in pointers to Global
Variables being released, ie a Global String Variable length is
now zero?

There exists and has to my recollection always existed a bug where
global variables are reset on some or all unhandled errors. I do
remember having a similar problem with an error that was handled by
a resume next, many years ago, (Access '97, maybe even Access 2).
Perhaps that was fixed.

How I wrote code years ago is different than how I code today.
That comes from experience gained on using the tools and features of
the language. I seldom use globals any more and pay more attention
to error handlers.

On Error Resume Next handles errors as long as the code it is in is in
scope
and On Error Goto 0 has not been called.

To extend Lyle's example with a call to another method:

'------
On Error Resume Next
Err = 0
Call CauseError
If Err.Number 0 Then Debug.Print _
Err.Description & vbNewLine & "There was an error!"
On Error GoTo 0

Debug.Print "After Error SomeGlobal = " & SomeGlobal
'------

Sub CauseError()
Debug.Print 3 / 0
End Sub

The error in CauseError is handled by the calling code.

I would be a very scary thing indeed if On Error Resume Next did not
handle an error and global variables were reset because of it.