Access all areas?

In forms or datasheet views, Access 2007 offers much better sorting and filtering, which mirror the new facilities in Excel 2007. Both sorting and filtering take account of the type of data in a field to offer appropriate options. Date fields, for instance, offer to sort “Oldest to Newest” and filter based on month name, quarters and so on, while Text fields allow filtering by “Contains” or “Begins With”, as well as the standard comparisons like “Equals”.

Layout view

A halfway house between running a form and Design view, the new Layout view lets you edit various things about a form such as the position and formatting of fields, while still seeing it populated with live data. This is useful for checking items such as the width of text in combo boxes, but otherwise it seems to restrict what you can do compared to Design view. On the plus side, adding a field to a form is just a matter of dragging it from the Field List onto the form, and Access has a good stab at sensibly placing and sizing the new field and its label – the other fields and labels get shuffled down to fit the newcomer (although it may annoy people trying to design complex layouts).

A new form type in Access 2007 is the Split Form, which has both a traditional form layout and a datasheet layout containing the same data on the same form. You can place this Datasheet view above, below, left or right of the main form, with a splitter bar that allows end users to control their relative sizes. Controls can now be anchored to the edges of the form so they resize as the form resizes, similar to the anchoring and docking in Visual Studio (although described in different terminology). This feature is necessary because of the move to a Workspace environment in which tools such as the Navigator can be popped out or rolled away, changing the shape and size of the working area where your forms are displayed. Writing VBA code to cope with resizing forms was time consuming, difficult and prone to errors, and setting Anchoring properties is much quicker, easier and more logical.

Controls and data types

In the “about time too” category, date fields now sprout a drop-down-calendar button so you can pick a date rather than having to type it in. Memo fields now have a TextFormat property, which you can set to allow Rich Text formatting, and the text is then stored with HTML tags for bold, alignment and other attributes. When entering text into a Rich Text Memo field, you can use standard keystrokes like for bold, or use a mini-toolbar that fades in whenever you select some text – but unless you dock your form to the main window, you can’t use the main formatting tools on the Home tab of the Ribbon. Many of the sample templates use pop-up forms that can’t use the font and rich text controls on the Ribbon.

For better integration with SharePoint, Access now includes a new data type, the multivalued field. Like a category field in Outlook, such a field can hold one or more items from a list of permissible values, so in a table of dogs the “Colours” field for one dog might contain the values “Black; Brown; White”. This is a poor man’s way to avoid having to define a look-up table of possible colours along with a many-to-many relationship between that table and the table of dogs. Quick and dirty it may be, but it isn’t usually good database design.

And at last, you can select system colours for forms and controls from a drop-down list. This list is long, and you don’t get any colour swatches to show the current settings, but at least you no longer have to remember and type in long negative numbers to use the standard colours.