Friday, April 4, 2008

Making use of Continous Forms

For those of you that may be new to Access, here is an important tip to create more effective database forms: take advantage of the "Continuous Forms" feature extensively. Especially if you or your users are coming from an Excel-based world.

Basic Use

In the most basic approach, you would simply create a form that has the form property View" to "Continuous Forms." This will give you the multi-row view you and your users are "Default accustomed to.

And to make sure you're using the most of your screen real estate, just put the most important fields in the Detail section of the form. If you have another set of fields that is less important, put in the "Footer" section of the form. In the example below, you'll notice there are only five columns in the main section.
The rest (in this case it's just a big notes field) can be put down at the bottom, and will change automatically based on the record you've selected. To display the Header/Footer, if it doesn't show up already, you'll need to click "View>>Header/Footer" in older versions of Access. In Access 2007, you'll need to look for an icon in the "Arrange" tab of the Ribbon that looks like this:
(yes, I know, not the most obvious looking icon, one of the many rough adjustments to the new ribbon UI)

Advanced Approach
The best way to approach interface design is often to aggregate data from multiple data sources in a single place for the user. This can best be accomplished by placing a subform in the main Form Footer section. And even better, making that subform a continous form. For example, you have Customers in your main form, and in the Form Footer subform, you'd like to see all the Orders for the selected Customer. Create your Orders form. Then in the Form Footer section of the main form, insert a subform (look for the icon on the design toolbox). Now when you select your new Orders form, you'll get an error message (at least in pre-2007 versions) telling you that Access will need to set the main form to a Single Form view instead of Continuous Forms. Ignore that error and proceed. Now that you have your subform in the Footer, feel free to change your main form back to a Continuous Form. Everything will work just fine, and now you can work more efficiently with your data all in single place. Here's a good example from our Assets template:

Note that you can see the asset, and all related maintenance records, in a single place.