Software

10 things that are [still] broken in Access 2007... and maybe in 2010

In Access 2007, some things just don't work -- and a few problems appear to be persisting in the 2010 Technical Preview.

In Access 2007, some things just don't work -- and a few problems appear to be persisting in the 2010 Technical Preview.


If you've made the switch to Access 2007, you know that a number of things don't work as you expect. In fact, a few things don't work at all. Although there are too many issues to list here, we'll look at some of the problems that might be troublesome to most users at some time or another -- and we'll note where things may remain problematic in Access 2010.

Note: This is an update to an article that originally appeared in June 2008. It's also available as a PDF download.

1: Access 2007 loses printer specification

Access associates reports with the default printer when you create it. However, you can save a non-default printer to a report via the Page Setup dialog box. Unfortunately, Access 2007 loses that setting and sends the report to the default printer. Interestingly, if the non-default printer was set using a previous version, it still works. Download and install the hotfix.

2: Where oh where has my Spell Check gone?

The decision to make Spell Check invisible probably annoys me more than any other change. Spell Check isn't exactly broken -- it's just hard to find. If you want to go to the trouble of working through the Ribbon, click the Home tab and choose Spelling from the Records group. (What a pain!) It's still there in Access 2010's most recent Technical Preview. But even after you do find it, it isn't always available. At least you can still press F7 to check spelling. Or you might want to add Spell Check to the Quick Access toolbar to keep it handy:

  1. Click the Office button, click Access Options (at the bottom right), and choose Customize. In Office 2010, click the Office tab, click Options under the Access item in the left pane, and then select Quick Access Toolbar in the left pane.
  2. Select popular Commands from the Choose Commands from list.
  3. Scroll down and select Spelling, as shown in Figure A.
  4. Click Add and click OK.

Now the Spell Check feature is readily available via the Quick Access Toolbar, shown in Figure B.

Figure A

Figure B

Even with the feature accessible, it won't work all the time. If you save and close an object, Spell Check is often available when you reopen the object. That shouldn't be necessary, but I've run into it several times.

3: Some shortcut menus are broken

Subform shortcut menus, also known as context menus, don't work in Access 2007. In earlier versions, a right-click displays a context-sensitive menu. Download and install the hotfix to get subform shortcut menus back. Shortcut menus seem to be working fine in Access 2010 subforms.

4: There's no more single-click browsing

Now here's one that's sure to confuse most users. In earlier versions, you could hold down the Previous and Next navigation buttons in a form's Navigation toolbar to quickly cycle through all the records. This behavior is similar to clicking the First or Last button, but you can actually see Access cycling through the records. This behavior is totally gone in Access 2007 and there's no fix for it. It's just gone -- and for good it seems. It hasn't reappeared with any updates.

5: Date Picker enabled, despite setting

Access 2007 automatically adds the Date Picker to Date/Time controls, making it easier for users to enter and change date values. In Design view, you can set the Date/Time field's Show Date Picker property to No to inhibit this enhancement. In other words, if you set this property to No, none of the inheriting objects will display the Date Picker -- except it doesn't work consistently. Specifically, queries still show the Date Picker. There's no fix for it, and my guess is that it will stay broken, since the problem is still with us after more than a year of updates and fixes. .It persists in Access 2010 Technical Preview as well.

6: When is old new?

When you edit a value, that control's OldValue property equals the control's previous value until you save the record. In other words, OldValue equals the value that Access restores if you undo a change (by pressing Ctrl + Z or choosing Undo from the Edit menu).

Access 2007's combo box control has a bug in this area: A bound combo's OldValue property will return the current value and not the old value when the combo is bound to 2007's new multivalue field.

This bug is going to be a serious problem if you need this functionality. As far as I know, there's no fix. My best advice is to capture the current value as a static variable instead of using OldValue.

7: Rich text is unavailable in subforms

Microsoft has added rich text capability to 2007 text controls -- sometimes. Okay, the truth is, the capability is there, but you might not know how to put it to work because the Format tab is unavailable in Form View. However, you can use the tools in the Font group -- sometimes. This group is disabled for subforms. What all that means is you can't assign formats to a rich text control in a subform using a tool or menu. You can, however, highlight the text and use keyboard shortcuts, such as Ctrl + B for bold and Ctrl + U for underscore. Since this is more a result of tool placement and not an actual bug, I doubt Microsoft will fix it.

8: Memo fields are flaky

Memo fields have always been a bit buggy if you did anything besides enter lots of characters. 2007 memo fields have two problems:

  • If you change the Append Only property to No, Access 2007 wipes out the column's history. Don't expect a fix for this because this is by design and not a bug, according to Microsoft.
  • Running a query that contains a GROUP BY clause on a Memo field against a list in Windows SharePoint Services may corrupt the data in the Memo field. For now, Microsoft suggests you use a Text field instead of a Memo field, which doesn't really help if you actually need a Memo field. And if you don't need a Memo field, why would you be using one?

