A few years ago, while teaching an introductory Microsoft Access course at a technical college, I came across some very simple—and vital—lessons that have value far beyond the beginner’s classroom. My years as a consultant have shown me how these application design lessons need to be a part of the consultant’s knowledge base whenever the client’s solution involves the creation or enhancement of existing applications. Here is how these lessons came to light in my Microsoft Access class. The takeaways are for consultants of every level.

The class
The class’s assignment for the semester was a very common products/customers/sales model. I had taught the students introductory database design, and now it was time to try to create a working sample application from the ground up.

The students dutifully created the tables, keys, and relationships. They were anxious to leave the world of Dr. Codd behind and move on to the exciting business of creating a user interface with colors, images, and things that reacted when you clicked on them. Most of them smugly ran the Form Wizard against each of the tables they had created and handed in their first assignment deliverable thinking that making money as a developer was going to be child’s play.

When I reviewed the students’ handiwork in class, however, reality set in. Admittedly, the standard Create, Retrieve, Update, and Delete operations could have been accomplished using the simple interface they had created, but things started to fall apart when I worked through some basic scenarios.

My students quickly learned that is not enough to create a series of generic interfaces. Windows must be designed to aid users in completing their specific tasks or the application will not be successful.

How will the applications be used?
I had the students consider the plight of the receiver at the distribution center who would be faced with using their applications. The receiver’s main task was to receive shipments of products and accurately record their availability so that other employees could find them in the database and to create Sale or Order records in which customers agree to purchase those products. In our simple database, the Product table included columns or fields such as ProductID, Description, QtyOnHand, and QtyOnOrder. To receive a shipment of a particular product, the receiver had to do the following:

  1. Find the record for the product using the ProductID from the copy of the purchase order.
  2. Increase the value in the QtyOnHand field by the number of that product received.
  3. Decrease the value in the QtyOnOrder field by the same amount.

How could this be done using our simple Products form? Once we had navigated to the desired record using a Filter or Search, we had to enter the current value of the QtyOnHand field into a calculator and add the quantity received before entering the new value. Then we had to calculate the new QtyOnOrder using a similar technique before moving to the next record to save our changes.

Even the students who at first helpfully suggested popping up the Calculator program on the screen eventually realized that this was not user-friendly. When a user working on a powerful PC must use a calculator in order to complete an operation in your application, something is seriously wrong with your design.

Takeaway: If the application created by the consultant forces the user to do extra work or complicates the user’s everyday tasks even further, the application will not be accepted. This constitutes a failure on the part of the consultant, not the user.

Start with the task
Lacking experience with application design and the needs of end users, the students had made a typical rookie mistake: they had carefully designed a back-end database to efficiently and securely store data. Then they had created a front end that was simply a graphical add-on that mirrored the back end. The data in the tables may have been accessible, but not in any usable way.

As an introduction to the programming tools available for Microsoft Access, we began creating an interface that mirrored the tasks users needed help with, rather than the design of the database itself. We started with a blank form and named it after the task it was to accomplish: Receiving. We then worked through the steps in this task.

Assuming that the products to be received had been verified by way of a purchase order number and that the quantity was correct, the first step was to enter or find the ProductID number. We placed an unbound text box named ProductID on the blank form with an appropriate label next to it.

Assuming that the ProductID was valid, the next task was to enter the quantity received. We added an unbound text box named QtyReceived to the form with an appropriate label beside it.

Finally, the updates had to be made to the database. For this simple example, we added a button with a caption such as “Process” or “Receive Product” to the form. Clicking this button would call some code in a module that would find the record for the product and update the QtyOnHand and QtyOnOrder fields.

Takeaway: How the consultant chooses to accomplish this is not that important. (In this particular course, I used this as an opportunity to introduce Data Access objects.) Whatever technology the consultant uses to accomplish the processing, it should be modular—that is, the code that accomplishes the receiving process should be kept separate from the form itself so that it can be easily found and maintained as required in the future. The methods in the code should mirror the tasks the user must perform.

