Designing Forms and Reports for Speed

Most interaction between the user and your application happens through forms and reports. Each open form or report in your application uses memory and requires some disk activity. This section contains suggestions on how you can design and manage forms and reports so your application runs faster.

Optimizing Form Loading and Paging

The following tips can reduce the amount of time the user spends waiting for a form to load and to switch between pages.

Simplify the Form's Design

The number and type of controls on a form can greatly affect the time it takes for the form to load. To make your forms load more quickly:

 Minimize the number of controls on your form. Loading controls is the biggest performance cost when loading a form. Where applicable, reuse controls on your form in multiple situations.

 Avoid overlapping controls.

 Group the controls on pages instead of displaying them all at once. That way, for forms that must contain many controls, Microsoft Access doesn't have to generate all the controls at the same time.

Eliminate Code from Forms That Don't Need It

A form without a form module loads more quickly and occupies less disk space than one with a form module. To make your forms load more quickly, find alternatives to using event procedures in your forms and remove any existing form modules that you can.

There are several alternatives to using event procedures in your forms and controls. For example, suppose your application uses a switchboard form exclusively to navigate to other forms. When you design your switchboard form, instead of using command buttons with event procedures, you can use command buttons with macros. You can also use labels, text boxes, or command buttons that navigate to objects by using hyperlinks.

See Also   For more information on hyperlinks, see Chapter 21, "Developing Applications for the Internet and the World Wide Web" or search the Help index for "hyperlinks."

Note   If a form and its controls don't use event procedures, the form doesn't need a form module. If a form currently contains event procedures, and you remove all event procedure code from that form, the empty form module isn't deleted automatically. If you want to delete the empty form module, you must do so manually by setting the form's HasModule property to No. You don't need to do this for forms that have never had any code because when you create a new form, the HasModule property is set to No by default and a form module isn't created for the form.

You can still use code with a form that has no form module by using an expression to call a Function procedure from a standard module. To do this, define a Function procedure in a standard module and then call that Function procedure from an event property of the form or a control. For example, to open a Data Entry form when a user clicks a command button, type the following expression in the OnClick event property setting in the command button's property sheet:

=OpenDataEntry()

Note   You can't use Sub procedures to do this, because they can't be called using an expression.

If you call a Function procedure from a standard module by using an expression, the form opens more quickly because the module isn't loaded into memory when the form is opened. However, the standard module takes time to load into memory the first time an expression calls a Function procedure from it.

Use Saved Queries for RecordSource and RowSource Property Settings

A saved query gives better performance than an SQL SELECT statement. Therefore, you should use saved queries for RecordSource and RowSource property settings.

See Also   For more information on the RecordSource and RowSource properties, search the Help index for the name of the property.

Use an Image Control to Display Bitmaps

If an unbound object frame doesn't need to be activated, use an image control instead of an unbound object frame control to display a bitmap. Object frame controls are actual windows and use significant system resources; an image control doesn't use nearly as many resources.

If you have existing unbound object frame controls containing bitmaps that don't need to be activated, you can greatly reduce a form's load time by converting them to image controls.

   To convert an unbound object frame containing a bitmap to an image control

1   Open the form in Design view.

2   Click the unbound object frame.

3   On the Format menu, point to Change To, and then click Image.

Microsoft Access converts the unbound object frame to an image control. The bitmap remains on the form, but you can't open the application used to create the bitmap from the form; therefore, you can't edit the image.

Also, avoid using bitmaps that were created from large palettes or that contain many colors because they take longer to load and use more memory.

Close Forms That Contain Unbound OLE Objects When Not in Use

When the user activates an unbound OLE object, information about the object is stored in RAM. This memory isn't released until the form closes. To minimize the performance impact of unbound OLE objects, design your application so that forms that contain such objects close when they aren't being used, such as when the focus shifts to another form. In addition, if you want to display an objectsuch as part of a Microsoft Excel worksheetbut you don't want the user to be able to edit the object, set the control's Enabled property to False. That way, the user can't activate the object.

