a simple getter function for a constant

[veloopity]

I want to define a number of public constants, like this

public const const_a = 14
public const const_b = 29

and I would like to use these constants in queries (independent of forms), like this

select * from mytable where ID = const_a

This doesn't work as easily of course, so it seems I need a getter function like this

public function myconst (constantname)
myconst = eval(constantname)


end function

I could then use the function in my query, like this

select * from mytable where ID = myconst("const_a")

Unfortunately, this still doesn't work because even though the value gets sent to the function and could be outputted, the expression "eval(constantname)" doesn't return the value of the constant with that name.

I don't know what to do, but can't believe this can't be done in a simple way. Looking forward to your solution

-Michael

[ItsMe]

If const_a = 14

why not just have

select * from mytable where ID = 14

[veloopity]

because it is not 2 constants, but dozens of them. Believe me, we have reasons to do it that way. We want to keep literal ID values out of our code and store them in a central place (but not in a database table but in a list of constants.) So, ideas how to do it?

[rankhornjp]

does "const_a" ever change?

[ItsMe]

You could place some variables in a general module as public. ie, intDeptID or lngDeptID, intUserID or lngUserID.

Then, when the program knows what the value is, maybe 14, it can assign it to the variable. Maybe when a form opens you can do something like intDeptID = 14. Then you can use intDeptID in various functions and or routines throughout the DB.

I really don't know the solution. That Is why I asked a question. I am still not sure I understand but.....

[rankhornjp]

Also, how do you select which constant you want to use? Are you using a list box or text box or .....?

[veloopity]

I don't want to explain why I am doing things, I need a solution for my question. I think I have stated clearly enough what my question is. Again:

Imagine a public constant const_a, containing the value 43. How can I write a simple function that is given the string "const_a" and that would give me back the value 43?

[Dal Jeanis]

1) Assuming that "dozens" is the right order of magnitude, you can use TempVars to store those values. They will always be available to queries and code, and can be accessed with a standard naming convention. You are limited to 255 total tempvars.

2) Depending on how you plan to use the constants, you could create a tblConstants table with two fields, ConstName and ConstValue. This is more clumsy than TempVars, but has no upper limit for how many you could use.

3) Use a getter function such as the following, in a standard module:

Option Compare Database
Option ExplicitGlobal GBL_UserID As String
Global GBL_UserName As String
Global GBL_LogInTime As Date
Global GBL_LogOutTime As Date

Function set_globals()
    GBL_UserID = Environ("UserID")
    GBL_LogInTime = Now()
End Function
   
Public Function get_global(G_name As String) As Variant
     Select Case G_name
        Case "GBL_UserID"
             get_global = GBL_UserID
        Case "GBL_UserName"
             get_global = GBL_UserName
        Case "GBL_LogInTime"
             get_global = GBL_LogInTime
        Case "GBL_LogOutTime"
             get_global = GBL_LogOutTime
    End Select
End Function

[veloopity]

thx bunches Dal, sounds good to me, will check this out on Monday!!

[veloopity]

great! I hadn't heard about Tempvars before. Works like a charm!

[Dal Jeanis]

Great! Please mark the thread solved. Top of page, under "thread tools".