Make the task easier
Once the steps in the task were established, the students worked on simplifying each step. For example, rather than using a simple text box control in which the ProductID was entered, the students created a look-ahead combo box that found the number as the user typed or displayed a drop-down list of sorted numbers with a description alongside. Considerations included how many numbers the user might be forced to scroll through and how to handle numbers that were not found.

Could the layout of the controls on the screen be fine-tuned to make the steps easier to follow? For example, should the controls be arranged in a top-to-bottom or left-to-right order that suggested the sequence in which they should be used? This may seem like a no-brainer until you consider cultural differences. A number of written languages read from right to left, so the left-to-right English language model may not be as obvious to your users as it may seem to you.

Are labels easily read in the environment where the application will be used? Remember that not all computers reside on desktops with ideal lighting. Are the fonts a size and style that are available on the target machine or are they simply the personal favorites of the developer?

Is a simple list of instructions displayed on the screen so that going somewhere else, such as Help, for answers is not often necessary? If the task is part of an entry-level position’s responsibilities, new and inexperienced users may need constant reassurance.

Takeaway: The consultant’s goal is to create applications that make the user’s tasks easier. If the application proves too difficult to use, it becomes a task in itself and will not be used. Logic tells us that creating applications that are difficult to use is not the way to enhance your reputation with your clients and win repeat business.

Make the task error-proof
At each step in the task, I made the students go through the exercise of stopping and asking, “What could go wrong?”

What if the ProductID could not be found? A simple typographical error might prevent the receiver from finding the product. Is it possible to find a product by description? Is an image of each product available for comparison with a physical example?

What if the user makes a typing error in the quantity text box? Could code be written to check for a numeric entry and warn the user, or, better yet, should a custom control be designed that allowed only positive integer entries? What if the quantity received does not match the quantity on the purchase order?

What happens if the user clicks the Receive Product button before entering all the required information? Should the button be disabled until all the information is in place?

What happens if the user clicks the Receive Product button more than once, either accidentally or because he is not sure if processing has taken place? Should the button be disabled after the first click? Perhaps a message should be displayed telling the user that his task has been completed. Perhaps the table needs a time stamp field indicating the time and date that the record was last updated and by whom, to reassure the user that the task completed successfully.

What if an error occurs during processing? Should the update be handled as a transaction so that it can be rolled back if necessary?

Takeaway: There are a number of ways that consultants can handle errors caused by users. One way is to notify the users that they have made an error and then tell them how to correct it. This is equivalent to controlling quality by throwing out anything that doesn’t meet certain standards. A much smarter way to handle errors in your applications is to make them impossible to commit in the first place. This is equivalent to controlling quality by putting procedures in place that ensure that nothing will be made that does not meet certain standards.

The bigger picture
After this bout of microthinking, the students were asked to take a step or two back and see where this simple screen they were designing fit in with the other tasks users needed to perform. Would it be easier for the receiver to fill out a list of products received and quantities that matched a particular purchase order or shipment and then process them all as a batch? Were the individual parts of the receiving process separate enough that the application could scale up to a larger database or scale down onto a hand-held device in the future? How much would have to be rewritten if the application had to run in a browser instead of Microsoft Access?

At the school where I worked, Microsoft Access was part of the curriculum. I saw my job when teaching the course not as helping the students learn how to create Access applications, but rather helping the students learn how to use Access to create applications. There is a subtle but important difference.

The first approach centered on using the wizards to get something working quickly and then expecting users to adapt to the new application by “learning Access.” This approach centered on the software product.

The second approach made Access just one more tool in the arsenal of skills the students would leave with and produced applications in which, rather than users learning the application, the developers—in this case, the students—learned the tasks the users had to accomplish and built an application around those tasks.

Takeaway: Consultants must take the time to learn and understand the tasks their users face each day. The applications that consultants create must help users do their tasks more effectively. If your clients see results immediately in higher productivity, better customer service, reduced costs, and happier employees, they will be back for more.