Access Forms (continued)

Whether you are starting a new database, or renovating an old one, Access Form Design is one of the most entertaining and also important steps.  Do it right, and you have a highly functional, productive application with a professional look and feel to hand to your users. 


In the course of daily business, we see a lot of our customer’s Microsoft Access forms. Some are great, and some are stuck in 1998 design paradigms. This article gives you ideas for key ways to improve your MS Access forms in a few minutes.

5	User-centric instead of Data-centric   Access form showing event information in the parent, and related equipment in the subform Design paradigms of the 1980's and 1990's were largely data-centric.  In some applications, users were forced to navigate to one screen to add a record, and another to change existing records.  Displaying a single record was preferred to reduce load on the application, desktop, mainframe, server, etc.  Gladly, those days are behind us.  But even still, the Microsoft Access form design tools will guide you to a data-centric design mode.  Here are some ways you can make your forms more user-centric so that your users are productive. Access Subforms Experienced Access users know that creating a form that uses multiple tables as the record source can lead you to trouble.  Access can find it difficult to know how to insert new data into multiple tables unless you explicitly define the joins between the tables.  The easier way is to create a subform that is linked to the "parent" form such that when you navigate from one record to another in the parent form, the subform will change to show only related records.  Collect information from other sources Why force users to navigate away from their current form to look something up when you can present it in their primary workspace?  If you can anticipate what your users might be interested to know about a certain record, Access provides a simple set of tools to fetch data from other tables or queries and show it directly on your form.  Unfortunately, the name isn't necessarily an obvious Google search: Domain Aggregate Functions.  Essentially, a fancy way to say, "let me collect information from another table/query and summarize it."  The most commonly used are: DSum() - Sum values from a table/query based on some criteria DCount() - Count values from a table/query based on some criteria DAvg() - Average values from a table/query based on some criteria DLookup() - Get a value from a table/query based on some criteria There are more functions to get the maximum, minimum, standard deviation, first, or last values.  The important thing to know is that you can add one of these to your form without much trouble.  Add a textbox to your form, and in the toolbar/ribbon select the "Expression Builder" option (a little magic wand icon).  From here, you can add the function you want to use. Don't be frightened by the cryptic terminology.  Replace <<expr>> with the field name you want to sum/count/average/etc.  Replace <<domain>> with the table or query name, and finally <<criteria>> with any filter criteria you need to apply.  Say you want to sum the revenue from a customer.  Your text box would look like this: =DSum("Revenue","tblOrders","[fk_CustomerID] = " & [CustomerID]) In the above example, we're summing the Revenue column in the table "tblOrders," but only if the customer ID indicator in tblOrders matches the customer ID of the current record on this form.  If you see "#Error" you'll know something is wrong.  Usually that the field name you specified doesn't exist, or the table/query name is slightly off.
6	Use tab controls to deemphasize non-critical information Use the Access tab control to group information Notice in the screen at right, we have several tabs.  The tab control in Access is a fantastic way to deemphasize less important information, reduce the size your form will need to take up on screen, and divide your forms into logical sections.  The tab control lets you add as many tabs as you'd like. Tab controls can be a bit tricky to learn.  You'll have to think of it as a container of sorts, where it can only shrink to be as small as the largest thing inside.  So if you find your tab control can't shrink in size, check the tabs to see if there is something "preventing" it from shrinking.
7	Inline calculations Similar to the tip in #5 above, you can use inline calculations in unbound textboxes on your forms to show users important information without waiting for a report or query.  Access is much like Excel, in fact.  Except instead of specifying cell names  =Sum(A2:A5)   you need to specify the field names on your form =[Quantity]*[Unit-Price]. Access will support any standard mathematic operation you want to apply. The textbox will not be editable, of course. And as you enter data into your fields, the text will refresh to show the results of the calculation for that record in the same manner that Excel does when you update cells.
8	Form window behaviors When you create a new form in Access, the default properties are generally not optimal for user-friendly interaction.  Open the Properties dialog box for your form and get to know these key properties: Popup (other tab) Set this to "Yes" and your form will remain on top of other forms when open.  It will retain the dimensions you set for it even when other forms are maximized or minimized. Auto Center (format tab) Set this to "Yes" and your form will open centered on screen when in popup or dialog mode.  This will not apply if you are using Access 2007/2010's tabbed window interface and the form's Popup property is set to "No." Record Selectors (format tab) This property is set to "Yes" by default, and shows the gray bar to the left of each record.  If you have a form that shows a single record at a time, you will usually want to set this to "No." Navigation Buttons (format tab) Set this to "No" and the navigation bar at the bottom of the screen will disappear.   . Cycle  (other tab) This property is set to "All Records" by default.  This means when your user reaches the last field on your form, if they hit the tab key, they will be taken to the next record, or a new record.  In cases where you don't want them to leave the current record, change this to "Current Record."
9	Summary and Detail Forms Access supports several different form views, the most common being the Single Form and Continuous Form view.  These are really detail and summary forms, respectively.  The Single Form view lets you see only one record at a time, while the Continuous Form view presents a list of multiple records in rows.  Unfortunately, the Continuous Form seems to get limited attention by the Access designer.  Many prefer to use the Single Form view to display records, and it definitely has it's place.  Putting a dropdown box on a detail form to let users navigate between records can be cumbersome to create, and not the ideal way to present information. Design your forms knowing the key task the user will be carrying out.  When needed, you may need to create multiple versions/views of the same information to fit your user's task.  For example, a summary form would be ideal to browse a list of products, whereas a detail form might be appropriate for filling out a new order. A detail form:  Ideal for diving into the specifics of a record A summary form: Great for fast access to high level information, or to browse for the record(s) you need to work with
10	Load forms with context In the prior tip, we talked about using summary or detail forms.  Lucking, you can use both in tandem to great effect.  Say your users will need to be able to browse a list of new prospects each day, and use some summary information to decide who to call upon.  Once they are on the call, they would prefer to see all the details about that prospect in a single consolidated view.  Why not pop up a detail form from the summary screen?  With Access, you can use the Command Button wizard to do just that.  Drag a new button into your form.  In the wizard, choose the "Form Operations" option, then "Open Form."  Next you'll be asked what form to open.  In the screen after that, be sure to indicate "Open the form and find specific data."  Finally, you'll need to tell Access how to find the related record.  In our example, you'd need to indicate what the unique field is that will help Access locate the same record in your detail popup form. In the example above, we're using Customer-Name.  That will work fine as long as Customer-Name is always unique.  Otherwise, we recommend using the numeric ID field.  Also note that you can make your detail form a popup form so that it will display over the summary form.  See tip #8 for more on how to set the popup property.