Results 1 to 12 of 12
  1. #1
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73

    Questions on Global variables

    I have 2 questions:
    Earlier versions of Access used to allow us to do a 'Dim something as Global' and that variable would be available in Forms/Subforms. Interestingly when I search for Global I really don't find a lot of help on it but I know it is still available. So the question is If I want to dimension something as Global where do I define it ie. if in VBA code where?

    2nd question - if I manage to define something as Global can I read into that variable something that comes from a DLookup. The idea is to read a bunch of variables out of a table into Global variables when the application loads so that they are available throughout the session.

    Any help appreciated.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    1. In a standard module. These days, many people use Public instead of Global but AFAIK these amount to the same.
    2. Yes
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You can also use TempVars to set values that are globally available.

  4. #4
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Thanks for you help - now a simple question I hope
    I have 2 modules in my application - the first is called Class1 (which I think was created when the db was created) and has nothing in it but Option Compare Database. The other is called Module 1 and the only thing in it is some code to determine who the user is by calling an Api to find the logged in user.
    So the fact that there are 2 modules means I have 2 options of where to place both the Global definition and the DLookup statements to populate them. Would it matter where I put these declarations - meaning in either module?
    Thanks for any more help!

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Re the first 2 questions. What you're asking about is called variable scope. IIRC, it's not enough to declare a variable Public in a standard module and expect it to be visible to the whole project - you have to do so in the right spot. While it's implied that this link is for Excel, the title suggest otherwise, plus I am certain the information applies to all of vba.
    https://support.microsoft.com/en-ca/...r-applications

    This link should also answer the latest question. Sounds to me like the class module is useless if there's nothing in it but "Option Compare Database" which leads me to point out that any project that doesn't include Option Explicit in every module is just asking for trouble. This should be turned on by default and added where it is missing.

    Project level scope is risky IMHO, and public variables cannot also be static. You have to clearly understand the chances of processes possibly altering the value of a public variable and what could happen if it occurs. It might, for example, be very bad in a financial application. Depending on the application, table records might be more secure/reliable.
    Last edited by Micron; 11-21-2018 at 05:04 PM. Reason: wrong link
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Thank you for your comment.
    I note your indication that table records might be better - which is the way I have been doing it - using DLookup every time I need the variable in question - which really rarely if ever changes.
    That being said I have run into yet another issue - I remember in earlier Access the declaration would of been Dim something As Global - when I try to use this statement (anywhere) Access informs me it is looking for either New or Type (ie string etc) YET I am able to select Global out of the drop down list that appears after As - very weird. The reason I wanted to change this was just to save on coding and db application size - DLookup works fine but is required in almost every load of a form.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree with Micron - delete the class module, always Option Explicit etc

    Some developers avoid the use of Public variables. I do use them but sparingly.
    Some use Tempvars widely. Others including myself don't use them at all

    You can create new standard modules as necessary (or class modules but they have a different purpose)
    I create separate modules for all code related to a specific area e.g. modEmail, modSecurity, modRegistry etc etc so in large apps I may have 50-100 modules
    One of those is called modDefinitions where I place all Public variable definitions and nothing else
    The code which uses them will be located in other modules

    However if you prefer you can place Public variables in the declarations section at the top of each related module where the code is found

    As for the code to get the logged in use, there are 3 methods using Environ, WScript and a Windows API
    Environ is the simplest but can be 'spoofed', WScript is reliable as is the API but the latter method is more complex and needs modifying for 64-bit Access
    For more info on each method, see this page on my website: http://www.mendipdatasystems.co.uk/g...ame/4594424315

    EDIT:
    Which version are you referring to when you wrote that old Access versions accepted
    Dim something as Global
    .
    I've used Access for 20 years and I don't recall that EVER being used.
    In fact I've just tested in Access 2.0, 95 & 97 (all of which I have on VMs).
    As i expected, none of them accepted either Dim strText As Global or Dim strText as String As Global. Both shown in RED as here
    I do also have every version of Access since those but i'm fairly sure it won't work in any

    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I was going to imply that you haven't reviewed the information at the link I provided but for some reason, decided to test it first. I copied from the wrong browser tab and have corrected that. Pretty sure it addresses how to write the declaration and where to put it depending on the need. In short, what you want isn't entirely governed by declaring something Public or Private - it also depends on where it's located. It makes more sense to me to provide a link to the info rather than repeat it here and I think you'll agree when you see the "scope" of the subject.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Dim (or Static) - defined in procedure - local variable applied to that procedure only
    Dim (or Private) - defined at module level in declarations section - applies to all procedures in that module only
    Global or Public - defined in standard module (NOT a form/report class module) in declarations section - applies globally in all code modules of the application

    Therefore whilst Dim/Private/Static have to be defined where they are going to be used, Public/Global can be defined in any standard module
    So I can keep track of them I prefer to have them all defined in one module used for no other purpose
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    Thanks for your very detailed answer. At this point I think I will just use the methodology I have been using - DLookup where required. It's not elegant to say the least but it doesn't give me any problems.
    I also suspect that the problem I was having was based on using the statement incorrectly. For some reason in my mind I thought I remembered that the statement would be Dim 'something' as Global however based on your answer and others I think the correct syntax would be -Global 'something' as string etc - perhaps that is the reason Access didn't like my statement. Thanks again - appreciate all help!

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    DLookup is fine for many purposes but is VERY SLOW if used in a query field. Avoid in that situation
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    73
    I think I found that out the hard way a little while ago - couldn’t understand why the darned form was so slow to load so I did it a different way - still not elegant but got the job done!
    Thanks again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Global Variables
    By Homegrownandy in forum Programming
    Replies: 4
    Last Post: 09-03-2018, 06:23 AM
  2. VBA is reseting all my global variables...
    By ohmydatabase in forum Access
    Replies: 7
    Last Post: 10-14-2017, 03:02 AM
  3. Cannot create Global Variables
    By Paul H in forum Programming
    Replies: 3
    Last Post: 05-20-2014, 11:27 AM
  4. Global Variables?
    By futurezach in forum Reports
    Replies: 4
    Last Post: 06-20-2013, 03:45 PM
  5. Setting global variables
    By Remster in forum Programming
    Replies: 1
    Last Post: 08-24-2011, 08:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums