I like to use functions to store many of my variables. This works similar to
having textboxes on a hidden form, except I just find it easier. It
overcomes the problem that Stuart mentions (dropping values on unhandled
errors), and avoids me having to maintain a separate form. It also allows
for some error checking and other options that using a textbox doesn't do for
you. You can also use a function call in a query (which you cannot do with a
variable), and I find that since my functions are all typed (declared as a
particular data type), I don't need to declare them as parameters in the
query.
I generally create a code module (mod_Global_Variables) and put all the code
in that module. I take advantage of the use of static variables, so that
each time I call the function, it retains the previously set value, if I
don't overwrite it. An example of this might be:
Public Function fnEmpID(Optional SomeValue As Variant = Null, Optional Reset
As Boolean = False) As Long
Static EmpID As Long
Dim varInput As Variant
If Reset = True Then EmpID = 0
If Not IsNull(SomeValue) Then
EmpID = SomeValue
ElseIf EmpID = 0 Then
Do
varInput = InputBox("Enter an employeeID")
If IsNumeric(varInput) Then
EmpID = CLng(varInput)
Exit Do
End If
MsgBox "Input must be numeric"
Loop
End If
fnEmpID = EmpID
End Function
I define the parameters to be passed as optional, so that if none are
passed, the function returns the value previously assigned to the static
variable. I generally declare the main parameter "SomeValue" as a variant
and set the default value to NULL, so that I can test use that to determine
whether a value was passed. I also frequently include a "Reset" variable so
that I can clear out the static variable for testing purposes.
To set the value of fnEmpID, you just add a line of code to your application:
fnEmpID 123
or
call fnEmpID(123)
To use this in a query, you might write:
SELECT * FROM tbl_Employees WHERE [EmpID] = fnEmpID()
The neat thing about this is that since the default value of a numeric
variable is zero (0), I can test in my function to determine whether the
value has been set. If not, I can open a form or use an inputbox to allow
the user to input the value, or could lookup a default value in another table.
Another advantage is that when you are debugging your code, you don't have
to have the "hidden form" that Dirk mentioned open and populated. You can
just call the function from the immediate window and pass it a value, or
check its value.
Hope this all makes sense.
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
