Global variables in Form Header gives #Name?

revver

Active Member
Joined
Dec 20, 2007
Messages
257
I thought this would be simple but ...

Background
I have a module mdlGlobal which contains
Code:
Public gstrEntityName As String 'declare global Entity variables
Public gstrEntityWWW As String
------------------------------------------------
Public Sub SetGlobals()
    gstrEntityName = DLookup("EntityName", "Administration", "AdminID =1")
    gstrEntityWWW = DLookup("Website", "Administration", "AdminID =1")
End Sub
and I have frmRrelink which launches at startup and whose Open event ends with
Code:
SetGlobals
It then loads frmMenu and closes itself. At this point, in the immediate window
. Debug.Print gstrEntityName
gives the correct reply.

Problem
frmMenu contains txtFormBanner in the form's header. I want to include the contents of gstrEntityName in the banner. I tried a ControlSource of =[gstrEntityName] & vbCrLf & "Contact Menu"
but I get #Name? error
even =[gstrEntityName] gives #Name?

What am I missing?
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
the global variable only exists in the VBA environment, I think. If you want it in a textbox, you'd need a "getter" method:
Code:
Public Function getGStrEntityName()
    getGStrEntityName = gstrEntityName
End Function

Then in your textbox:
Code:
=getGStrEntityName()
 
Upvote 0
That worked, thanks.
It seems clumsy to have to go to that sort of trouble. Why shouldn't global variables be global? !!
 
Upvote 0
If I am not mistaken, I think Access 2010 (or is it Access 2013) introduced a form of "public variables" but I can't remember what they call it. Does anyone know?

It is also possible to create a table of such values for your database. I often have a table called tblConstants (or DBConstants or DBTokens or DBVars - I seem to not be able to decide). Then you just update the table and you can use the value in queries or DLookups and so on. In this case, however, I usually make sure to have an ID field with a number data type - set the validation rule to "1" or "=1" and set its value to the same. That way it is impossible to accidentally add another row, which could play havoc with queries expecting a single record from this table. Most often I use this table to set reporting dates for reports - basically a date for the current fiscal period or the begin and end dates of the current reporting period.
 
Last edited:
Upvote 0
@xenou, it is the TempVars collection

In Access 2007 TempVars collection was introduced to to do what you want. The TempVars are available in places when you can not acerss VBA variables.

See: Power Tip: Maximize the use of TempVars in Access 2007 and 2010

Example of how to use:

Code:
Public Sub SetGlobals()     


     TempVars.Add "gstrEntityName", DLookup("EntityName", "Administration", "AdminID =1")

 
     TempVars.Add "gstrEntityWWW", DLookup("Website", "Administration", "AdminID =1")

 End Sub


You can use this in a control source


=TempVars!gstrEntityName
 
Upvote 0
I don't know what happened to my original final reply so ...
TempVars works well. much quicker than the getter method which takes almost one second to display the relevant text box. TempVars takes no discernible time.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top