Designing Forms for Efficient and Accurate Data Entry

This excerpt from “Microsoft Office Access 2007 Forms, Reports, and Queries” introduces several techniques that serve to either make data entry less of a chore, or to reduce or eliminate data entry errors (or both).

Chapter 3: Designing Forms for Efficient and Accurate Data Entry

In this chapter

• Preventing Errors by Validating Data
• Using Controls to Limit Data Entry Choices
• Case Study: Using an Option Group to Select the Shipper
• Entering Data with ActiveX Controls
• Collecting Form Data via Email
• From Here

Data entry is one of those tasks that I describe as “dangerous” because it’s a chore that’s both tedious and important. It’s tedious because entering dozens or hundreds of records is no one’s idea of fun. It’s important because the data must be entered accurately; otherwise, any analysis of the data becomes at best misleading and at worst just plain wrong. The danger, then, lies in the fact that data entry is prone to errors but can’t afford to have any.

As a forms designer, you can help to reduce this danger by setting up your forms so that data entry is both as efficient as possible and as accurate as possible. In some cases you can achieve both goals with a single technique. For example, asking someone to type a customer name manually is both slow and prone to misspellings. However, suppose you already have a Customers table with a CustomerName field. If you relate the current table with the Customers table (using, say, a common CustomerID field), Access adds the CustomerName field to the current form using a drop-down list that contains all the customers. This makes data entry more efficient (the users just select a name from the list instead of typing it) and more accurate (the users can’t misspell the customer name).

This chapter introduces you to several techniques that serve to either make data entry less of a chore, or to reduce or eliminate data entry errors (or both).

Preventing Errors by Validating Data

If, as the cooks say, a recipe is only as good as its ingredients, a database is only as good as its data. Viewing, summarizing, and analyzing the data are meaningless if the table you’re working with contains erroneous or improper data. For basic data errors (for example, entering the wrong date or transposing a number’s digits), there’s not a lot you can do other than exhorting yourself or the people who use your forms to enter data carefully. Fortunately, you have a bit more control when it comes to preventing improper data entry. By “improper,” I mean data that falls in either of the following categories:

• Data that is the wrong type. For example, entering a text string in a cell that requires a number.

• Data that falls outside an allowable range. For example, entering 200 in a cell that requires a number between 1 and 100.