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 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
DSum() - Sum values 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
DCount() - Count values from a table/query based on some
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:
= " & [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
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.
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.
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
you need to specify the field names on your form
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.
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
- 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
- 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
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
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
|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
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
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.