<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=US-ASCII">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1170" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>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. </FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>Here
is my template for static functions. This code lives in every app I write and I
follow the directions:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>Static
Function CurrentX(Optional lngNew As Long) As Long<BR>'Copy this template each
time you need a new set/get function<BR>'Then Replace "X" with the name of your
object, i.e.,"Employee"<BR>'Replace all in current proc and you're
done.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2> Dim lngCurrent As Long<BR> If lngNew
<> 0 Then lngCurrent = lngNew<BR> CurrentX =
lngCurrent<BR> #If conDebug = 1
Then<BR> Debug.Print "Current X: ",
CurrentX<BR> #End If<BR>End Function<BR></FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>In the
old days, these were called get/set functions, because you can use them to get
or set their variable(s). </FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>To set
the variable, you do this:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2>CurrentEmployee(123)</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>To get
the variable, you do this:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2>CurrentEmployee()</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>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:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>SELECT
* FROM myTables WHERE EmployeeID = CurrentEmployee()</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2>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:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2>CurrentEmployee(Me.cboEmployeeList)</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>And
then call your report:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2>DoCmd.OpenReport ReportName:=rptMyReport,
View:=acPreview 'or whatever you prefer</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>My
preference for this path is based on these criteria:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>1.
Transparent code -- intuitively obvious even to the casual
viewer</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>2.
Everything SQL can do VBA code should not bother doing</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2>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. </FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>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:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>Static
Function CurrentX(Optional lngNew As Long) As Long<BR>'Copy this template each
time you need a new set/get function<BR>'Then Replace "X" with the name of your
object, i.e.,"Employee"<BR>'Replace all in current proc and you're
done.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2> Dim lngCurrent As Long<BR> Select
Case lngNew<BR> Case Is <
0<BR>
lngCurrent = 0<BR> Case Is >
0<BR>
lngCurrent = lngNew<BR> Case
Else<BR> 'just
return the current value<BR> End Select<BR>
<BR> CurrentX = lngCurrent<BR> #If conDebug
= 1 Then<BR> Debug.Print "Current X:
", CurrentX<BR> #End If<BR>End Function</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>Now we
have a way to pass in a "reset to zero" message.</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>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:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>3.
Reset the value to zero and this means print the whole bloddy thing, for all
employees. </FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>But to
do this we need to modify the query, just a tad, adding a cool OR
test:</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>SELECT
* FROM myTables </FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff size=2>WHERE
</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003><FONT face=Arial color=#0000ff
size=2> EmployeeID = CurrentEmployee()</FONT></SPAN></DIV>
<DIV><SPAN class=125352919-07062003> OR</SPAN></DIV>
<DIV><SPAN class=125352919-07062003> CurrentEmployee() =
0</SPAN></DIV>
<DIV><SPAN class=125352919-07062003></SPAN> </DIV></FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=125352919-07062003>Save
this to a named query. Base your forms and/or reports on said query. Refresh
CurrentEmployee() as needed. Nothing to it.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=125352919-07062003>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. </SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003>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:</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003>CurrentX(123)</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003>:123</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003>'Assuming the recordsource is a query referencing
CurrentEmployee()</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003>DoCmd.OpenForm
FornName:="frmEmployeeEdit"</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003>DoCmd.OpenReport
ReportName:="rptEmployeeSickDays"</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=125352919-07062003>In
both cases, to see one Employee you call CurrentEmtployee(####). If you want the
whole list, reset the value of CurrentEmployee() to zero. </SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003>CurrentEmployee(-1)</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=125352919-07062003>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.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=125352919-07062003>(They
are entitled to their worthless opinions :-) </SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=125352919-07062003>Arthur</SPAN></FONT></DIV><!-- Converted from text/plain format -->
<P><FONT face=Arial color=#0000ff size=2></FONT> </P></BODY></HTML>