Results 1 to 8 of 8

Global variables in Form Header gives #Name?

This is a discussion on Global variables in Form Header gives #Name? within the Microsoft Access forums, part of the Question Forums category; I thought this would be simple but ... Background I have a module mdlGlobal which contains Code: Public gstrEntityName As ...

  1. #1
    Board Regular revver's Avatar
    Join Date
    Dec 2007
    Location
    Brisbane Australia
    Posts
    235

    Default Global variables in Form Header gives #Name?

    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 by revver; Oct 10th, 2013 at 08:43 PM. Reason: Formatting
    Len
    _________________
    Office 2003 and 2010
    Windows XP Pro / Win 7

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default Re: Global variables in Form Header gives #Name?

    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()

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular revver's Avatar
    Join Date
    Dec 2007
    Location
    Brisbane Australia
    Posts
    235

    Default Re: Global variables in Form Header gives #Name?

    That worked, thanks.
    It seems clumsy to have to go to that sort of trouble. Why shouldn't global variables be global? !!
    Len
    _________________
    Office 2003 and 2010
    Windows XP Pro / Win 7

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default Re: Global variables in Form Header gives #Name?

    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 by xenou; Oct 11th, 2013 at 10:55 AM.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular HiTechCoach's Avatar
    Join Date
    Aug 2010
    Posts
    748

    Default Re: Global variables in Form Header gives #Name?

    @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
    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert
    Have you split yet?
    "If technology doesn't work for people, then it doesn't work."




  6. #6
    Board Regular revver's Avatar
    Join Date
    Dec 2007
    Location
    Brisbane Australia
    Posts
    235

    Default Re: Global variables in Form Header gives #Name?

    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.
    Len
    _________________
    Office 2003 and 2010
    Windows XP Pro / Win 7

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,146

    Default Re: Global variables in Form Header gives #Name?

    Thanks Boyd - that was it.

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  8. #8
    Board Regular HiTechCoach's Avatar
    Join Date
    Aug 2010
    Posts
    748

    Default Re: Global variables in Form Header gives #Name?

    Quote Originally Posted by xenou View Post
    Thanks Boyd - that was it.
    You're welcome.

    Glad I could assist.
    Boyd Trimmell aka HiTechCoach
    Microsoft MVP - Access Expert
    Have you split yet?
    "If technology doesn't work for people, then it doesn't work."




Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com