Microsoft Access 2010 Bible

Chapter 24: Optimizing Access Applications

865

frm![InvoiceDate] = something

frm![SalespersonID] = something

This way, the form name is looked up only once.

An even faster way is to use the With construct:

With Forms![frmSales]

![SaleDate] = something

![InvoiceDate] = something

![SalespersonID] = something

End With

Using field variables

The preceding technique also applies to manipulating field data when working with a recordset in
VBA code. For example, a typical loop looks something like this:

...

Do Until tbl.EOF

MyTotal = MyTotal + tbl![OrderTotal]

tbl.MoveNext

Loop

If this routine loops through many records, you should use the following code snippet instead:

Dim MyField as Field

...

Set MyField = tbl![OrderTotal]

Do Until tbl.EOF

MyTotal = MyTotal + MyField

tbl.MoveNext

Loop

This code executes much faster than explicitly referencing the field in every iteration of the loop.

Increasing the speed of finding data in code

Use the FindRecord and FindNext methods on indexed fields. These methods are much more
efficient when used on indexed fields. Also, take advantage of bookmarks when you can. Returning
to a bookmark is much faster than performing a Find to locate the data.

Listing 24.1 is an example of using a bookmark. Bookmark variables must be dimmed as variants,
and you can create multiple bookmarks by dimming multiple variant variables. The following code
opens tblCustomers, moves to the first record in the database, sets the bookmark, moves to the
last record, and finally repositions back to the bookmarked record. For each step, the debug.
print command shows the relative position in the database.