9: It just doesn't add up!

For several versions, you've been able to add fields in Datasheet view. In Access 2007 (and Access 2010 Technical Preview), a new table created in this manner comes complete with an ID field, and as you add fields to the new table, Access increments the first ID value by 1. Surprise! For instance, if you add three fields before you save the table, the ID value is 3, as shown in Figure C.

Figure C

There are three simple workarounds:

  • Add a second row before you add fields to the table.
  • Save the table before you add a field to the table.
  • Avoid creating tables in Datasheet view (the best solution).

10: Layout view crashes Access 2007

If you change a control's Control Source property in Layout view, Access 2007 may close unexpectedly (crash!). You may or may not get one of the following error messages, and neither really helps:

  • Microsoft Office Access has stopped working.
  • Microsoft Office Access has encountered a problem and needs to close. We are sorry for the inconvenience.

For now, the simplest workaround is to change the Control Source property in Design view.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

11 comments
roger
roger

I have several data entry forms which, for convenience, I want to display as datasheets. Even if I set datasheet as the default in properties, however, the form doesn't open as a datasheet - I can get a continuous form but it's not really what want because it is so much more bulky in appearance than the datasheet format. Can anyone help or suggest a workaround

ThisSteve
ThisSteve

Another thing that has happened to me recently is that form buttons suddenly lose their Even Procedure connections in the Properties menu. The macro's are still there, and all that is required to fix is to select the button on the form, go into Properties and choose [Event Procedure] for the correct event and they work fine.

daniel
daniel

About 10 days ago I installed a new hotfix that finally show the object Description in the Navigation Pane. It took about two years to come up with a stable solution for this. Unfortunately it is not as efficient as in 2003 where it was possible to sort by Description column. I am a trainer and I often suggested users that are allowed to create their own queries and reports to prefix the Description with their initials. For instance "DLa: This query display the ...". That way it was easy to sort by the description column so that everybody saw their objects grouped. Forget about this simple thing in 2007!!!

rtrujillo@metalsurfaces.
rtrujillo@metalsurfaces.

For some older Access databases, you need to use: You can create a desktop shortcut with a target of "path to 2007 msaccess.exe" "path to 2003 mdb" /wrkgrp "path to secure mdw" Why didn't they keep it the old way? Now, we have to use the "Command Prompt."

wfsaccounting
wfsaccounting

I wish I could use that Hotfix for page setup in Access 2003. I'm having a terrrible time with it not retaining the page size in some reports.

body_for_life2100
body_for_life2100

'Right Click' filter does not function correctly. In Access 2003, you could right click in a field and effectively query the data. It even allowed you to use wildcards and search more than one criteria (e.g. *seven* or *hat* would return any records that had the words 'seven' or 'hat'). In Access '07, not only is the dropdown menu completely different (which wouldn't be so bad if the function of '03' was still there), but the functionality has been taken away.

mike.hall
mike.hall

Another frustrating thing on Access 2007 (Sp2) is the fact that an object (form or report) suddenly becomes corrupted. ...A Macro fails to run error...etc The resolution is simply to copy the object, rename the "corrupt" one and then rename the copied object back to the original name, then all works. This happens on any random object , not necessarily the one undergoing design changes. a Compact and Repair doesn't solve the problem. I havent managed to find any data on this problem on the net.

lwentworth
lwentworth

I'm finding I need Help with Help. Is it just me or is everyone else finding it useless?

Data Nut
Data Nut

Some of these are minor issues, but annoying nonetheless... 1. Displaying database name in the title bar: Sometimes you need to have multiple databases open. DB name disappears from title bar. You can go into Access Options and click ok for it to reappear, but then it disappears again... 2. Copy and paste broken: Our IS dept. updated something a few months back, and now I can't do a regular copy & paste from Excel to Access (sometimes it's just not worth writing a query). Now I have to do a paste special and pick text. It's pretty bad when two MS products can play well together. 3. Losing the ability to Alt-Tab between objects. I'm a keyboard person. I use Ctrl-F6, etc. but it's very awkward. 4. Lame help: ok, this isn't new with 2007... Is it so hard to provide more info? If a query bombs or one row of data won't load, to actually display which part of query, field and/or data element is causing the issue and not just give a vague error message? Or at least when an error message appears and the Help button is made available, that the help functionality be contextual to the error? 5. Record scrolling in forms: It was really handy in previous versions - why take it away? There's more to mention, those are just off the top of my head. OTOH, I'm actually getting used to the ribbon...

zenoscope
zenoscope

I had to do Access 2003 as part of my IT course, and the help was all but useless. I was swearing at it every 5 minutes.

Editor's Picks