Top 10 Productivity Tips for Microsoft Access 2010

I've been working with Access 2010 now for about 6 months and I'm finally getting more productive. I wanted to come up with 10 "Productivivity Tips" to speed you on your learning curve, but as I documented the things that I've learned, they came out more like "annoyances". Either way, I hope you find them helpful.

1) Hide/Show the Ribbon

Unless you've adopted Access 2007, you'll find the Ribbon to be a bit irritating. Menu options aren't where you left them in Access 2003. Moreover, the thing takes up so much space it infringes on your work area. You can "minimize" the ribbon quite simply by double-clicking on one of the tabs and Access remembers your selection for future sessions.

2) Get used to Right-Clicking

Maybe it's just me but the simplest things have become hard. The old menu options for going "into design mode" still exist, but if you've minimized the ribbon (see tip # 1), then it's going to take an extra click ... or two to get there. In Access 2003, one click could toggle you between Form View and Design View. Now, after showing the ribbon (first click), the default view is "Layout View" so you must click (second click) on the drop down arrow to see the Design View option (third click).

I suppose I'm just lazy but I've taken to using the Right-Click menu. You can right-click a form, report or query to expose the View menu. That makes it a consistent 2-click process. However, there's another discrete little toolbar in the lower right corner that exposes the available View Menu options. This is a "one-click" solution I can live with, once I get used to looking there for it.

As for opening things in the Access Navigation bar at the left, the only two ways I know of is to double click the object (which opens it) or to right-click and choose the type of view you wish. Like I said, get used to right-clicking.

3) Finding a Code Window

Maybe this is just my frustration with the menu showing again but I used to open the code window for a form or report with the menu-toolbar option. It's more complicated now because if, while working on a form in design view, you end up with the ribbon on the Create tab, the option labeled "Module" does NOT open the module behind your form but, as the name suggests, creates a NEW module.

While this is logical, it requires a diligence that I'm not accustomed to. Not only do I have to look for menu options but now I have to pay attention to which menu tab is active.

To bypass this potential hazard, I now simply press Ctl+G to open the Immediate Window thereby making the VBA editing environment available. I still have to locate my form in the object browser to load that particular code module, but at least I've stopped creating blank modules which I must later delete.

4) Home, Create, Design

While we're on the subject, the three tabs that are most important to get to know are the Home, Create and Design tabs and of these, Home is the least useful. The Create tab contains all the options for making new tables, queries, forms, etc. The Design tab allows you to work with these newly created objects, turning a SELECT query into an UPDATE query and dropping controls on your form. Once you've got controls, the Home tab is where you go to apply text formatting. It's also where the usual run-time options live, such as sorting a datasheet or filtering a form.

5) Use the Quick Access Toolbar

Things like printing are also, in my opinion, too hard. What used to be one click is now three. That is, unless you add the print or print preview options to the Quick Access Toolbar. As a rule, until one gets used to the menu options, it's a good idea to overload this toolbar with things you can't easily find in the menus.

6) Try out Navigation Forms

Navigation forms provide an alternative to the Switchboard of previous Access versions. The menu option on the Create tab (shown below) will guide you through the process and it's very intuitive. In the beta, the display and positioning of buttons was glitchy but I haven't noticed a problem in production code.

7) Output Reports to PDF

For some time I've been using Cute-PDF to output my invoices and other reports to PDF. PDF output support is now incorporated into Access 2010 though I believe it is more easily implemented through VBA code. From the menu system, it takes about 5 clicks to get it done as can be seen in the screen shot below. This is one of those good candidates for the Quick Access toolbar, which turns it into a single click.

=> File Menu => Save and Publish => Save Object As => PDF or XPS => Save As

8) Just Enable All Macros

If you, like me, hate that security warning message, then just enable all macros. I know it says it's not recommended, but why? Because if you're not careful, code might execute in this file? I hope so ... that's why I wrote it. To disable the warning you need to enable macros. Find the Access Options from the File menu and navigate to the Trust Center options. Select the Enable All Macros option and save.

9) Take Advantage of Query Intellisense

Yes, Intellisense is now available while creating queries. It shows up in the Criteria row and the Update To row for an UPDATE query. Auto-complete will assist you in getting table and column names correct. For example, if you start typing your table.column name, a list pops up from which you can select. Functions are also exposed in the Intellisense auto-complete list.

For years, I've been advocating using SET and GET functions to save and recall values for filtering data. For example, a report might be launched from a form that collects an ID or text code. You store this value using a SET function. Then the query behind the report uses a GET function call to recall the value. In Access 2010, your functions are exposed with Intellisense from the criteria row, as shown in the screen shot below.

The same is true for built-in functions so regardless of whether you use my SET-GET paradigm or not, this feature may come in handy. On the other hand, you now have to be careful when typing so that Intellisense doesn't misunderstand and auto-complete something you didn't intend.

10) Look to the Experts for More Tips

As I work with Access 2010, I'm constantly on the lookout for things that will save me time. I recently learned that some old friends from the Access newsgroups, Arvin Meyer and Douglas J. Steele, have written a book titled Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs. The book is published by Wiley and
these authors are two of the most respected MVPs in the Access community. From the published table of contents it's clear they cover topics that apply to previous versions of Access as well as Access 2010 specific features. If you're hungry for more tips, it's the place to look.