Access+Tips+and+Avoiding+Common+Mistakes

Access forms are extremely powerful. However, the developer can easily make mistakes to cause a form to behave incorrectly or poorly. Here’s a simple checklist of common problems with form designs. Developers should verify these problems do not exist prior to finalizing their forms. Check these items on your forms before you distribute them: > Verify that the tables and queries used by the form are valid. This includes checking the form’s record source and the row source of all combo boxes and list boxes. > Without setting the form caption property, the name of the form appears which may not be what you want your users to see. > Make sure that what the user sees is accurate and spelled correctly. In addition to the labels, the user also sees validation text and control tip text. A common mistake occurs when a control with these properties is copied, and only the label and control source are changed. In this case, other properties such as validation rule, input mask, default value, format, etc. may also be wrong. > Make sure accelerator keys (hotkeys) are not duplicated. Accelerator keys allow users to use the Alt key and letter to jump to a control. They are set by using the “&” character in captions followed by the letter. For instance, a Help button may have an “H” hotkey and appear as “Help” with its caption “&Help”. A common mistake is to assign the same key to multiple controls on the form. Test your form by using each hotkey twice and verify it doesn’t go to more than one control. > If it doesn’t have an OnClick event, the button should be removed, made invisible, or fixed. Sometimes the OnClick event is accidentally assigned to the wrong event, such as the OnDblClick event. Alternatively, an OnClick event is not necessary if it has a Hyperlink. > Make sure the tab order of the controls is correct. By default, the tab order should go left to right, top to bottom. This is what users expect. If your form does not behave this way, it exhibits unexpected (unintuitive) behavior which can be frustrating for your users. The default tab order can be easily set under the View, Tab Order menu. For situations where you want the tab order to behave differently (for instance, you may want it to go down columns for an option group), you can change this, but at least you’re making a deliberate decision to deviate from the default order. > Views Allowed should be explicitly assigned to only display the way you want your users to see the form. Options include viewing the form in form view, datasheet view, and in Access 2002, PivotChart and PivotTable views. Without the undesirable options turned off, a user can change the view of the form by right clicking on the form and switching with the shortcut menu. > By default, the Shortcut menu property is set to Yes, and no shortcut menu is specified. This means Access’ default menu appears. If this is not desired, set this property to No. > If you are using a help file, make sure the help file name and help context ID are correct. > Make sure every event that has [Event Procedure] assigned actually has an event procedure defined. A common mistake is to assign the event without actually clicking through to write the code for it. This can also happen if you rename a control and forget to rename the event procedures tied to the old name. > Make sure the AutoCenter property is set to Yes. AutoCenter ensures that when your form is opened, it opens in the center of the screen regardless of where you placed it when you saved it on your machine, or what the user’s screen resolution is. > Make sure the AutoResize property is set to Yes. AutoResize automatically adjusts the display of your form to the size you designed it. If this property is set to No, the form appears in the way you last saved it, which can easily be too big or small. > Combo boxes should have their LimitToList property set to Yes so users can only enter values in the list. If this is set to No, users can enter any value. To support users adding new values to the list, set the LimitToList property to Yes, and use the NotInList event to handle the new values. > The drop down from a combo box should be greater than the default of 8 (16 for Access 2007), so that if your list is longer, more can be shown without the user being forced to scroll through them. We recommend 25 or more. > AutoExpand simplifies data entry by auto-filling the remainder of the selection based on the first few letters the user types. > The AllowDesignChanges property should not be All Views, but rather Design View Only. When set to All Views, users can change the design even though they are not in design view. This is something one rarely wants them to do. In fact, if the form property sheet is open the last time a form was designed, when a form appears with this property set to All Views, the property sheet also appears which is very confusing to end-users. > For the BackColor property of form sections and controls, use the system gray color (-2147483633) rather than the default gray (12632256). In recent versions of Windows (Windows Me, 2000, XP), there's a slight change in the way gray is displayed, and the older gray appears darker than it should. Mixing these two values for older Windows versions is not a problem but you can see a difference on new versions. For Access 2007, see the tips below. Making sure forms are properly designed takes time and effort to verify all the things that could go wrong. This is especially difficult if you are taking over someone else’s work and/or if there are a large number of forms. Fortunately, there are some tools which can address some of the pitfalls listed here by examining all your forms and highlighting the problems. The first is [|Total Access Analyzer]  from FMS, Inc. Total Access Analyzer is the most popular Access add-in and offers comprehensive database analysis that covers much more than forms. With the exception of verifying the accuracy of what users see, the form analysis detects all the other pitfalls listed here plus many other areas such as module code analysis, procedure flow diagrams, cross-reference documentation and verification, and muchThe second tool, [|Total Access Speller] , is also from FMS, Inc. Total Access Speller is a spell-checker that spell checks what the user sees on your forms, reports, and other objects. Easily verify all the captions, validation text, and control tips are properly spelled. For significantly enhancing the user interface of forms, consider [|Total Access Components] , a collection of 29 ActiveX controls designed specifically for Access to make your forms dazzle. Add animation, rotated text, fancy borders, etc. [|Tips for designing forms in Access 2007]  Developing an application takes a lot of effort. Don’t let simple mistakes ruin your application and embarrass you in front of your boss and/or clients. Even a simple typo can ruin the trust a user has for your application, work, and attention to detail. It usually only takes a few minutes per form to ensure those who use it don’t see obvious problems. This is a wise investment of your time for your career and reputation. Use this checklist as the basis of your quality assurance process for your forms. If you find other problems, add them to the list so you can avoid them in the future. Making mistakes is okay. Making the same mistake more than once means you’re not learning. Good luck.
 * Microsoft Access Form Tips and Avoiding Common Mistakes **
 * Introduction **
 * <span style="color: #3366ff; font-family: 'Verdana','sans-serif'; font-size: 12pt; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Common Mistakes **
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Verify Data Sources **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Use Captions **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Spell Check **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Avoid Duplicate Hotkeys **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Command Buttons Must have an OnClick Event **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Verify Tab Order **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Explicitly Set the Allowed Views **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Verify Shortcut Menu Setting **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Verify Help File Context IDs **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Avoid Missing Code **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Set AutoCenter to Yes **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Set Resize Property to Yes **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Set Combo Boxes LimitToList to Yes **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Increase Number of Rows Displayed for Combo Boxes **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Set Combo Box AutoExpand Property to Yes **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Set AllowDesignChanges Property to Design View Only **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Use System Colors **<span style="font-family: 'Verdana','sans-serif'; font-size: 10pt; line-height: 120%; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">
 * <span style="color: #3366ff; font-family: 'Verdana','sans-serif'; font-size: 12pt; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Additional Resources **
 * <span style="color: #3366ff; font-family: 'Verdana','sans-serif'; font-size: 12pt; mso-bidi-font-family: 'Times New Roman'; mso-fareast-font-family: 'Times New Roman';">Conclusion **

Info273 Prof. Colucci