Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating & Using a Public variable (1)

    Athough I am new to access I have experience using other relational databases. I need help on a project that I am working on.
    I want to create a variable that will be initialized by a user selection and then be passed on to different forms.

    As a test I used a list box(filled in by the table1 field ABC) and adjusted the properties so that when clicked the following;

    Public sub apple_click()
    Dim XYZ
    XYZ=ABC <--- ABC is a field in a table
    end sub
    On the same form I created a text box to see the value of XYZ
    I could not get this text box to display the variable value. It only had #name?.

    Does anyone have a suggestion on what I am doing wrong?

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating & Using a Public variable (1)

    Including the Dim statement inside the apple_click procedure makes it local to that procedure only. Since you need XYZ to be available throughout the application once the user sets its value, you can either declare XYZ as a Public variable in a separate module (making it global) or declare it as a Public variable within your form and keep the form loaded (but not visible) at all times. Public variables on a form need to be declared in the General Declarations section rather than within a Sub and will need to be prefixed with the form's name when referenced from another form. e.g. txtValue.text = frmApple.XYZ. Using the form to hold the variable (rather than a global in a separate module) allows you to limit where and how the value of the public variable gets changed.

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating & Using a Public variable (1)

    Roger,
    Sorry, I just realized I had some of the syntax wrong in what I just posted. I have been doing more VB than Access/VBA and was mixing things up a bit! In Access when you are referring to another form by name you have to use Forms! in front of the name, e.g. Forms!frmApple.XYZ, the Forms collection will only contain forms that are currently loaded, even if not visible. Probably some of the other people in this forum will have better ideas on which is preferable in this situation: a global variable in a module, or a public form-level variable in a form that stays loaded. Sorry for the error.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating & Using a Public variable (1)

    You cannot reference a variable as the controlsource for a control. You can only return the value of a variable in a function and use that function as the controlsource. Global variables are only useful within code and since they can be changed from anywhere in an application, they are highly prone to getting stepped on if you aren't extremely careful in your coding.
    Charlotte

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Creating & Using a Public variable (1)

    You didn't get specific on how you wanted to use the global variable, but I'll comment on it.

    For one thing, for all forms to see it it must be declared in the declarations area of a module, not within a procedure. The problem with global variables is that they can get lose their value in some circumstances (such as an untrapped error). I find it better to use a hidden form that contains a control with the data I want. Then I can always refer to it by using: Forms!myhiddenformname!controlname.

    And while I'm on a roll, you should be more specific in your variable declarations. Merely using "Dim XYZ" will declare XYZ as a variant. This is rather inefficient, and can actually impede performance, as each time Access has to use this variable it has to figure out how to use it based on the circumstances. Better to use "Dim XYZ as String", etc.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    Jul 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating & Using a Public variable (1)

    Charlotte
    Thanks, I was trying to use the variable as the control source.
    Would I put a simple function in the control source. For example, I tried Eval(XYZ) and it still gives me #Name?.
    I'm still missing something.

    A little more detail.
    I'm trying to have the user click a parent record after logging in. Based upon this record, I set a variable(s) that will be used on a header to be included on all following forms. Right now I'm just testing on a single form.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating & Using a Public variable (1)

    You have to create a function like this:

    Public Function MyFunction() As String
    MyFunction = XYZ 'the name of the global variable
    End Function

    set the controlsource in the propertysheet like this:

    =MyFunction()

    I would heed Mark's warning about global variables though. I avoid them myself because of their breakable nature and the limited scope of "global" in Access.
    Charlotte

  8. #8
    New Lounger
    Join Date
    Jul 2002
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating & Using a Public variable (1)

    Thanks - for everyones help
    Based on the warning on global variables, I may rethink, and save the user selection in a table

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating & Using a Public variable (1)

    You will still need a public function to retrieve the value for use in queries and as controlsources. A trick to speed that process up is to use a static variable in your function. Lookup the value in the table when the static variable is not populated. Otherwise, just return the value of the static variable.
    Charlotte

  10. #10
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    vancouver, BC, Br. Columbia
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating & Using a Public variable (1)

    Charlotte when you say "use a static variable", do you mean something like:

    Public Function MyFunction() As String
    Static XYZ
    If nz(XYZ) = "" then
    'code to lookup the value from the table and put it into XYZ
    end if
    MyFunction = XYZ 'the name of the global variable
    End Function

    set the controlsource in the propertysheet like this:

    =MyFunction()

    Thanks ... Gwenda
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Creating & Using a Public variable (1)

    As test I

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Property procedures

    I use public properties for some things, but they aren't really much different from passing an value in any other way. Once they become public, they can be changed from pretty much anywhere in the app. The big advantage with using a Let/Get procedure is that you can include code in it to validate the value and make sure it meets whatever rules you have set for it. I've had some problems with custom form properties when trying to refer to them in a query, but they have always worked reliably in code. On forms, I tend to use Property Get procedures without a Let so that the values can be read from elsewhere but only changed from inside the form. That allows the data to be retrieved freely and eliminates the need to push it out to other objects. With reports, I tend to just use public variables rather than property procedures to enable custom report properties because I usually do want to allow other objects and routines to set the values. For instance, I frequently use a CalledFrom property on both forms and reports and populate it with the name of the object (form, menu,etc) that I want to return to when the current one closes. That one works well as either a procedure or a variable since only one object at a time can open an instance of a form or report.

    Actually, you still aren't seeing them discussed outside the context of class modules. Form and report modules *are* class modules, remember? And only classes can have Property procedures.
    Charlotte

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Creating & Using a Public variable (1)

    Exactly. The static variable only needs to be populated when it is null or uninitialized. That means you don't pay a huge price for using even something as slow as a DLookup.
    Charlotte

  14. #14
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Property procedures

    Thanx for comments. I can't prove it but I did know form modules are class modules, really... I guess I was thinking of class modules in terms of those you define in their entirety as opposed to those derived from an existing class such as Access Form.

Posting Permissions

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