Enterprise Software

Create Access forms that promote data accuracy

Don?t waste time fixing your users? data entry mistakes. Create Access forms that require fewer keystrokes, eliminating errors in the process. Here?s how to make your Access forms more efficient and your data entry troubles all but disappear.

Whether you are in charge of training your users how to use company Access forms, or you are in charge of fixing all the resulting mistakes from incorrect data entry, here are some tips to save your users time and keystrokes—and save yourself some time as well.

The less you do, the fewer mistakes you make
While it’s easy for nonprogrammers to create Access forms using the form wizard, these forms require that all information be typed into text boxes. Figure A shows an example of one such form used to track voter correspondence regarding bills currently up for debate in the state legislature.

Figure A

With only text boxes to work with, this form leaves users open to all sorts of data entry errors. For example, without training, users will not know that they need to enter a “1” if the correspondent is for the bill or “2” if against. Even with training, the natural tendency would be to enter the words "For" or "Against."

In my classes, I emphasize four techniques for creating forms that require little or no direct data entry. What’s more, students do not have to be programmers to develop forms that use these techniques.

Let the computer type for you
The easiest way to avoid typing errors is to have the computer type the data for you. For example, if this form is to be used by the New Jersey State Legislature, the staff will most likely have to type NJ in the State text box for almost every letter. To eliminate a good deal of this typing, set the default value for the State field to NJ, as shown in Figure B.

Figure B

Once set, Access will automatically enter NJ in the State field for each new record. If a letter is received from out-of-state; e.g., from a New York City business owner with an office in New Jersey, the user can still override the default and type in NY.

Click, don’t key
Another way to eliminate typing is to replace a text box with a list box that will let users select from a list of all possible entries. For example, in our demo form we can replace the Title text box with a list box by clicking on the List Box button in the Access Toolbox, as shown in Figure C.

Figure C

We then click-and-drag the mouse to draw a box on the form where we want the list box to appear. This displays the List Box Wizard, as shown in Figure D, where we select the option I Will Type In The Values That I Want; i.e., the values we want displayed in the list box.

Figure D

Next, we enter the desired values, as shown in Figure E.

Figure E

Then, we select the field (Title) where we want the selected value to be stored, as in Figure F.

Figure F

Finally, we assign a label to our list box and click Finish, as in Figure G.

Figure G

The correspondent’s title can now be entered by scrolling through the list and clicking on the appropriate entry. (See Figure H.)

Figure H

What if it’s not on the list?
Often, you won’t be able to anticipate all the possible values that should be in a list. For example, if our form is used to track voter opinion in a particular voting district, we could build a list box listing all ZIP codes within that district. But as we’ve seen in the Default Field Value technique, there may be a few cases where letters will come from outside the district. Because list boxes do not let users enter values outside the list, we need to build a combo box. Like the list box, a combo box can be used to list commonly entered values, but it differs in that users can enter a value outside the list when necessary.

To build a combo box for the PostalCode field, we select the Combo Box button from the Toolbox as in Figure I.

Figure I

Similar to the List Box Wizard, we then enter the values we want to appear in the list, as in Figure J.

Figure J

We continue to follow the wizard’s instructions much like we did when we created the Title List Box; only here we indicate that the selected values should be stored in the PostalCode field. (See Figure K.)

Figure K

The resultant combo box is shown in Figure L.

Figure L

Use Options instead of Lists
List boxes require that the user scroll and click. If you have room on your form, you can eliminate the need to scroll by using option groups in place of lists.

In this example, we will replace our For/Against text box with an option group. First, we click on the Option Group tool button and then draw the option group box on the form by clicking and dragging with the mouse. This displays the following screen, where we enter the two options, For and Against, as shown in Figure M.

Figure M

Next, we assign the values that will be stored in the For/Against Bill field for each option; "1" for For and "2" for Against, as in Figure N.

Figure N

In the next screen, we indicate the field where we want the values to be stored, as in Figure O.

Figure O

If we wish, we can now click Next, which will allow us to give our option group a label; in this case, we entered, "For Or Against Bill?" Clicking Finish will display the option group, as shown in Figure P.

Figure P

Hold down the keystrokes
Whenever possible, use these four techniques (default field values, list boxes, combo boxes, and option boxes) to eliminate text boxes in your Access forms. As the number of required keystrokes is decreased, so too will the number of data errors and omissions decrease.

Stop the errors of your users’ ways
How do you keep your users from making costly errors? Let us know by posting a note below or by sending us a note.


Editor's Picks

Free Newsletters, In your Inbox