Variable in a form won't keep its value after being used in the call to another form-CodePudding

I have a form with a variable in it called "VigilTable." This variable gets its value from the calling string OpenArgs property.

Among other things, I use this variable in the call string when opening other forms.

But it only works the first call.

MsgBox VigilTable before the call will always show "Spring2022" or whatever on the first call but always comes up blank on succeeding calls (and I get "invalid use of NULL" when the called form attempts to extract the value from OpenArgs). The variable is dimmed as String in the General section of the form's VBA code.

So what's happening here? And can I fix it?

Thanks.

CodePudding user response：

Ok, so you delcared a variable at the form level (code module) for that given form.

and we assume that say on form load, you set this varible to the OpenArgs of the form on form load.

So, say like this:

  Option Compare Database
  Option Explicit
  
  Public MyTest     As String
  
  
  
  Private Sub Form_Load()
  
      MyTest = Me.OpenArgs
  
  End Sub

Well, I can't say having a variable helps all that much, since any and all code in that form can use me.OpenArgs.

but, do keep in mind the following:

ONLY VBA code in the form can freely use that variable. It is NOT global to the applcation, but only code in the given form.

However, other VBA code outside of the form can in fact use this variable. But ONLY as long as the form is open.

So, in the forms code, you can go;

MsgBox MyTest

But, for VBA outside of the form, then you can get use of the value like this:

Msgbox forms!cityTest.MyTest

However, do keep in mind that any un-handled error will (and does) blow out all global and local variables. So, maybe you have a un-handled error.

Of course if you compile (and deploy) a compiled accDB->accDE, then any errors does NOT re-set these local and global variables.

but, for the most part, that "value" should persist ONLY as long as the form is open, and if you close that form, then of course the values and variables for that form will go out of scope (not exist).

Now, you could consider moving the variable declare to a standard code module, and then it would be really global in nature, but for the most part, such code is not recommended, since it hard to debug, and such code is not very modular, or even easy to maintain over time.

So, this suggests that some error in VBA code is occurring, and when that does occur, then all such variables are re-set (but, the noted exception is if you compile down to an accDE - and any and all variables will thus persist - and even persist their values when VBA errors are encountered.

CodePudding user response：

For a string variable, a more robust solution not influenced by any error, should be writing/reading in/from Registry. You can use the, let as say, variable (the string from Registry) from any workbook/application able to read Registry.

Declare some Public constants on top of a standard module (in the declarations area):

Public Const MyApp As String = "ExcelVar"
Public Const Sett As String = "Settings"
Public Const VigilTable As String = "VT"

Then, save the variable value from any module/form:

SaveSetting MyApp, Sett, VigilTable , "Spring2022" 'Save the string in Regisgtry

It can be read in the next way:

Dim myVal as String

myVal = GetSetting(MyApp, Sett, VigilTable , "No value") 'read the Registry
 If myVal = "No value" Then MsgBox "Nothing recorded in Registry, yet": Exit Sub  
Debug.print myVal