Part IV: Professional Database Development
864
Some Access functions perform similar processes but vary greatly in execution time. You probably
use one or more of these regularly, and knowing the most efficient way to perform these routines
can greatly affect your application’s speed:l The IIF() function is much slower than If...Then...Else.l (^) The With and For Each functions accelerate manipulating multiple objects and their
properties.
l (^) Change a variable with Not instead of using an If... Then statement. (For example,
use x = Not(y) instead of If y = True then x= False.)
l (^) Instead of comparing a variable to the value True, use the value of the variable. (For
example, instead of If X = True then.. ., use If X then.. .)
l (^) Use the Requery method instead of the Requery action. The method is significantly
faster than the action.
l (^) When using OLE automation, resolve references by declaring variables as specific object
types, rather than creating object references at runtime by using the GetObject or
CreateObject functions. For example, in Chapter 22 you saw how to create object
variables using the following syntax:
Dim WordObj As Word.Application
This statement is considerably faster than using CreateObject:
Set WordObj = CreateObject(“Word.Application”)
Using control variables
When referencing controls on a form in code, there are some very slow and some very fast ways to
use references to controls. The slowest possible way is to reference each control explicitly, requir-
ing Access to sequentially search for the control on the form. For example:
Forms![frmSales]![SaleDate] = something
Forms![frmSales]![InvoiceDate] = something
Forms![frmSales]![SalespersonID] = something
If the code is in the code module behind frmSales, you can use the Me reference. The Me refer-
ence substitutes for Forms![formname] and is much faster because it can go right to the form:
Me![SaleDate] = something
Me![InvoiceDate] = something
Me![SalespersonID] = something
If your code is not stored behind the form but is in a module procedure, you can use a control
variable like the following:
Dim frm as Form
set frm = Forms![frmSales]
frm![SaleDate] = something