Link to home
Create AccountLog in
Avatar of newknew
newknew

asked on

"global" variable/constant defintion for MS Access application

I want to define two variables (which are actually application constants) that can be accessed by all forms, queries, etc. in the MS Access desktop database.

EmployeeSSRate As Double = 0.042
EmployerSSRate As Double = 0.062

I need to know if I need to create a specific module and how to declare the "variables" for global access and ALSO how to access those values from forms, queries, etc.

Thanks,
Tim
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

"that can be accessed by all forms, queries, etc. in the MS Access desktop database."
You cannot reference these global variables from a query.

Other than that, put this in a regular vba module:


Option Compare Database
Option Explicit

Const EmployeeSSRate As Double = 0.042
Const EmployerSSRate As Double = 0.062

mx
Or, if ... you need to change these globals on the fly (?) ... then define them like so:

Option Compare Database
Option Explicit

Public EmployeeSSRate As Double
Public EmployerSSRate As Double


Then ... where ever necessary, you can set them as required, for example in the On Load event of a form:

Private Sub Form_Load()
    EmployeeSSRate = 0.042
    EmployerSSRate  = 0.062
End Sub

mx
You had to declare global variables in module (if you have no it, create new).
You can assign values in first opened form.
Avatar of newknew
newknew

ASKER

I'm going to have to be able to use it in queries... was trying to avoid a clunky "lookup table" that stored all my values in it, but looks like i'll need to use that anyway.

BTW, in your example, how do I reference the variable from a form?
"how do I reference the variable from a form?"
Well, how do you 'want' to ... and where ?

To retrieve from a query, you need a public function that you call from a query, for example

Public Function mGetMyValue (sOp)

    Select Case sOp
        Case "EmployeeSSRate"
                    mGetMyValue = EmployeeSSRate
        Case "EmployerSSRate "
                     mGetMyValue = EmployerSSRate
    End Select

End Function

You can then call from a query, passing the appropriate sOp value to retrieve the value needed.

mx
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Example of retrieving from a query:

SELECT Table1.ID, Table1.FIELD1, Table1.FIELD2, mGetMyValue("EmployeeSSRate") AS EmployeeSSRate, mGetMyValue("EmployerSSRate") AS EmployerSSRate
FROM Table1;


mx
newknew,

>>I'm going to have to be able to use it in queries... was trying to avoid a clunky "lookup table" that stored all
>>my values in it, but looks like i'll need to use that anyway.

MX's suggestion in http:#a34465962 is roughly what I would have recommended in terms of how to implement this in VBA, but that said, why NOT simply use a lookup table?

In my experience, any business rule parameters are easier to maintain if they are stored in a table rather than written into the VBA code itself.  In this case, you could have a lookup table such as:

tblSSRates

RateType    Rate
------------------
Employer    0.062
Employee    0.042

Open in new window


Indeed, I sometimes find it useful to have a kind of "catchall" "parameters" table to store various parameters in a common place.  I usually structure it like this:

tblParameters

ParamGroup     ParamItem     ParamValue
---------------------------------------
SSRate    Employer      0.062
SSRate    Employee      0.042
<other rows>

Open in new window


Patrick
Overall, I am not a fan of Globals ... and I would seriously consider the Table approach.  However, since you were looking for a VBA solution ... that's why I posted it.

mx
Avatar of newknew

ASKER

perfect, simple and obvious.  I already have some custom, global functions... this was a simple add, thanks.
Avatar of newknew

ASKER

sorry, i meant my last comment to the accepted solution, but I forgot that EE comments don't thread.
Well, I was thinking that I pretty much posted those function first, as well as showing how to define globals as well as an example of how to call from a query ... :-)

mx
Joe,

The difference between your function and mine is that you predefined the constants, then returned them via a single function call where you pass the constant name.  My method is simpler, you don't have to define a global, and all you have to do is call the functions individually.

I was a little surprised that I was awarded all of the points, but it's a new year and who am I to complain?  ;-)
Well, I was answering:

"and how to declare the "variables" for global access and ALSO how to access those values from forms, queries, etc."

RE:
"My method is simpler"
Humm ... well, not really ... because the OP has two constants that need to be returned ... yours only returns one.  So, either you need two Functions, or you pass a op code as to which one to return.

Also, the OP specified Double, yours is Single ... a minor point I know.  

Happy New Year ...

mx
Happy New Year to you as well.

I see that you and Capricorn1 have jumped out in the lead of points for the new year.  I sure hate starting out with 0 points again!