There are lots of false assumptions floating around the world of Access development. Susan Harkins has rounded up some of the worst object design mistakes that developers make — and offers a best practice to counter each one.
When you develop an Access application, normalizing your data and creating relationships is just the beginning. The next step is adding the UI objects that users will interact with and the queries that will populate those objects. There isn't a set of rules, like normalization rules, to guide you during this stage. It's easy to make assumptions that will haunt you sooner or later... usually both. The following are 10 assumptions you shouldn't make when adding queries, forms, and reports to your database application.
1: Object names don't really matter
Object names indicate the object's purpose and often provide an easy way to organize objects in a logical manner. Object names can also help a developer work a bit more efficiently. A descriptive name, such as Employees, is adequate for users. But it doesn't help the developer much. Some developers use a prefix or tag to identify objects by type. This practice can come in handy during programming. For example, you can easily cycle through collections and find all subforms by checking the name property for an appropriate prefix or tag. The details are up to you; I don't advocate a one-size-fits-all convention. Find one that helps you work efficiently and apply it consistently.
2: Tables are for storing data, period
Tables are an interface used to display data, but there's more to them than data. Field properties, such as data type and field size, help you maintain data integrity. An input mask provides a pattern for data input, and a default value can reduce input. Indexing the appropriate fields will help performance. Even the field description, which many developers ignore, is helpful. Access displays the description text in the status bar, providing a helpful hint to users. Setting the Required property to Yes eliminates the need to deal with null values (although null values are acceptable values). In addition, forms and reports inherit these attributes and properties. That means you can set them once at the table level instead of repeatedly throughout all your UI objects.
3: You can just replicate your paper forms
Paper forms are for paper — not your Access forms. Trying to replicate paper forms in Access might work, and it might not. You could end up making a lot of extra work for yourself. Let the data and the process dictate form design.
4: You need just one big form
Access will let you build a form that's 22 inches wide, but how are you going to display it? Too much of a good thing is a nuisance. When designing forms, break processes down into small tasks and use forms to perform them. Don't try to use one form to do it all. The larger the form, the slower it will perform, and it's sure to overwhelm the users.
5: Wizards produce reports you can use as-is
Access wizards are pretty slick. Most of them will save you time, prevent errors, and produce an acceptable object. Unfortunately, the results of the report wizards are ugly. These wizards provide a nice start, but if you distribute one of these reports without some serious tweaks, you'll look incompetent. Just don't do it.
6: Wizards creates subforms, so go ahead and use them
If you run a form wizard on a multi-table query, the wizard will most likely generate a main/subform arrangement to display related data. The arrangement is sound, but controls load faster than subforms. Often, list controls are a better choice for displaying related data than subforms. Don't settle.
7: Users don't need to know what's going on
One of the most grievous mistakes developers can make is to ignore the user. Impatient users are apt to do things they shouldn't. When they don't know what else to do, they press [Esc], [Enter], [F1], and even [Ctrl][Alt][Delete] — oops! Give users some kind of visual feedback. Let them know that a task has been successfully completed (or not). If the latter, tell them what they need to do to continue. When users need to wait for a task to complete, show them a simple meter or status message. Don't leave them hanging.
8: Basing forms and reports on a table is just fine
Base your forms, subforms, reports, and subreports on queries rather than tables. You can easily restrict queries to return just the fields and records you really need to populate the object. In addition, you can easily adjust the underlying query to adapt to changing requirements.
9: Users should ignore empty reports
Don't display empty reports. Users will assume something's wrong. It might not occur to them that there's just no data to report. Use the report object's NoData event procedure to display an explanatory message and cancel the report as follows:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "The report has no data.", vbOKOnly + vbInformation
Cancel = TrueEnd Sub
10: You'll remember what you did
Sure you will. Anytime you strategize and choose between possible solutions, consider documenting that decision. You don't have to write a book; most of what you do is self-explanatory. If you're writing code, you can add a few comments, but there's no such vehicle for objects. Consider adding an invisible text control (visible in Design view only) with a short explanation — at least leave the next guy a few breadcrumbs. (That next guy might be you!)