[AccessD] Static Function Tutorial (was Like Operator SQL Syntax)

Static functions contain variables whose value is preserved between calls.
More precisely, static variables retain their values. By default all
variables in a static function are static variables. You could declare a
static variable in a non-static function, but I don't know why you would. 
 
Here is my template for static functions. This code lives in every app I
write and I follow the directions:
 
Static Function CurrentX(Optional lngNew As Long) As Long
'Copy this template each time you need a new set/get function
'Then Replace "X" with the name of your object, i.e.,"Employee"
'Replace all in current proc and you're done.
 
    Dim lngCurrent As Long
    If lngNew <> 0 Then lngCurrent = lngNew
    CurrentX = lngCurrent
    #If conDebug = 1 Then
        Debug.Print "Current X: ", CurrentX
    #End If
End Function

In the old days, these were called get/set functions, because you can use
them to get or set their variable(s). 
 
To set the variable, you do this:
 
CurrentEmployee(123)
 
To get the variable, you do this:
 
CurrentEmployee()
 
So, given this setup, suppose you have a report that depends on a form being
open with a combo box item selected. You could pass in a WhereCondition when
you invoke the report, or you could write the report data source as follows:
 
SELECT * FROM myTables WHERE EmployeeID = CurrentEmployee()
 
Imagine, if you will, a listbox that shows the employees, and whose hidden
first column is the PK EmployeeID. When the user clicks "Preview Report" or
whatever, you write one line of code:
 
CurrentEmployee(Me.cboEmployeeList)
 
And then call your report:
 
DoCmd.OpenReport ReportName:=rptMyReport, View:=acPreview    'or whatever
you prefer
 
My preference for this path is based on these criteria:
1. Transparent code -- intuitively obvious even to the casual viewer
2. Everything SQL can do VBA code should not bother doing
 
There's a third reason, but first I must explain another variation on the
static func declared above. If you read it carefully, you will realize that
when the app opens CurrentEmployee() = 0. Each time you call it in set mode,
you assign a value > 0. As presented above, the function renders it
impossible to reset the value to zero. Why would you want to do this? I'll
answer that in a moment, but first let's add a reset method, as it were. 
 
We could just drop the IF, but that would render the GET mode useless, so
instead we need another method. I use -1 as the reset value, so the function
evolves to:
 
Static Function CurrentX(Optional lngNew As Long) As Long
'Copy this template each time you need a new set/get function
'Then Replace "X" with the name of your object, i.e.,"Employee"
'Replace all in current proc and you're done.
 
    Dim lngCurrent As Long
    Select Case lngNew
        Case Is < 0
            lngCurrent = 0
        Case Is > 0
            lngCurrent = lngNew
        Case Else
            'just return the current value
    End Select
    
    CurrentX = lngCurrent
    #If conDebug = 1 Then
        Debug.Print "Current X: ", CurrentX
    #End If
End Function
 
Now we have a way to pass in a "reset to zero" message.
 
Why would we want to do this? Because we can rewrite the query above using a
slick trick that deals with the zero value, and that lets us multiply the
utility of the query. I.e. we can now use it in 3 ways:
 
1. Suppose the combo-box employee selector allows extended select. we can
print one report for each selected employee. Walk the selected items and
reset CurrentEmployee() in the loop and print the report, nothing to it.
2. Suppose a form walks the employee table. In the OnCurrent call
CurrentEmployee(Me.EmployeeID) and every report that uses this approach is
ready to print. Nothing to it.
3. Reset the value to zero and this means print the whole bloddy thing, for
all employees. 
 
But to do this we need to modify the query, just a tad, adding a cool OR
test:
 
SELECT * FROM myTables 
WHERE 
    EmployeeID = CurrentEmployee()
    OR
    CurrentEmployee() = 0
 
Save this to a named query. Base your forms and/or reports on said query.
Refresh CurrentEmployee() as needed. Nothing to it.
 
Bryan Carbonell graciously showed me how to talk to Pegasus. The client
needs to send email reports to about 300 people, each email is unique, the
result of a report with a specified PK. Thanks to Bryan and static
functions, the solution is about 10 lines of code, maybe 20, I didn't
actually count them, but it's trivial. The code resets the static function,
outputs the report to a file, attaches the file to a new email message and
sends it. With all the benefits if CopyToSelf, select-all, just one, every
odd numbered item, &c. 
 
Bryan's nice sample code aside (but not without many thanks), the point is
that if you create a query whose parms are derived from static functions you
can forget all about what form is open &c.  You can be in debug window and
type:
 
CurrentX(123)
:123
'Assuming the recordsource is a query referencing CurrentEmployee()
DoCmd.OpenForm FornName:="frmEmployeeEdit"
DoCmd.OpenReport ReportName:="rptEmployeeSickDays"
 
In both cases, to see one Employee you call CurrentEmtployee(####). If you
want the whole list, reset the value of CurrentEmployee() to zero. 
 
CurrentEmployee(-1)
 
Sorry for the length of the message. But this approach has proven invaluable
to me. Everything I can blame on SQL I tend to shift in that direction. Then
when something goes amiss, I change some SQL and not some code scattered all
throughout my app. I guess it's a question of where you prefer to perform
your fixes. I like minimal code and maximal SQL. That's just my take on it,
and I know that many listers disagree.
 
(They are entitled to their worthless opinions :-) 
 
Arthur

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030607/9820ad00/attachment-0001.html>