Global Variables in Access Query

[MSeda]

I would like to use a Global variable as a criteria in an access query.
in my first attempt to do so I wrote a Function that looks like this

Public Function GloVar(ByVal VarName) As String

GloVar = VarName

End Function

It Didn't Work.
It evaluates to the text of the variable name not the value of the variable. Additionaly access forces the VarName to be in quotes. If I Type GloVar(JobNo) it automatically changes to GloVar("JobNo") .

I read a post from about a year ago that suggested using an If statement for each variable. i.e.

If VarName = "JobNo" Then
GloVar = JobNo

I (as the original poster) was hoping for a more universal function that would retrieve the value of whatever variable was entered without have to introduce an if statement for every variable.

Any help will be appreciated.

Megan

[NeoPa]

Am I right in thinking that you want a function (GloVar) to return the value (contents) of a variable, whose name (in the source code) matches the string passed in VarName?
Essentially this is impossible as the compiler is not running when the code is, so there is no access to the source code.
Strangely, VBA DOES provide a way (Eval() function) whereby you can call a function 'indirectly' in this way, but not to access a variable.

Interesting question.

[MSeda]

You understand pretty well what I want to do.

Specifically what I want to accomplish is this.

When a job is created via the "Create New Job Form" the global variable JobNo is assigned the Job Number. which is passed to subsequent forms i.e. "Create a purchase order".

I would like to be able to use the variable as criteria in a query. I am attempting to eleminate some of the bulky queries and code in my database and this seems like the simplest solution since the global variable can maintain its value while forms open and close during the job creation process and without the need for flags at the table level.

I am currently using a function that looks like this

Public Function GloVar(ByVal VarName) As String


Select Case VarName

Case "CurEmp"
GloVar = CurEmp

Case "CurEquip"
GloVar = CurEquip

Etc... for each variable

End Select
End Function

This method requires each variable be entered as a case into the function. I was hoping for something a little more universal that could be used with any variable.

I appreciate the input.

Megan

[NeoPa]

You can do what you're doing already, or alternatively, do a function for each variable.
As I explained in the previous post though, what you'd like to do is not supported in VBA (for the reasons I explained in said post it wouldn't make sense).

[PEB]

In fact Eval uses the visible objects from the modules in VBA...

And the global variables are visible only in modules but nowhere else..

Thill the functions are visible everywhere..

It's good solution your one using the select case method to show your global variables...

:)

[MSeda]

Thanks for the Input. I guess I'll stick with the case select function.

[NeoPa]

This function might prove helpful, although :
1. It works by ordinal (number of position in list) rather than name.
2. I haven't tested that it works independantly of my other routines (thug it passed a manual scan ;) ).

'RptParms sets and returns a set of parameters required by a report.
Public Function RptParms(intSetGet As Integer, _
                         ParamArray avarParams() As Variant) As Variant
    Static avarParms() As Variant
    Dim intIdx As Integer
 
    RptParms = 0
    If intSetGet = 0 Then
        intSetGet = UBound(avarParams) + 1 - LBound(avarParams)
        If intSetGet < 1 Then
            ReDim avarParms(1 To 1)
            avarParms(1) = "Error"
            Exit Function
        End If
        ReDim avarParms(1 To intSetGet)
        For intIdx = 1 To intSetGet
            avarParms(intIdx) = avarParams(intIdx - 1)
        Next intIdx
    Else
        'If outside bounds then it drops through and is set to "Error"
        On Error Resume Next
        If avarParms(intSetGet) = "Error" Then
            RptParms = "Error"                  'On Error
        Else
            RptParms = avarParms(intSetGet)
        End If
    End If
End Function

[MMcCarthy]

I would like to use a Global variable as a criteria in an access query.
in my first attempt to do so I wrote a Function that looks like this

Public Function GloVar(ByVal VarName) As String

GloVar = VarName

End Function

It Didn't Work.
It evaluates to the text of the variable name not the value of the variable. Additionaly access forces the VarName to be in quotes. If I Type GloVar(JobNo) it automatically changes to GloVar("JobNo") .

I read a post from about a year ago that suggested using an If statement for each variable. i.e.

If VarName = "JobNo" Then
GloVar = JobNo

I (as the original poster) was hoping for a more universal function that would retrieve the value of whatever variable was entered without have to introduce an if statement for every variable.

Any help will be appreciated.

Megan

Assuming varName is declared as a string

Global VarName As String

You are returning a string using the function you described
Instead declare VarName as a Variant and return a Variant from the function. This will allow for different datatypes.

Global VarName As Variant

 
Public Function GloVar(ByVal VarName) As Variant
 
GloVar = VarName
 
End Function
 
 

[MSeda]

Unfortunately changing to a GloVar to a variant still returns the name of the variable, or whatever text is entered as varname. I'm using the Case Select function for now. But I definately would prefer the simpler solution if you think of anything.
Thanks

[MMcCarthy]

Unfortunately changing to a GloVar to a variant still returns the name of the variable, or whatever text is entered as varname. I'm using the Case Select function for now. But I definately would prefer the simpler solution if you think of anything.
Thanks

Can you post the query statement in which you are trying to use this function.