MS-Access / Getting Started

---

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, requiring 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 reference 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
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-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.

LISTING-1 Using a Bookmark to Mark a Record

Public Sub BookmarkExample()
  Dim rs As DAO.Recordset
  Dim bk As Variant
  Set rs = Workspaces(0).Databases(0).OpenRecordset( _
    "tblContacts", dbOpenTable)
  'Move to the first record in the database:
  rs.MoveFirst
  'Print the position in the database:
  Debug.Print rs.PercentPosition
  'Set the bookmark to the current record:
  bk = rs.Bookmark
  'Move to the last record in the database:
  rs.MoveLast
  'Print the position in the database:
  Debug.Print rs.PercentPosition
  'Move to the bookmarked record:
  rs.Bookmark = bk
  'Print the position in the database:
  Debug.Print rs.PercentPosition
  rs.Close
  Set rs = Nothing
End Sub