How to set up a global variable to use within various modules?

[CarrieR]

I apologize in advance - I know this is a basic question but I absolutely can't find a way for this to work.

Currently I have a database I've made, with several processes of importing files, modifying the results, re-exporting files, etc. These are all done as part of a nearly-automated monthly process.

Most of these monthly processes pull from folder and file names with the numeric month in the name. So currently, I begin each function with:

Dim Month As String
Month = "09"

Then I use variable Month within various string command texts (TransferText, RunSQL, etc.). For example, making a new table for the month:

DoCmd.CopyObject , "Results_" & Month & "2010", acTable, "Results_EMPTY"

I know there's a way to set up one global variable called glbMonth, in one module, that I call to in all of these other modules which I currently begin with the 2 lines above. However, I haven't gotten any of these to work.

How do I set up this global variable module? More importantly, how do I call this variable within these functions, so I can continue to use it the way I have (as text)?

Thanks for your help!!


P.S. This is my own personal database to make a monthly process automated, and having read the cons of global variables, I believe it will work for me in this minor use case.

[ADezii]

First of all, it is never a good idea to Name a Variable Month since it is an Intrinsic Access Function (Month()). That being said, to Declare a Variable Globally, you must Declare it in a Standard Code Module as Public:

Public gstrMonth As String

where g denotes its Scope (Global/Public) and str (String) its Data Type.

Once the Varibale is initialized, you may use it as you previously indicated:

DoCmd.CopyObject , "Results_" & gstrMonth & "2010", acTable, "Results_EMPTY"

It will be 'Visible' anywhere within you Application, since it is Public.

[CarrieR]

That sets it as a variable, but I need to define it, so it is defined the same way in every module that calls it (that is, setting it = "10", for example).

So I wrote:

Public gstrMonth As String
        gstrMonth = "10"

in a module SetGlobals. In one of the many modules that reference it, I simply used "gstrMonth" instead of "Month" as you said. This is exactly where I have gotten tripped up before.

Error message is "Compile error: Invalid Outside Procedure." It marks this at the "10" in the Global Variables Modules.

So my question is, how can I get this to work so not only is the global var created, but it is set to "10" (this month) in ONE place, and all of the modules that call it now know that it = "10"?

[CarrieR]

I ended up solving my issue through a lot of trial and error. Here was my solution:

I set up new module SetGlobalVariab les with the following code:

Option Compare Database

Public gstrMonth As String

Public Function SetMonthValue()
gstrMonth = "10"
End Function

Then within one of the many modules that needed to call this variable, where I used to have:

Dim Month As String
Month = "10"

I replaced it with:

Call SetMonthValue

This did what I needed, and solved my problem.

[ADezii]

There is no need to Call a Function in order to Define a Public Variable. Since it is Public/Global, you can Initialize/Define it anywhere within your Application. A good place may be the Open() Event of your Main Form, as in:

Private Sub Form_Open(Cancel As Integer)
  gstrMonth = "10"
End Sub

[CarrieR]

But I actually need to define it globally. (For the purposes of this DB that only I use, this is what I need.)

I don't use any forms. This is all within modules.

[ADezii]

You can then create an AutoExec Mcro that Calls the SetMonthValue() Function, where gstrMonth is initialized.

[CarrieR]

Thanks, but unless there's some reason I shouldn't be doing it the way that I did, that works perfectly for what I needed.

If there is a reason, let me know, but otherwise, it works well!

[ADezii]

If it ain't broke, then don't fix it! (LOL).