User-centric instead of Data-centric
Access form showing event information in the parent, and related equipment in the subform
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 criteriaThere 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.
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
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.
Use tab controls to deemphasize non-critical information
Use the Access tab control to group information
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.
Inline calculationsSimilar 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.
Form window behaviorsWhen 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:
Summary and Detail FormsAccess 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.
Load forms with contextIn 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.