Help With Global/Public Variables

[SimonB]

I want to refer to a global variable from multiple modules in Access VBA code. In reading the "books" it would appear that a "Public" variable should serve this purpose, but I can't seem to get it to be recognized beyond the module in which I declare it. How do I declare it as soon as the database opens, and what do I need to do to have it be available to any procedure throughout the current session on the database, both for using its currant value, and for changing that value?

I have a similar issue with defining a constant to be used by the database any time it opens. I want the programmer to declare a value for a public/global loop limit that he might need to adjust depending on operational user feedback. The public/global variable allows it to be set and changed in only one central place rather than having to change it in every location where it is used.

I think these are both the same issue.

Any help would be appreciated.

Simon B.

[CreganTur]

Welcome, Simon- always good to see new faces!

Did you declare the Public varaiable in the Declarations section of your module? Also, you MUST declare it in a Module, not in the code behind a Form- Form code is local only to the Form that it's behind (it can interact with Globals and Subs/Functions declare in Modules, but the reverse is not true).

Regarding the Constant: you can declare the variable as Public Constant to give it global scope, and keep the value from changing.

HTH:thumb

[SimonB]

Randy, thanks for the quick reply.

What you say makes sense. I was declaring it in the top section of the Module (my terminology may not be accurate here) thus:

Option Compare Database
Public VariableName As String ' this is supposed to be the global variable
Option Explicit

But this is in the VBA behind a form, so that may be my problem. How do I trigger a Module that is not associated with a form, if that is where the declaration needs to go?

[CreganTur]

(my terminology may not be accurate here)

A module a collection of VBA procedures that is independant from a Form- this is a very, very basic description, but in this case it's important.


But this is in the VBA behind a form, so that may be my problem. How do I trigger a Module that is not associated with a form, if that is where the declaration needs to go?
The fact that you're putting it in the VBE behind a form is what's causing your problems.

On your database window, click Modules -> New; this will open a New VBE window. Declare your Global variables here- once you do this, they'll be available to all procedures in your database. The code behind your forms that use this Global variable will be able to interact with it.

[SimonB]

That sounds like it should work. I'll try it now. Many thanks. Can I assume that this module runs when the DB opens, and I don't have to do anything to trigger it like a procedure associated with an event does?

[CreganTur]

Can I assume that this module runs when the DB opens, and I don't have to do anything to trigger it like a procedure associated with an event does?

You don't have to do anything except reference the global variable like you would any other variable.

Now, if you have any procedures in the module, along with the variable, then you would need to call them from somewhere else.