Reference to a variable in a query

[Bob Howard]

My database has a module in which I keep several global variables and I have
no problem referring to them in any of the forms or reports. But there's
one that I now find I would like to refer to in a query and cannot determine
the right protocol (or even if it's permitted). The variable is defined as
follows:

Public gblReportSequence As String

Is it possible to refer to a global variable in a query?

Is it possible to refer to a global variable within the RecordSource of a
form's control??

Thanks in advance!!!!

Bob (@Martureo.Org)

 

[Guest]

If you want to get a velue from a global variable to a query, you can create
a function that return the variable value
Function ReturnGlobalVar()
ReturnGlobalVar = GlobalVariable
End Function

You can call the function in the query

 

[Linda Burnside]

I don't think you can refer to the global variable in a query, but you could
use a hidden form which contains the value of the variable. Take the value
of the variable and update a textbox (the form and the textbox are not
visible). You can refer to the value of a textbox in a form in a query.

Linda

 

[Bob Howard]

Linda ---

I previously had that method implemented, but was advised that using
controls on hidden forms to pass global variables is not good coding "form."
So I converted all of the ones that had been implemented that way to global
variables in a module --- all except this one that I reference in a query.

In my application, the main form is always open. All forms (including the
main form, but beside the two that are dialogs) are non-modal popups.
Buttons on the main form take you to the different aspects (forms / reports)
of the application. So I have (had) a whole bunch of non-visible controls
on the main form used to pass data around.

It appears that you don't feel that the "hidden form" technique is "bad
coding form" --- but I've just spent lots of work converting away from it,
and would now like to finish the process.

Maybe I should have waited a bit longer before doing all that work! (-:

Bob.

 

[Paul Overway]

Why use a global variable at all? They're evil!

If you need to pass a variable to a query, you can set the SQL via code, or
pass the value as a parameter, i.e.,

Set db = CurrentDb()
Set qdf = db.QueryDefs("SomeQuery")

qdf.Parameters("someparameter")= someValue

qdf.Execute

You should try to limit the scope of variables as much as possible.

 

[Bob Howard]

I'm developing a "closed" application driven by a main form and buttons,
etc. --- the users have no access to access (hee-hee). Anyway, my main form
is sort of a "driver" for the entire application and all of the functions
within the application are hung off the main form. I definitely have need
for global variables so as to "generalize" the different functionf of the
application and not have to duplicate programs that do 99% the same
processing. I liberally use global variables to alter the course of
processing, affect the query results, determine the report sequence, etc.
So the presence of global variables is critical to this application as it is
currently designed.

I'm not sure why you feel they are so evil --- I've found them to be great!
One of my design criteria is to have a total application package
(documentation and all) which, along with the MDE, will ZIP to 4 megs or
less (to be able to download is on a 56k line in 30 minutes or less).

If I had to duplicate all those forms, reports, VBA codes, etc., I would be
way over 4 meg by now. Some of my forms perform a combination of Input,
Update and Browse processing from three buttons on the main form. The forms
and the VBA behind them are only there once, and one a few times during the
processing do I really care which of the three modes I'm running in. A
global variable is set to direct this.

Etc. Etc. throughout the application.

So as far as global variables are concerned, I love them!

Bob.

 

[Linda Burnside]

I would agree that it's kind of clumsy, and probably not the best option in
most situations.

I like Ofer's suggestion -- will have to test that out sometime.

Linda

 

[Paul Overway]

No offense, but most people that use global variables write spaghetti. The
biggest issue with globals is that they're difficult to follow and
troubleshoot. There's no getting around that. Because of their global
nature, the variable may be assigned a different value from somewhere
unexpected. Try sloughing through 40000 lines of code trying to isolate
where a variable might have received an errant value. Either you're asking
for headaches....or you're planning to leave one for someone else.

Your concerns about size and duplication suggest to me that the app isn't
well designed. There are any number of ways to pass variables, i.e.,
classes, form properties, properly written functions and subs, etc.. ...that
have minimal impact on size and duplication. In fact, if you design your
app properly, you'll be able to re-use a lot of code in future apps.

In the past 10 years, I think the maximum number of globals I've used in an
app is 2....and those were for application level objects like Outlook, Word,
or Excel.