Referencing global variable - MS Access

Question

Hi,

Im trying to pass the name of a global variable to a sub routine and would like to know how to reference it. For example I could do the below with a control:

Private Sub passCtrlName(ctlName as String)
Me.Controls(ctlName) = "Whatever"
End Sub

Edit:

For Example

Public imGlobVar As String
Public Sub passGlobVar(frm as Form, ctlName as String, globVar as String)
frm.Controls(ctlName) = globVar
End sub

And call it as Private Sub imaButton_Click() imGlobVar = "something" Call passGlobVar(Me , txtBox1, imGlobVar) End sub

2nd Edit:

It seems that I could most definitely be barking up the wrong tree here, so I will explain what I'm trying to achieve.

I have a form that has textboxes for the users (risk) address, with a checkbox at the top that lets the user select that this address is the same as the 'contact' details already on the system, and the textboxes are locked.

Populating the textboxes is fine and works. What I use the global variables for is to improve usability (albeit slightly). The user can add new details, and if they hit the checkbox 'make same as contact' the details that they have entered are stored in the global variables, one for each control.

If the user has made a mistake by hitting the checkbox, they haven't lost these value, and by unchecking the box the entered values are returned. I hoped to create a sub routine where I could pass the name of the global variable and control and calling this routine, as opposed to writing it out for each control.

I have a feeling that I could be using the wrong technique to achieve my goals. But in answer to my original question, it appears that you can not pass global variables to sub routines in the manner that I wished.

Answer 1

You do not need to pass global variables, you can simply refer to them by name. Note that global variables are reset if an unhandled error occurs.

In http://msdn.microsoft.com/en-us/library/dd897495(office.12).aspx you will find a section on Scope and Lifetime of Variables and Constants.

In a module:

Option Explicit 
Public glbVarName As String
Const MyConstant=123

Sub InitVar
   glbVarName="Something"
End Sub

Any other module, includeing a form's class module:

Sub SomeSub
    MsgBox glbVarName
    SomeVar=2+MyConstant
End Sub

Answer 2

If you're asking if you can dynamically reference global variables using a string containing the variable name the answer is no. You could use a single global array and pass the index, which would allow you to dynamically reference an element of the array.

[Edit]
In response to the clarification in the question: You could just save the value of each control to its Tag property when the user checks the checkbox. Then, if the user unchecks the checkbox, you can just loop over your controls and assign the value from the Tag back to the Value of the control.

Answer 3

You could store the values from your controls in a Dictionary object, using the control names as the dictionary keys. Then you can retrieve the value for each control based on the control's name.

Option Compare Database
Option Explicit

Const cstrMyControls As String = "Text0,Text2,Text4,Text6"
Dim objDict As Object

Private Sub chkToggle_Click()
    If Me.chkToggle = True Then
        Call SaveValues
    Else
        Call RestoreValues
    End If
End Sub

Private Sub SaveValues()
    Dim varControls As Variant
    Dim i As Long

    Set objDict = Nothing 'discard previous saved values '
    Set objDict = CreateObject("Scripting.Dictionary")
    varControls = Split(cstrMyControls, ",")
    For i = 0 To UBound(varControls)
        objDict.Add varControls(i), Me.Controls(varControls(i)).Value
    Next i
End Sub

Private Sub RestoreValues()
    Dim varControls As Variant
    Dim i As Long
    If objDict Is Nothing Then
        'MsgBox "No values to restore." '
    Else
        varControls = objDict.keys()
        For i = 0 To UBound(varControls)
            Me.Controls(varControls(i)).Value = objDict(varControls(i))
        Next i
    End If
End Sub

Answer 4

I use additional field in table - name cancel - of course boolean - when i'm not sure if contents of fields will be valid I set it true. If this field will be true by the end - then I clean up (it may be all record or some fileds of course). Very easy.