none
Access - Global Variable. Code placement.

    Question

  • Where can I use:

    Dim boolVar1 as Boolean

    Where any Form can see it?

    I have a 'mainmenu' that opens I thought about putting it here:

    Option Compare Database

    *Dim boolVar1 as Boolean

    Private Sub ()

    But I'm not sure that would be able to be seen by other forms.

    What I am trying to do:

    Look up permissions and set variables to yes/no depending on permission; then on other forms set the controls to visible if the user has permission. I have many Boolean vars to customize the permissions for each computer.


    Access 2010

    Thursday, July 11, 2013 7:12 PM

Answers

  • The first thought that occurs to me is probably the crudest suggestion, so expect better answers. I would put whatever variables you are using for "control" in a table. Make the apropos fields of that table available where necessary in say, invisible checkboxes/radio buttons. That’s probably pretty tedious, too, eh.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    • Marked as answer by UpTide Monday, July 15, 2013 8:18 PM
    Thursday, July 11, 2013 7:38 PM
  • Opening a recordset is more efficient than Dlookups. DLookups have their place, but I would not consider it for something like this.

    Global variables can loose their values (unhandled errors, etc.), especially if not using an accde.

    If you really want to do it that way, then I would suggest an array, populated via a recordset.

    Make all of your calls through a procedure that first tests if the array has any elements, and if not, loads from the recordset. That way, should your global array variable lose its content, it will automatically reload.

    • Marked as answer by UpTide Monday, July 15, 2013 8:18 PM
    Thursday, July 11, 2013 9:11 PM
  • This was kind of suggested earlier -- except the poster said to store the variable(s) -- No -- store the desired value in a table and then reference that value throughout your app as needed.  Then you can update that value or remove it as necessary.  Global vars are kind of a risky thing -- and by Global I mean application wide.  They are there, but it is just too easy for a global var to either pick up an undesired value or for the value to be used unexpectedly.   It is more reliable to read a desired value from a table -- as needed.

    If what you want is a Form_wide global var or a code module_wide global var, that isn't as risky as an application_wide global var.  It is a common practice to declare a Form level or Code Module level var.  All you have to do is declare the var within the Form code module or Code module at the top of the module outside of any subs.

    Dim somVar As Variant

    Sub Thing1()

      someVar =  ...

    End Sub

    Sub Thing2() 

      Dim someT As Variant

      someT = someVar

       ...

    End Sub

    For application_wide vars -- I would store a value in a table instead.


    Rich P


    • Edited by Rich P123 Friday, July 12, 2013 3:25 PM .....
    • Marked as answer by UpTide Monday, July 15, 2013 8:18 PM
    Friday, July 12, 2013 3:21 PM

