Creating & Using a Public variable (1)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

As test I

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote