How to set a Public variable in a Macro?

[Gary Schuldt]

I want to set a global variable equal to a value in a single-column unbound
combobox.

Here's the code I have:

OnExit from combo, I call this macro, which contains the single statement:

SetValue [Modules]![Module1]![pubstrCurrentGarden].[Value] = [combo]

I have the statement

Public pubstrCurrentGarden As String

In the General Declarations of Module1.

I have tried all sorts of syntaxes for the Item part of the SetValue action,
ranging from just pubstrCurrentGarden all the way up to what you see.

If it's even possible to set a global variable in a macro, please tell me
how!

Thanks.

Gary

 

[Steve Schapel]

Gary,

No it is not possible.

Depending on how you are trying to use this "variable", there are many
situations in your database operations, for example in queries and on
forms and reports, and within VBA procedures, where you can refer
directly to the value of the combobox.

 

[Gary Schuldt]

Steve,

Are you saying it is not possible to refer to a Public "variable" in a
macro?

I am using the form with the combo box to set a parameter that will be
tested in more than one place after the form has been closed. That's why I
chose the Public route. If there is a better way, please point me to it.

OK, so I am still faced with the "easiest" way to set this pubVar. (I'm a
newbie, and this is my first experience with a Public variable.)

My next tactic is to use an Exit Event Procedure for the combobox. However,
it looks like I cannot declare pubVar in the Procedure itself but must do it
in the form Module. Progress is slow . . .

Gary

Gary,

No it is not possible.

Depending on how you are trying to use this "variable", there are many
situations in your database operations, for example in queries and on
forms and reports, and within VBA procedures, where you can refer
directly to the value of the combobox.

--
Steve Schapel, Microsoft Access MVP

I want to set a global variable equal to a value in a single-column unbound
combobox.

Here's the code I have:

OnExit from combo, I call this macro, which contains the single statement:

SetValue [Modules]![Module1]![pubstrCurrentGarden].[Value] = [combo]

I have the statement

Public pubstrCurrentGarden As String

In the General Declarations of Module1.

I have tried all sorts of syntaxes for the Item part of the SetValue action,
ranging from just pubstrCurrentGarden all the way up to what you see.

If it's even possible to set a global variable in a macro, please tell me
how!

Thanks.

Gary

 

[Steve Schapel]

Gary,

I was referring to assigning the value to a global variable using a
SetValue macro. You can refer to a global variable in various macro
conditions or arguments if you have set up a public function to return
the value of the variable... but I don't think this is what you're asking.

And no, as far as I know, a global variable can't be declared in a form
module. It has to be in a public module. For example, you could simply
put this in any public module...
Global pubVar As String

Then, in any event in the form where your combobox is, you can put...
pubVar = Me.NameOfYourCombobox
Possibly the AfterUpdate event of the combobox would be the most likely
event in practice.

Then, after the form is closed, the value that was in the combobox will
be retained in the pubVar variable, and can then be used within any VBA
procedure within the database.

 

[Gary Schuldt]

Steve,

thanks, that helps.

I found that, if you're in the VB code window for a form, if you click on
General, you'll get the Declarations section displayed, where you have to
put the

Public pubVar As String (if you're going to put it in the form module at
all)

I wasn't sure whether to make the procedure an AfterUpdate or Exit event for
that control, so I chose the latter, but for no particular reason except
that, since it's an unbound control, I was thinking that "nothing" is really
being updated. Fuzzy thinking, probably . . . but . . .

I got it to work!! And that's what counts.

Gary

 

[Steve Schapel]

Gary,

As I mentioned before, the public/global variable should not be declared
in the form module... it won't work.

 

[Gary Schuldt]

Steve,

<ruefully> yes, as I just discovered. I misinterpreted a statement in
Prague & Irwin's book, which said something like "you can declare a public
variable in any module, but it only makes sense to declare them in the
modules that use them."

It goes on to say (which I didn't see!) that "the only exception is true
global variables . . . which should only be declared in one standard
module"! OK, so Public is not the same as true Global. Sigh.

If you do it in more than one, they end up being different variables, I
guess, even though the declarations are identical in every respect.

re: "one standard module"
I understand the "one" part, but not sure I know what they mean by
"standard".

Gary

 

[Steve Schapel]

Gary,

"Standard module" just means a module created from the Modules tab of
the database window, as distinct from an object class module which is a
form or report module. Just make a new module, and then type your
variable declaration in there...
Global pubVar As String

Then, you will be able to assign a value to this variable from anywhere
within any other VBA procedure in the database.

Isn't learning new stuff exciting, eh?

 

[Gary Schuldt]

Steve,

ok, what you say about "standard module" makes sense to me. I have only one
of those--Module1, I guess the default name--in my database. But I guess
you could have as many as you wanted, depending on logical grouping
considerations.

re: Global pubVar As String

I have been using the Public statement to declare the "global" variables. I
can't find anything in Help about a Global statement . . . I'm running
Access 2K.

Yes, learning is exciting (I'm a teacher by profession), but there are times
you "just want to get the job done"! Believe it or not, I remember when MS
Access was first released, and friends of mine who were techno-challenged
were raving about how EASY it was to created their own databases! I can't
say that I've found that to be the case. So thank the heavens above for
MVP's like you!

Gary

 

[Steve Schapel]

Gary,

I have been using Access since Version 1. I have never met anyone who
has not found it a pretty steep learning curve. Thant's just the nature
of the beast... not Access specifically, but relational data management
work.

Following your message, I looked in Help, and see also that there is no
reference to the Global keyword. It works fine, and I don't know why I
started using it, but it is equivalent to the Public keyword as you have
been using. You can use either.

 

[Gary Schuldt]

Steve,

thanks for the enlightenment about the Global keyword (or is it a
"statement" like Help says Public is?). I think I prefer "Global".

re: relational data management

Your comment interested me, since I've been making my living teaching
teaching relational data management (among other things) for almost 25
years. Before that I was a software engineer. I don't feel that the
relational part accounts for the steep learning curve in Access; there is a
lot of literature on SQL and the relational model around. For me, it's
mostly programming the #$%^ user interface--the forms, controls, VBA and
macros!

Gary

 

[Gary Schuldt]

Steve,

thanks for the enlightenment about the Global keyword (or is it a
"statement" like Help says Public is?). I think I prefer "Global".

re: relational data management

Your comment interested me, since I've been making my living teaching
teaching relational data management (among other things) for almost 25
years. Before that I was a software engineer. I don't feel that the
relational part accounts for the steep learning curve in Access; there is a
lot of literature on SQL and the relational model around. For me, it's
mostly programming the #$%^ user interface--the forms, controls, VBA and
macros!

Gary

 

[Steve Schapel]

Gary,

Fair comments. I suppose because of your background, you are in a
different situation than most of the people I meet who are trying to
master Access. I was using the phrase "relational data management work"
to include the design and construction of the user interface, which to
me is an integral part of application development. I can't directly
comment, because I have not used other development tools, but I have
certainly seen statements from many experienced Access developers to the
effect that the speed and ease of building the user interface, whilst
sometimes difficult, compares favourably with trying to do so using
other tools.

One factor is the Help system, which Microsoft have apparently been
having challenges with. Many developers, including myself, keep Access
97 installed so we can use a decent Help system, because the Access 2000
and Access 2002 Help are so difficult to find what you want. The Help
in Access 2003 is improved. Nevertheless, there's no substitute for a
good book. In that regard, I have recently got Access 2003 Inside Out
by John Viescas, which is an excellent general-purpose tome.