In Multiple-Page Forms, Initialize Pages Only When Needed

If your application uses multiple-page forms, initialize and prepare only the first page when the form opens. Don't spend time preparing pages or data structures until the user moves to the page that contains them. If a subform, list box, or ActiveX control takes a long time to fill, wait until the user goes to that page before setting the ControlSource or RowSource property.

Consider Converting Subforms to List Boxes or Combo Boxes

When Microsoft Access loads a form with a subform, it's actually loading two forms into memory. To speed up the form's load time, use a list box, a combo box, or a form based on a query that contains all of the fields that you want to display.

Optimize List Box and Combo Box Performance

To help your forms load more quickly, set the RowSource property of your list boxes and combo boxes to a query that includes only fields from the record source that are absolutely necessary. Create an index for the first field displayed in the list box or combo box, and any other fields that are used for criteria.

The bound column in your list box or combo box should have the text data type, not a numeric data type. To find a match in the list, Microsoft Access needs to convert a numeric value to text in order to do the character-by-character match. If it already has a text data type, this conversion isn't necessary.

If you don't need Microsoft Access to automatically fill in the text box portion of your combo box with a value that matches the characters your user types, set the AutoExpand property to No.

Place Code in a Standard Module to Reduce Form Load Time

For a complex form that the user opens frequently, consider moving some of the form's code into a standard module. This reduces the time it takes to load the form.

Open and Hide All Forms at Startup

If you use many forms on a frequent basis, you may want to open and hide all forms when your application opens. To open the forms, you can add Visual Basic code to the startup form's Open event procedure. To hide the forms, use the OpenForm action and set the Window Mode argument to Hidden. Then, instead of having your application open each form when the user needs it, set the form's Visible property to True. When the user finishes with a form, set its Visible property to False. While this technique slows startup because your application opens all its forms at that time, it can improve performance after startup considerably.

See Also   For more information on using event procedures, see Chapter 2, "Introducing Visual Basic." For more information on setting a form's properties while it's being used, see Chapter 3, "Using Forms to Collect, Filter, and Display Information."

Important   Remember that any reference to properties, methods, or controls on a form, or to a form variable declared with the New keyword, causes Visual Basic to load the form. For more information on the New keyword, search the Help index for "New keyword."

Don't Open Forms and Reports Across a Network

If your application uses data that's shared over a network, split the application into two database filesa back-end database that contains all the tables, and a front-end database that contains all the other objects in your application and that links to the tables in the back-end database. Store the back-end database on the network server, and distribute copies of the front-end database to your users so that each user has a local copy. Local forms and reports open faster and reduce network traffic.

Note   You can use the Database Splitter Wizard to separate your tables from the other objects in your database. For information on using this wizard, see "Separating Your Application's Tables from Its Other Objects" in Chapter 15, "Delivering Your Application."

Optimizing Operations on Forms and Reports

The following section suggests ways you can speed up common operations on forms and reports and provide feedback to the user during long operations.

Set Only the Properties That Need to Be Set

Setting a property in code always uses processor time. You may want to check the value of a property before you set it in code because it may already be set to the value you want.

Use the Requery Method Instead of the Requery Action

If you're updating data in a form or control, use the Requery method rather than the Requery action to update the data. The Requery method's performance is significantly better than the Requery action's performance.

For Fast Printing, Use Fewer Graphic Objects on Forms and Reports

Microsoft Access treats lines, bordered controls, and opaque controls on a form or report as individual bitmaps. Forms and reports that contain these or other graphic objects can print more slowly than forms and reports without graphic objects. For faster printing on nonlaser printers, delete some or all of the graphic objects from the form or report. For faster printing on a laser printer, be sure the form or report's FastLaserPrinting property is set to Yes.

Give the User Feedback During Long Operations

When performing time-consuming operations, give the user visual feedback that something is happening. Use the Hourglass method or action to display the hourglass, or create a progress meter to keep the user informed of the operation's progress.

See Also   For information on displaying a progress meter, search the Help index for "SysCmd function."

© 1996 Microsoft Corporation. All rights reserved.