VBA controls reference (form) global variables?

Question

OK, I've been out of Access programming for a couple of versions, but I could swear I used to be able to point controls at form global variables. Sample code as follows:

Option Compare Database
Option Explicit
Dim Testvar As String

Private Sub Form_Load()
Testvar = "Load"
End Sub

Private Sub Form_Open(Cancel As Integer)
Testvar = "open"
End Sub

Private Sub Text0_Click()
Testvar = "settest"
End Sub

I should be able to put a text box on the control that can see the TestVar variable, but the controls don't do it. Also, I used to be able to do that with the form's record source.

So, the questions - Am I crazy - that was never possible? Or have I forgotten how to address the form?

And then the most important question - what is the best way to get around this?
The most common way this is used is to pass in OpenArgs (record keys in this case) which is then parsed in to global vars and then a couple of controls display the open args and/or look up values to display from the keys.

I really hate to have to build routines that rebuild and load the record sources for the controls. Hope someone knows a better approach

Are you trying to bind the text box to the module level variable? — RubberDuck
– RubberDuck, Commented Dec 9, 2014 at 14:05
That is correct. I would have thought that the control would at least be able to "see" the variable. Even if it might not allow direct binding. — alanTuring
– alanTuring, Commented Dec 9, 2014 at 14:16
@alanTuring you have to declare the global variable in a regular module rather than on the form module. Declare it as public myGlobal as String then it becomes accessible across all modules. — user2140173
– user2140173, Commented Dec 9, 2014 at 14:33
@vbaforall - I wouldn't want to declare global vars for form specific values. A var declared at the form level should be available to all objects on the form. — alanTuring
– alanTuring, Commented Dec 9, 2014 at 14:37

HansUp · Accepted Answer · 2014-12-09 16:13:49Z

2

In addition to your existing event procedures, you can add a function in the form module which retrieves the value of the Testvar module variable.

Function GetTestvar() As String
    GetTestvar = Testvar
End Function

Then use =GetTestvar() as the Control Source for Text0.

edited Dec 9, 2014 at 16:13

answered Dec 9, 2014 at 15:17

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

alanTuring Over a year ago

OK, this is probably what I was remembering. In the best spirit of OOP, having a put and get function for form external references. This works just fine in my actual case - a field with a dlookup on it as follows - =DLookUp("LookupDesc","vlookup","LookupGroup='FUNS' And LookupID= '" & GetFunctID() & "'")

HansUp Over a year ago

Not sure why you're talking about external references. I revised this answer to clarify my intention was to create the function in the form's code module.

alanTuring Over a year ago

OK, a little "off topic" - using a function to fetch a value off of another "object" is very OOPie. In a very formal coding model, any values needed by another module should be called with a "get". I just thought it was a very nice approach.

RubberDuck · Accepted Answer · 2014-12-09 14:19:42Z

2

You have to actually set the value of the text box. There's no way (to the best of my knowledge) to bind a text box to a variable.

Option Compare Database
Option Explicit

Private Sub Form_Load()
    Text0.Value = "Load"
End Sub

Private Sub Form_Open(Cancel As Integer)
    Text0.Value = "open"
End Sub

Private Sub Text0_Click()
    Text0.Value = "settest"
End Sub

Of course, you could store the value in a variable and use it to set the value instead, but it makes little sense to do so in this simple example.

answered Dec 9, 2014 at 14:19

RubberDuck

12.9k5 gold badges56 silver badges99 bronze badges

1 Comment

alanTuring Over a year ago

Rats. I could have sworn that it was possible to reference a variable in a control. So be it. Thanks, guys

HansUp · Accepted Answer · 2014-12-09 14:55:08Z

2

The TempVars collection is a feature introduced in Access 2007. So, if your Access version is >= 2007, you could use a TempVar to hold the string value. Then you can use the TempVar as the control source for your text box.

With =[TempVars]![Testvar] as the Control Source for Text0, the following event procedures do what you requested.

Private Sub Form_Open(Cancel As Integer)
    TempVars.Add "Testvar", "Open"
End Sub

Private Sub Form_Load()
    TempVars("Testvar") = "Load"
End Sub

Private Sub Text0_Click()
    TempVars("Testvar") = "settest"
    Me.Text0.Requery
End Sub

Note: [TempVars]![Testvar] will then be available throughout the application for the remainder of the session. If that is a problem in your situation, you could remove the TempVar at Form Close: TempVars.Remove "Testvar"

edited Dec 9, 2014 at 14:55

answered Dec 9, 2014 at 14:41

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

4 Comments

RubberDuck Over a year ago

That's a pretty slick way of doing this. I didn't realize you could do that with TempVars. Just to note though, TempVars are available throughout the application, so this could get a bit messy if there are many controls using this method. They're also only available in Access (or with a reference to Access). ++

HansUp Over a year ago

Yes, I added a warning about that. Thanks.

alanTuring Over a year ago

@HansUp - that looks like a fairly workable solution. The TempVars are global in scope, which I don't care for, but they do have the properties I'm looking for. Thanks for the tip - I've never heard of TempVars.

HansUp Over a year ago

@alanTuring I understand so added another answer which may be closer to what you want.

Mohsin · Accepted Answer · 2014-12-10 10:34:01Z

Requirement was: To show the login Id of the application user on all forms in the application.

Here is how I implemented this:

Create a module: module_init_globals
with the following code:

Option Compare Database

'Define global variables

Global GBL_LogonID as string

Option Explicit

Public Sub init_globals ()

   'Initialize the global variables

   'Get_Logon_Name is a function defined in another VBA module that determines the logon ID         of the user

    GBL_LogonID = Get_Logon_Name()

 End Sub


On the main/first form - we need to call the module that will initialize the global variables:

In the code for "on open" event I have:

Private Sub Form_Open (Cancel as Integer)
   call init_globals
End Sub




then on each of the forms in the app, I have a text control - txtUserID to display the  logon id of the user

and I can set it's value in the "on open" event of the form.

txtUserID.value = GBL_LogonID

Collectives™ on Stack Overflow

VBA controls reference (form) global variables?

4 Answers 4

3 Comments

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related