Life of a variable issue... 1

[mike777]

Hello,
To summarize, I have the following.
1. An array variable, declared as Public in the declarations section of the module.
2. A sub procedure that populates the array variable, and calls a MakeForm sub procedure.
3. A MakeForm subprocedure that uses the array values to create the form and also adds code to certain controls on the form, using CreateEventProc, InsertLines, etc.

The interesting (or frustrating) thing is, that the MakeForm procedure is called from the last line of "2.". I've placed message boxes at the very end of "2.", even after the Call MakeForm line. In the message box, I put Ubound(MyArrayVar). Everything is fine. But if I reference the array variable from the form itself, I get Error #9, subscript out of range, and I believe this means that my array variable has been killed.

Likewise, if I comment out the line that calls MakeForm from "1.", and comment out the message box there, and then I go and create my own new form and put on it a command button that says msgbox ubound(MyArrayVar), everything is fine.

If I let the MakeForm procedure run through as normal, then go and create my own form, using msgbox ubound(MyArrayVar), then I get the Error #9 again.

So the short version of the long story is:
I am confident that by declaring a Public variable in the declarations section of a module, that you will have a variable available for use as long as the application is running, regardless what other procedures are run between assigning a value to the variable and calling the variable. Is this correct do you think?
So how is it, possibly, that my array variable is getting wiped out here, when, at the end of my Call MakeForm statement, and just before the final End Sub, it is still available to other class modules, etc.??

Sorry for the long note. As you can see, I've spent a lot of time on this. Any help you can provide is very much appreciated.

Thanking you in advance.

-Mike

 

[rewdee]

Hi Mike

What you are saying is puzzling because I would have thought that the public array would be available also. All I can suggest is have you tried saving the form before you reference the public array? What about checking the object browser, can you see what the value is when you are inside the form? Can you use a collection instead of an array?

Hope something might help?
Rewdee

 

[cmmrfrds]

If you can't get around the problem with the array, you could substitute a temporary ADO recordset. It would be defined as Public in the Standard Module as you are doing with the array. Recordsets are easy to work with.

Example.
Public rs as New ADODB.Recordset

In some function, create the recordset and fill the values.
Dim varrFields as Variant
With rs.Fields
.Append "field1", adVarChar, 25, adFldIsNullable
.Append "field2", adVarChar, 10, adFldIsNullable
etc
End With

varrFields = Array("field1", "field2&quot
With rs
.Open
.Addnew varrfields, Array("val1", "val2&quot
.Addnew varrfields, Array("val1", "val2&quot
.Addnew varrfields, Array("val1", "val2&quot
etc
End With
rs.MoveFirst

When you use the values do a filter to get the one you want.
rs.Filter = "field1 = " & "'" & aval & "'"
Do something with data
rs.Filter = adFilterNone
Now you can refilter for another value

When done with recordset
Set rs = Nothing

 

[mike777]

Rewdee,
Glad you are puzzled. Now I know the problem isn't me (for a change!). It is very strange, because the array is available immediately following its creation, after the MakeMyForm sub-procedure is called, at the end of the MakeMyForm sub-procedure and at the end of the original sub-procedure that calls the MakeMyForm procedure. But when the original procedure ends....voila!!...the variable is dead.
I will try commenting out the part of the MakeMyForm procedure that adds code to the new form. I believe something is happening there that resets the project. I'll report back.
CMMRFRDS: Thanks for the suggestion on that. I may have to give that a whirl, but I am afraid that since my array public variable is being reset, that my Recordset public variable may suffer the same fate. Let me do a little research (more research!!) and testing. What is your opinion about my concern?
-Mike

 

[mike777]

Yep, as soon as I comment out the code:

'        With mDl
'            mdlLine = .CreateEventProc("MouseMove",  _
             newLbl.Name)
'            .InsertLines mdlLine + 1, newLbl.Name _
             & ".specialeffect=acEffectRaised"
'            mdlLine = .CreateEventProc _
             ("MouseMove", "Detail")
'            .InsertLines mdlLine + 1, newLbl.Name  _
             & ".specialeffect=aceffectnormal"
'            mdlLine = .CreateEventProc("mousedown",  _
             newLbl.Name)
'            .InsertLines mdlLine + 1, newLbl.Name  _
             & ".specialeffect=aceffectsunken"
'            mdlLine = .CreateEventProc("mouseup",  _
             newLbl.Name)
'            .InsertLines mdlLine + 1, newLbl.Name  _
             & ".specialeffect=aceffectraised"
'            mdlLine = .CreateEventProc("click",  _
             newLbl.Name)
'            .InsertLines mdlLine + 1, "msgbox ubound _
             (strarrbrchs)"
'            .InsertLines mdlLine + 2, "Call  _
             CreateProc4NextResults(me.name)"
'        End With

It works fine. Do you see anything there that would cause the project to reset?
Thanks.

 

[mike777]

Definitely an issue with writing code to the form module from VBA.
If I do:

Public Const XXXX as Integer=1356

in the declarations section of the code module...Then

Sub MySub()
  ...The rest like below
End Sub

The value of XXXX is maintained when I, say, call the value of XXXX from a command button on a new form.

But if I do:

Public XXXX as Integer

Then

Sub MySub()
  XXXX=1356
  Call MakeMeAForm
End Sub

Sub MakeMeAForm()
  Dim fRm as form, mDl as module
  set fRm=CreateForm
  set mDl=fRm.Module
  with mDl
    .CreateEventProc(Blahblahblah)
  End With
End Sub

The value of XXXX is reset to 0.

The interesting thing is, that if I go back and try to modify the code module, I get the error message that says, "This action will reset your project..". So, the app THINKS it has not been reset already. Apparently it hasn't, I guess, even though all the variable values have been dumped...hmmm...

Also interesting is if I do (see &quot;<<<--&quot; line):

Public XXXX as Integer

Then

Sub MySub()
  XXXX=1356
  Call MakeMeAForm

  XXXX=1356 '<<<--

End Sub

The value of XXXX is STILL reset to 0.

I also tried making Sub MySub and Sub MakeMeAForm public, as in Public My Sub, Public MakeMeAForm, to no avail. Any suggestions? Is there a psychotherapy forum? I'm going to need one soon, I think.

 

[mike777]

No solution to this.
I'm just going to have to write my variables to a text file, and open the text file when I need the values.

 

[VBslammer]

Microsoft Knowledge Base Article - Q198637

ACC2000: Access Methods That Reset The Visual Basic Project
The information in this article applies to:
Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).


Symptoms
When you use certain Access methods that modify modules or references, all variables in the same Visual Basic for Applications project are reset.

You reset your variables with the following methods:
AddFromFile (Module Object)
AddFromFile (References Collection)
AddFromString
AddFromGUID
CreateEventProc
DeleteLines
InsertLine
InsertText
ReplaceLine
Remove (References Collection)

Cause
These methods automatically reset the database's Visual Basic for Applications project.

Resolution
Move any variables that you do not want to be reset to an add-in or library database. Add-ins and library databases have separate Visual Basic for Applications projects; therefore, variables stored in them are not affected when you use one of these methods.

If all else fails, check the knowledge base


VBSlammer

 

[mike777]

Right on!!!
You really are the slammer!