All replies

  • The first thought that occurs to me is probably the crudest suggestion, so expect better answers. I would put whatever variables you are using for "control" in a table. Make the apropos fields of that table available where necessary in say, invisible checkboxes/radio buttons. That’s probably pretty tedious, too, eh.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    • Marked as answer by UpTide Monday, July 15, 2013 8:18 PM
    Thursday, July 11, 2013 7:38 PM
  • Well I have the computer names and their permission bools stored in a table. I would like to declare those as variables and assign the value with a Dlookup. Then in my form's onload() I could do something like this:

    'Allow user to see money made
    If boolVar1 Then
    Me.Textbox1.Visibility = True
    End If
    'Allow user to see phone numbers
    If boolVar2 Then
    Me.Textbox2.Visibility = True
    End If
    [so on...]

    This way I don't have to use 5 Dlookups and find the computer name every time a user opens a form.

    I would like to know because I'm lazy and would rather just enter my code once. If it comes down to it I guess I could make it a function and just call it every time.. I just hope it won't make load times longer, as the table is stored on a shared network folder.


    Access 2010


    • Edited by UpTide Thursday, July 11, 2013 7:47 PM
    Thursday, July 11, 2013 7:45 PM
  • Where can I use:

    Dim boolVar1 as Boolean

    Where any Form can see it?

    Hi UpTide,

    If you declare your variable in a general module as

        Public boolVar1 as Boolean

    it is available all through your application.

    If that module is placed in a referenced library database, then it is even available in every application that refers to the library database.

    Imb.

     

    • Marked as answer by UpTide Thursday, July 11, 2013 8:01 PM
    • Unmarked as answer by UpTide Thursday, July 11, 2013 8:01 PM
    Thursday, July 11, 2013 7:53 PM
  • Thank you so much. This really helps. My old way of permissions was way messy. this will be much better. How can I publicly assign a value to the variable? If I assign it a value in  a Private sub1 and call the variable in the next sub2 will it be the same value as assigned in the first sub1?

    Access 2010


    • Edited by UpTide Thursday, July 11, 2013 8:03 PM
    Thursday, July 11, 2013 8:00 PM
  • Thank you so much. This really helps. My old way of permissions was way messy. this will be much better. How can I publicly assign a value to the variable? If I assign it a value in  a Private sub1 and call the variable in the next sub2 will it be the same value as assigned in the first sub1?

    Access 2010


    Hi UpTide,

    You may study the Scope of the variables.

    If a variable is declared within one Sub1, it is local to that Sub1. The same holds for "the same" variable in another Sub2. But because they are both local, the have nothing to do with each other.

    Imb.

     

    Thursday, July 11, 2013 8:21 PM
  • Ok so if I have an Access File that has two forms:

    Form1:

    Option Compare Database
    Public Var
    Var = "Set by Form1"
    Private Sub Form_Load()
       Var = "Set by Form1 Form_Load"
    End Sub


    Form2:

    Option Compare Database
    Private Sub Form_Load()
       Me.Textbox = Var
    End Sub

    Will Textbox contain:

    "Set by Form1"

    OR

    "Set by Form1 Form_Load"

    OR

    ""/null/not assigned

    Form1 Is opened. Then without closing Form1, Form2 is opened.


    Access 2010


    • Edited by UpTide Thursday, July 11, 2013 8:31 PM
    Thursday, July 11, 2013 8:30 PM
  • Trying to manage permissions strictly via code can get very messy, very fast. Plus any time the client wants to change someone's permission, they need to have you make code change.

    Use a table containing form name, control name, property and setting. The create a public function in a standard module with a parameter of type Access.Form.

    In the form's open event, you call the function, passing 'Me' as the argument.

    The function opens a recordset from the table using the form's name as criteria.

    Then loop through the records (if any) and set control properties accordingly.

    If you really want to do it well, add tables for users & groups. Permissions get assigned to groups and users belong to groups.
    Build forms for an administrator to manage the users, groups & permissions.

    Thursday, July 11, 2013 8:50 PM
  • I have a table which contains users and the permissions they have. What I want to set global vars which will be used by every form to determine the users permissions. Instead of Dlookup every single time a form opens it just compares with the variable.

    in table(permission table):

    user1 0 0 0 -1 -1

    user2 0 -1 0 0 0

    ... so on.

    I do no have to change code, all I have to do is change 0 to -1 to give permission, or -1 to 0 to take permission away. IF I can get the global variables (boolean) working.

    I have the slightest idea what I'm doing but I feel good about doing it this way, ignorance is bliss isn't it.


    Access 2010


    • Edited by UpTide Thursday, July 11, 2013 8:58 PM
    Thursday, July 11, 2013 8:57 PM
  • Opening a recordset is more efficient than Dlookups. DLookups have their place, but I would not consider it for something like this.

    Global variables can loose their values (unhandled errors, etc.), especially if not using an accde.

    If you really want to do it that way, then I would suggest an array, populated via a recordset.

    Make all of your calls through a procedure that first tests if the array has any elements, and if not, loads from the recordset. That way, should your global array variable lose its content, it will automatically reload.

    • Marked as answer by UpTide Monday, July 15, 2013 8:18 PM
    Thursday, July 11, 2013 9:11 PM
  • Thank you I will keep that in mind. How I am currently using the 'global variables' is as this:

    Main form (that can not be closed):

    form1:

    Public Var as Boolean
    Option Compare Database
    Private Sub Form_Load()
    Var = Dlookup()
    End Sub
    Option Compare Database
    Private Sub Form_Load()
    If Forms!Form1.Var Then
     Me.Button.Visible = True
    End If
    End Sub

    form2 ^

    Is this code clean? should I add error trapping?

    Should this still be done:

    Make all of your calls through a procedure that first tests if the array has any elements, and if not, loads from the recordset.

    Should I look into moving to: using an accde. ?

    Thank you all so much for your time.


    Access 2010

    Thursday, July 11, 2013 9:27 PM
  • Personally, I would not consider it clean and yes, I include error handling in just about all procedures. MzTools (http://www.mztools.com/index.aspx) is great for adding that. There are several factors to weigh regarding deploying your app as an accde.

    You obviously have a lot to learn. There has been much written on all three subjects and it would be pointless for me to try to summarize it all. I suggest that you utilize bingoogle. Here's a few links to get started.

    http://access.mvps.org/access/

    http://www.granite.ab.ca/access/tipsindex.htm

    http://www.kallal.ca/

    http://allenbrowne.com/tips.html

    Thursday, July 11, 2013 9:48 PM
  • Thank you!

    May I ask what is dirty about it?


    Access 2010


    • Edited by UpTide Friday, July 12, 2013 1:40 PM
    Friday, July 12, 2013 12:55 PM
  • Global varialbles....

    I just started using TempVars and I really like it.  You should look into that in the future.

    Friday, July 12, 2013 2:37 PM
  • This was kind of suggested earlier -- except the poster said to store the variable(s) -- No -- store the desired value in a table and then reference that value throughout your app as needed.  Then you can update that value or remove it as necessary.  Global vars are kind of a risky thing -- and by Global I mean application wide.  They are there, but it is just too easy for a global var to either pick up an undesired value or for the value to be used unexpectedly.   It is more reliable to read a desired value from a table -- as needed.

    If what you want is a Form_wide global var or a code module_wide global var, that isn't as risky as an application_wide global var.  It is a common practice to declare a Form level or Code Module level var.  All you have to do is declare the var within the Form code module or Code module at the top of the module outside of any subs.

    Dim somVar As Variant

    Sub Thing1()

      someVar =  ...

    End Sub

    Sub Thing2() 

      Dim someT As Variant

      someT = someVar

       ...

    End Sub

    For application_wide vars -- I would store a value in a table instead.


    Rich P


    • Edited by Rich P123 Friday, July 12, 2013 3:25 PM .....
    • Marked as answer by UpTide Monday, July 15, 2013 8:18 PM
    Friday, July 12, 2013 3:21 PM
  • I currently have it set where the main form can not be closed, but I think I need to add a check in my code.

    At form_open() sub I used "public var as boolean" and throughout my forms I am using:

    Forms![Formname].var

    and it is working perfectly the way I want to, but as:

    "Personally, I would not consider it clean and yes, I include error handling in just about all procedures."

    I need more work on it. I'm sure there are multiple ways to go about this, and I always seem to find the least efficient/clean way.


    Access 2010

    Friday, July 12, 2013 6:23 PM
  • Uptide,

    Dont take anything personally.  There are so many "Knowledgeable"  people here that it's insane the amount the wisdom here.  I love it...and i just started a few months ago to actively be involved in this forum. 

    But i am glad you are making progress on your application.

    Friday, July 12, 2013 6:51 PM
  • I currently have it set where the main form can not be closed, but I think I need to add a check in my code.

    1.  How does the database identify the user? Do you use some kind of LogIn Form? If you do, what do you do with the LogIn Form once the user logged in an the database recognised the user?  How do hold the user identity once identified?

    2.  I should point out that Global/Public variables tend to reset to their default values if an unhandled error occurs in your database.  These default values are unlikely to be correct for subsequent operation of the the database.  Thus, if you use Public Variables for critical operations (in your case "security" - more on this later in point 4), then it is important to make sure that unhandled errors never occur in your database.  Alphonse is correct in recommended error-trapping in every procedure and this is even more critical if you want to use ACCDE and / or Access Run-time version.

    3.  Another way is to avoid storing these values in Public Variable.  Peter (first respondent) offerred the method of storing these values in (hidden) Controls in a Form that remains open during the database session.  Since you MainMenu Form cannot be closed, you can create these hidden Controls on the this Form and store these values as values of the hidden Controls.  The values of these Controls will be available to other Forms and they are not reset to their default values if an unhandled error occurs in your database.  

    In my case, I tend to store these values in hidden Controls on the Login Form. Once the user is identified, I can retrieve appropriate permissions and store the permissions in the hidden Controls on the LogIn Form and the make this Form invisible.  The values you need are still available as the values of hidden Controls on the hidden LogIn Form and these values are not reset by unhandled errors.

    4.  Finally, I should point out that this "security" measure is not completely secure.  You should think of the set-up as "user identification" and not "security".

     


    Van Dinh

    Saturday, July 13, 2013 12:58 PM
  • I was about to post a long reply explaining that global variables are very poor idea for several reasons, but then I saw Alphonse's reply where he carefully explained everything I was going to say.  So, all I'll say here is that you would be wise to follow his advice, even if you have to significantly expand your horizons to do it.  The time spent doing homework on the subject will be repaid many times over by the timed saved chasing problems in any other scheme.
    Saturday, July 13, 2013 3:29 PM
  • I was about to post a long reply explaining that global variables are very poor idea for several reasons, but then I saw Alphonse's reply where he carefully explained everything I was going to say.  So, all I'll say here is that you would be wise to follow his advice, even if you have to significantly expand your horizons to do it.  The time spent doing homework on the subject will be repaid many times over by the timed saved chasing problems in any other scheme.

    There was a time that I also was against any global variable.
    Working on structuring code (as necessary as structuring data), I experienced that there are situations where you can use global variables in a controlled way without the "unsuspected and disastrous" effects that globals sometimes can have, at the same time make use of the "power" of global variables.

    So I have become a little milder.

    For the rest I fully agree with the other contributors of this thread.

    Imb.

     

    Saturday, July 13, 2013 7:11 PM
  • Ok so if I have an Access File that has two forms:

    Form1:

    Option Compare Database
    Public Var
    Var = "Set by Form1"
    Private Sub Form_Load()
       Var = "Set by Form1 Form_Load"
    End Sub


    Form2:

    Option Compare Database
    Private Sub Form_Load()
       Me.Textbox = Var
    End Sub

    Will Textbox contain:

    "Set by Form1"

    OR

    "Set by Form1 Form_Load"

    OR

    ""/null/not assigned

    Form1 Is opened. Then without closing Form1, Form2 is opened.

    Without entering into the more general discussion of the use of Public variables, I want to point out that the above code won't work as intended, because the public variable Var is not defined in a *standard* module, but rather in a form's class module. If you want the variable to be visible throughout your application without any qualification, the variable must be defined ...

    • in a standard module (not a form, report, or other class module), and
    • at module level (not within any procedure in the module), and
    • Public.

    By contrast, if a Public variable is defined at the module level with in a form or report module (which are types of class module), then it can be seen from outside the module, but only when qualified by a reference to a specific class instance. For example, if Var is defined as in the code you posted, then the reference from within Form2 would have to be like this:

        Me.Textbox = Forms!Form1.Var

    (and of course,  Form1 would have to be open at the time.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, July 14, 2013 1:01 AM
  • I would expect that with such a simple case, you would build a model and tell US how it works!

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, July 15, 2013 1:49 AM
  • I would expect that with such a simple case, you would build a model and tell US how it works!

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Not a viable option. To cover this, the work would be a 100 to 500 page document for just the "Global/Public Variable" chapter. Unless someone is paid for doing this work.

    Dirk Goldgar had covered in some areas which is sufficient enough. His method however needs error handling, just that we assumed that you know this.

    There is another method without error handling in VBA but in some methods like ControlSource, you need to avoid using it.

    Monday, July 15, 2013 4:00 AM
  • Van there is no security. The global var stores information about the ODBC drivers installed on the computer. If it has the driver it 'unlocks' what forms you can see.

    Access 2010

    Monday, July 15, 2013 12:34 PM
  • Van there is no security. The global var stores information about the ODBC drivers installed on the computer. If it has the driver it 'unlocks' what forms you can see.

    OK... You don't have to worry about point 4 in my reply.  The first 3 points in my reply still stand independently of point 4.

     


    Van Dinh

    Monday, July 15, 2013 1:41 PM
  • 1. it identifies the computer with:

    strCompName = """" & Environ("COMPUTERNAME") & """"

    (the quotes are so I can use it in Dlookup.

    2. understood, because we use runtime it crashes if I do not error tap. So I error trap.

    3. So from this I understand that the best way to go about this is to on open on the DB file (front end) a 'storage' form opens which has many hidden textboxes/thelike. This storage form then opens the main menu and then hides itself. Is this halfway sort of what you are talking about? Maybe this could be changed where instead of looking up the computer the user can enter a name into the textbox(later) and use that instead?

    Thanks for your help Van I hope this helps us understand each other.


    Access 2010

    Monday, July 15, 2013 2:10 PM
  • @AccessVandal - sorry, i was attempting to reply to UpTide's case of "two forms, two subroutines".

    Henceforth. i need to figure a way to reply to a specific post, so i don't tangle the threads ... the date works for old posts: for example:

    "Thursday, July 11, 2013 8:30 PM" works in referring to Uptide's post

    "10 hours 3 minutes ago" is less useful in referring to yours

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, July 15, 2013 2:12 PM
  • Trying to manage permissions strictly via code can get very messy, very fast. Plus any time the client wants to change someone's permission, they need to have you make code change.

    Use a table containing form name, control name, property and setting. The create a public function in a standard module with a parameter of type Access.Form.

    In the form's open event, you call the function, passing 'Me' as the argument.

    The function opens a recordset from the table using the form's name as criteria.

    Then loop through the records (if any) and set control properties accordingly.

    If you really want to do it well, add tables for users & groups. Permissions get assigned to groups and users belong to groups.
    Build forms for an administrator to manage the users, groups & permissions.

    ========================================================

    Any way you could send me instructions on how to do this. FYI I don't know what you mean with these points:

    Then loop through the records (if any) and set control properties accordingly. - A loop? Records for what? control properties of what?

    The function opens a recordset from the table using the form's name as criteria. - What is a recordset? form's name as criteria, so instead of looking at variables it will look up its name in this 'recordset'??

    In the form's open event, you call the function, passing 'Me' as the argument. - That sounds fun, how can I pass an argument to a function?

    standard module with a parameter of type Access.Form. - What is a standard module and where are its parameters? I figured out how to make the module and put a public function in it, but the parameters?

    As you can probably tell, I am not studied at all on this. Everything I know came from you guys.

    Thanks again. Feel proud.


    Access 2010



    • Edited by UpTide Monday, July 15, 2013 5:51 PM
    Monday, July 15, 2013 5:43 PM
  • Don't fight guys! This is a place of learning.

    Thanks everyone for your help!


    Access 2010

    Monday, July 15, 2013 5:46 PM
  • Sorry, but without you knowing the basics of VBA, DAO, controls, properites, etc., you need more help than I have time to provide.
    Monday, July 15, 2013 5:56 PM
  • Got it.

    Access 2010

    Monday, July 15, 2013 8:17 PM
  • 3. So from this I understand that the best way to go about this is to on open on the DB file (front end) a 'storage' form opens which has many hidden textboxes/thelike. This storage form then opens the main menu and then hides itself. Is this halfway sort of what you are talking about? Maybe this could be changed where instead of looking up the computer the user can enter a name into the textbox(later) and use that instead?

    You can do the above or if you want, simply have the hidden TextBoxes on your Main Menu Form since you metioned earlier that this Form will stay opened for the whole Access/database session.


    Van Dinh

    Monday, July 15, 2013 10:37 PM
  • @AccessVandal - sorry, i was attempting to reply to UpTide's case of "two forms, two subroutines".

    Henceforth. i need to figure a way to reply to a specific post, so i don't tangle the threads ... the date works for old posts: for example:

    "Thursday, July 11, 2013 8:30 PM" works in referring to Uptide's post

    "10 hours 3 minutes ago" is less useful in referring to yours

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Peter,

    Ah....I see. Sorry about that.

    I had the impression that you was asking for the Instruction on "How to do this with a guide".

    Since you did not address whom you were asking or questioning without indicating the "Thread Stater or the Origin Poster" name, I had to assumed that you were asking for help.

    Tuesday, July 16, 2013 6:48 AM