Retrieve Value Of Global Variables

[Salvani Langosta]

In an Access 97 database, I use serveral global variables that hold
information about the database, for example:

gstrFileServer - holds the server root where the database is stored
gstrDataServer - holds the server root where a related data warehouse
is stored

The values of these variables are set when the database is opened.

I want to retrieve one or more of these values in a query, but Access
does not recognize variables in a query. The alternative is to
include a function call in the query that returns the value of a
variable.

I want to write one function that accepts a string value identifying
the variable, then returns the value of the given variable.

Calling the function from the debug window might produce the
following:

? GetValue("gstrFileServer"):? GetValue("gstrDataServer")
\\FILE_001\VOL_0001
\\DATA_009\VOL_0002

What is the function that will extract the value from a given
variable?

Thanks in advance,

Gordon Rogers
MICO, Inc.

[Arno R]

Gordon,
You could create a function like:

Function GetgstrValue (strWantedValue as String) as String
If strWantedValue ="gstrFileServer" Then GetstrValue = gstrFileServer
If strWantedValue ="gstrDataServer " Then GetstrValue = gstrDataServer
End function

Be aware of the fact that global vars loose their values after untrapped runtime errors.
In a runtime Access environment this is no problem (Access crashes... and has to be restarted)
In a full Access environment I have seen unpredictable result swith this.

You could also store the values in a table and create a function to Lookup when needed.

--
Hope this helps
Arno R

"Salvani Langosta" <ro*****@mico.com> schreef in bericht
news:a9**************************@posting.google.c om...

In an Access 97 database, I use serveral global variables that hold
information about the database, for example:

gstrFileServer - holds the server root where the database is stored
gstrDataServer - holds the server root where a related data warehouse
is stored

The values of these variables are set when the database is opened.

I want to retrieve one or more of these values in a query, but Access
does not recognize variables in a query. The alternative is to
include a function call in the query that returns the value of a
variable.

I want to write one function that accepts a string value identifying
the variable, then returns the value of the given variable.

Calling the function from the debug window might produce the
following:

? GetValue("gstrFileServer"):? GetValue("gstrDataServer")
\\FILE_001\VOL_0001
\\DATA_009\VOL_0002

What is the function that will extract the value from a given
variable?

Thanks in advance,

Gordon Rogers
MICO, Inc.

[Tim Marshall]

Salvani Langosta wrote:

? GetValue("gstrFileServer"):? GetValue("gstrDataServer")
\\FILE_001\VOL_0001
\\DATA_009\VOL_0002

What is the function that will extract the value from a given
variable?

Have you tried something like:

Function fDataServer as string

fDataServer = gstrFileServer

end function

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto

[Salvani Langosta]

> You could create a function like:

Function GetgstrValue (strWantedValue as String) as String
If strWantedValue ="gstrFileServer" Then GetstrValue = gstrFileServer
If strWantedValue ="gstrDataServer " Then GetstrValue = gstrDataServer
End function

You're on the right track here, but I want to avoid using an if
statement for every variable used (there are 20+ variables) in the
application. What I want to do is provide a function that can be
called from a query, and will return the value held by the variable,
i.e.

SELECT GetValue("gstrFileServer") As Value...

Any other ideas?

[Salvani Langosta]

> > ? GetValue("gstrFileServer"):? GetValue("gstrDataServer")

\\FILE_001\VOL_0001
\\DATA_009\VOL_0002

What is the function that will extract the value from a given
variable?

Have you tried something like:

Function fDataServer as string

fDataServer = gstrFileServer

end function

Would this require a separate function for each variable? My goal is
to write one function that will return the value of any variable when
called from a query, i.e.

SELECT GetValue("gstrFileServer") As Value...

Any other ideas?

[Arno R]

Gordon,
I don't see a problem using more statements in the function GetstrValue.
What's the deal? Function does exactly what you want?
The function can (and must in the case of 20+ gvars) be written more efficiently but it needs more
lines then...
Function GetgstrValue (strWantedValue as String) as String
If strWantedValue ="gstrFileServer" Then
GetstrValue = gstrFileServer
ElseIf strWantedValue ="gstrDataServer " Then
GetstrValue = gstrDataServer
......
End if
End Function
If you don't like 'If' then use Select Case ;-)

If you use a table as I suggested you can go along with only one Lookup-function with only one line
like:
(tblGlobals as suggested by Paii)

Function GetgstrValue (strWantedValue as String) as String
GetgstrValue = dlookup("[GlobalValue]","tblGlobals","[GlobalID]='" & strWantedValue & "'")
End function

--
Hope this helps
Arno R
"Salvani Langosta" <ro*****@mico.com> schreef in bericht
news:a9**************************@posting.google.c om...

You could create a function like:

Function GetgstrValue (strWantedValue as String) as String
If strWantedValue ="gstrFileServer" Then GetstrValue = gstrFileServer
If strWantedValue ="gstrDataServer " Then GetstrValue = gstrDataServer
End function

You're on the right track here, but I want to avoid using an if
statement for every variable used (there are 20+ variables) in the
application. What I want to do is provide a function that can be
called from a query, and will return the value held by the variable,
i.e.

SELECT GetValue("gstrFileServer") As Value...

Any other ideas?

[Trevor Best]

On 2 Sep 2003 14:05:36 -0700 in comp.databases.ms-access,
ro*****@mico.com (Salvani Langosta) wrote:

> ? GetValue("gstrFileServer"):? GetValue("gstrDataServer")
> \\FILE_001\VOL_0001
> \\DATA_009\VOL_0002
>
> What is the function that will extract the value from a given
> variable?

Have you tried something like:

Function fDataServer as string

fDataServer = gstrFileServer

end function

Would this require a separate function for each variable? My goal is
to write one function that will return the value of any variable when
called from a query, i.e.

SELECT GetValue("gstrFileServer") As Value...

Any other ideas?

You could use GetSetting() and SaveSetting() to retrieve and store in
the registry. For intrinsic values only.

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)