I thought about this last night, and now I recall the logic. I have several
very complex queries that are used in multiple places in a the app: for
reports and for calculated controls (summary information at the bottom of
some input/lookup forms).
Due to the complexity of the information needed and the number of places
from which it is derived, these queries are stacked: Query1 is part of the
source of Query2 which is part of the source of Query3.
Query3 is used in several reports. These are run from a form that I have
that allows the users to select many criteria via combo boxes and then select
the report to run. One of the combo boxes provides the criteria for a Field1
in Query1.
Query1, however, is also used as the source of a calculated control on an
input/lookup form and needs to be filtered, also on Field1.
Thus, the criteria for Query1.Field1 could come from either of two forms,
and a single control is not available in the context of both instances in
which the query is called.
I have many (probably 10 or 15) of these on various queries/criteria
throughout the application, each involving the potential of being called from
at least two different forms, and I did not really want to create a unique
query for each context so that I could refer to the form in the criteria.
Better ideas?
Douglas J Steele said:
No, the only way you can refer to a variable in a query is through a
function such as you've done.
Wonder what your rationale was at the time for not using the control. The
control doesn't even have to be visible for it to work.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Brian said:
Yes. I do plan to implement that to replace my old method here. Every
time
I
make a mistake, someone opens my eyes to a better way of doing
something,
so
thanks again.
This brings up a related public-variable question (different app from the
original question, though). Some time ago, I tried to used a private variable
on a form as the criteria in a query and discovered that it did not
exist
in
the context of the query.
So, I made the variable public instead and had a function in a
non-form-related module to simply get the value of the public
variable.
Then,
in the query, I just call the function. Here are the three parts of the
scenario:
In the standalone module:
Public TestVar as Integer
Public Function GetTestVar As Integer()
GetTestVar = TestVar
End Function
Form:
TestVar = 3
Criteria in query where filtering to TestVar:
GetTestVar()
Am I missing some easier way to do this? I know I could use a hidden text
box called TestVar with the query's criteria as: [Forms]![MyForm]![TestVar],
but I think there was a reason I left that methodology behind also (can't
remember that part...)
:
I'd still recommend not using a public variable if you don't have to.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Oh, yikes. I just discovered that I made one of the oldest errors
in
the
book. I had started development of this app using an unbound text
box
on
my
form to hold the MessageCode value and switched to using the public
variable
without deleting the text box. With a control and a public
variable of
the
same name, no wonder it failed! Then when I posted the question, I
searched
through VBA, but forgot to run a Search & Replace, so I didn't
find
it.
That's two unexcusable error on my part.
FYI - it all worked once I removed the errant text box.
Thanks again.
:
Nothing jumps out as the culprit, but it would be better not to
rely
on
the
public variable, but instead to pass the variable to your sub:
Public Sub ErrorNotify(PassedCode As Integer)
MsgBox PassedCode
'stuff that e-mails me the message code goes here
End Sub
and call it
Call ErrorNotify(MessageCode)
Public variables should be used sparingly, as they're subject to being
reset
on errors.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
Oops - typed the variable names wrong. Here's the example using the
correct
MessageCode variable:
MsgBox MessageCode 'produces current value of ProcedureCurrent
ErrorNotify 'call the public sub
MsgBox MessageCode 'again, produces current value of ProcedureCurrent
Here is the public sub in Module #2
Public Sub ErrorNotify()
MsgBox MessageCode 'produces 0
'stuff that e-mails me the message code goes here
End Sub
:
I have a public variable declared in Module #1 (not a form
module)
and
used
in procedures on a form and in a public sub in Module #2 (also
non-form-related) as well . The value of the public variable
is
set in
the
form's module but does not carry through to the public sub. However,
when
the
public sub is done and the form's VBA continues, the value is there
again.
Three lines from the form's VBA look like this:
MsgBox ProcedureCurrent 'produces current value of ProcedureCurrent
ErrorNotify 'call the public sub
MsgBox ProcedureCurrent 'again, produces current value of
ProcedureCurrent
Here is the public sub in Module #2
Public Sub ErrorNotify()
MsgBox MessageCode 'produces 0
'stuff that e-mails me the message code goes her
End Sub
That is, the value is valid on both sides of the call to ErrorNotify,
but
not within it. I have verified that the variable is
dimensioned
only
once
in
Module #1, like this:
Option Explicit
Public MessageCode As Integer
Why is the variable only valid in certain contexts?
