referencing a global variable in the RowSource property

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

BobRoyAce

I have a combo box on a form whose RowSource is as follows:

PARAMETERS gsSelectedTeams Text; SELECT DISTINCTROW [Line Names].Line FROM
[Line Names] WHERE ((([Line Names].Team)=gsSelectedTeams));

What I am attempting to do here is to have a query that references a global
variable, namely gsSelectedTeams (defined in a Module in the database and
populated in a login form). It makes sense to me that the syntax is not
correct (I get prompted to enter a parameter value for gsSelectedTeams).
However, I do not know how to do this (correct syntax). Any help would be
appreciated.
 
BobRoyAce said:
I have a combo box on a form whose RowSource is as follows:

PARAMETERS gsSelectedTeams Text; SELECT DISTINCTROW [Line Names].Line
FROM [Line Names] WHERE ((([Line Names].Team)=gsSelectedTeams));

What I am attempting to do here is to have a query that references a
global variable, namely gsSelectedTeams (defined in a Module in the
database and populated in a login form). It makes sense to me that
the syntax is not correct (I get prompted to enter a parameter value
for gsSelectedTeams). However, I do not know how to do this (correct
syntax). Any help would be appreciated.
Click to expand...

You can't reference the variable directly, because the database engine
doesn't recognize VBA variables, even global ones. What you can do is
write a public function in a standard module -- most reasonably, the one
in which the variable is defined -- that takes no arguments and simply
returns the value of the varable. For example ...

'----- start of module -----
Option Compare Database
Option Explicit

Public gsSelectedTeams As String

Public Function fnSelectedTeams() As String
fnSelectedTeams = gsSelectedTeams
End Function
'----- end of (relevant part of) module -----

Then your query could be:

SELECT DISTINCTROW [Line Names].Line
FROM [Line Names]
WHERE [Line Names].Team=fnSelectedTeams();
 
Thanks Dirk...I tried your suggestion, adding a function to the module where
the global variable is defined and changed the RowSource to:

SELECT DISTINCTROW [Line Names].Line
FROM [Line Names]
WHERE [Line Names].Team=fnSelectedTeams();

SELECT DISTINCTROW [Line Names].Line
FROM [Line Names]
WHERE ([Line Names].Team=GetCurrentUserTeams);

This does not solve the problem as I still get a prompt asking for me to
enter a parameter, except this time the name of the parameter it is looking
for is the function name. Do I need to have a PARAMETERS statement at the
beginning of the RowSource property?
 
BobRoyAce said:
Thanks Dirk...I tried your suggestion, adding a function to the
module where the global variable is defined and changed the RowSource
to:

SELECT DISTINCTROW [Line Names].Line
FROM [Line Names]
WHERE [Line Names].Team=fnSelectedTeams();

SELECT DISTINCTROW [Line Names].Line
FROM [Line Names]
WHERE ([Line Names].Team=GetCurrentUserTeams);

This does not solve the problem as I still get a prompt asking for me
to enter a parameter, except this time the name of the parameter it
is looking for is the function name. Do I need to have a PARAMETERS
statement at the beginning of the RowSource property?
Click to expand...

No. If this is your SQL statement --
SELECT DISTINCTROW [Line Names].Line
FROM [Line Names]
WHERE ([Line Names].Team=GetCurrentUserTeams);
Click to expand...

-- then you've left off the parentheses that are required to let the
database engine know that GetCurrentUserTeams is a function. You need
the "()" on the end:

SELECT DISTINCTROW [Line Names].Line
FROM [Line Names]
WHERE ([Line Names].Team=GetCurrentUserTeams());
 
You are right indeed! Silly oversight on my part. Thanks for the
help...great solution!
 
Back
Top