Section 3.1. Datasheet Customization | Access 2007[c] The Missing Manual

3.1. Datasheet Customization

Getting tired of the drab datasheet, with its boring stretch of columns and plain text? You can do something about it. Access lets you tweak the datasheet's appearance and organization to make it more practical (or suit it to your peculiar sense of style). Some of these customizationslike modifying the datasheet fontare shameless frills. Other options, like hiding or freezing columns , can genuinely make it easier to work with large tables.

Note: Access doesn't save formatting changes immediately (unlike record edits, which it stores as soon as you make them). Instead, Access prompts you to save changes the next time you close the datasheet. You can choose Yes to keep your customizations or No to revert to the table's last look and feel (which doesn't affect any edits you've made to the data in that table).

3.1.1. Formatting the Datasheet

Access lets you format the datasheet with eye-catching colors and fonts. Do these options make any difference to the way the datasheet works? Not really. But if your computer desktop looks more like a '60s revival party than an office terminal, then you'll enjoy this feature.

To find the formatting features, look at the ribbon's Home Font section (see Figure 3-1).

Figure 3-1. The Home Font section lets you change the text font and colors in the entire datasheet. The most practical frill is the ability to turn off some or all of the gridlines and use alternating row colors to highlight every other row, as shown here.

Every formatting change you make affects the entire table. You may think it's a nifty idea to apply different formatting to different columns, but Access doesn't let you. If this limitation's frustrating you, be sure to check out forms and reports later in this book. Both are more complicated to set up, but give you more formatting power.

Note: There's one other way you can use the ribbon's Home

Font section. If you have a field that uses the Memo data type and youve set your field to use rich text (Section 2.3.2.1), then you can select some text inside your field, and change its formatting using the ribbon.

GEM IN THE ROUGH
Customizing All Your Datasheets

Access lets you format only one table at a time. So if you find a formatting option you really like, you'll need to apply it separately to every table in your database.

However, you can set formatting options so that they automatically apply to every table in every database by configuring Access itself. To pull this trick off, follow these steps:

Choose Office button Access Options to show the Access Options window.
On the right, you see the standard font, color , gridline, and column width options, which you can change to whatever you want.

When you change the datasheet formatting settings in the Access Options window, you change the defaults that Access uses. These settings determine the formatting that Access uses for new tables and any tables that aren't customized. When you customize a table, you override the default settings, no matter what they are.

If you set Access to use red text, but you format a specific table to use green text, the green text setting takes precedence. However, if you set a yellow background in the Access Options window, and you don't customize that detail for your table, then it automatically acquires the standard yellow.

3.1.2. Rearranging Columns

The fields in the datasheet are laid out from left to right, in the order you created them. Often, you'll discover that this order isn't the most efficient for data entry.

Imagine you've created a Customers table for a novelty pasta company. When a new customer registration ends up on your desk, you realize that the registration form starts with the name and address information, and then includes the customer's pasta preferences. Unfortunately, the fields on the datasheet are laid out in a completely different order. From right to left, they're arranged like this: ID, FreshPastaPreference, DriedPastaPreference, FirstName, LastName, Street, City, State, Country. (This organization isn't as crazy as it seemsit actually makes it easier for the people filling pasta orders to quickly find the information they want.) Because of this ordering, you need to skip back and forth just to enter the information from a single registration.

Fortunately, you can solve this problem without redesigning the table. Drag the columns you want to move to new positions , as shown in Figure 3-2.

Figure 3-2. To move a column, click the column header once to select that column. Then, drag the column header to its new location. In this example, the FirstName field is about to be relocated that it's just before the FreshPastaPreference field.

The best part of this approach is that you don't need to modify the database's actual structure. If you switch to Design view after moving a few columns, you'll see that the field order hasn't changed. In other words, you can keep the exact same physical order of fields (in your database file) but organize them differently in Datasheet view.

Tip: Rearranging columns is a relatively minor change. Don't worry about shifting columns around to suit a specific editing job and then switching them back later on. Your changes don't affect the data in the database. If you want to use a particular column order for a one-time job, simply refrain from saving your changes when you close the datasheet.

3.1.3. Resizing Rows and Columns

As you cram more and more information into a table, your datasheet becomes wider and wider. In many cases, you'll be frustrated with some columns hogging more space than they need and others being impossibly narrow.

As you'd expect, Access lets you tweak column widths. But you probably haven't realized how many different ways you can do it:

Resize a single column . Move the mouse to the column's right edge. Drag to the left (to shrink the column) or to the right (to make it larger).
Resize a column to fit its content . Double-click the column edge. Access makes the column just wide enough to fit the field name or the largest value (whichever's larger). However, it doesn't make the column so wide that it stretches beyond the bounds of the window.
Resize several adjacent columns . Drag the first column's header across the columns until you've selected them all. Then, drag the right edge of your selection to the left or the right. All the selected columns shrink or expand to fit the available space, sharing it equally.
Resize a column with pinpoint accuracy . Right-click the column header, and then choose Column Width. You'll see the Column Width dialog that lets you set an exact width as a number (Figure 3-3).

Figure 3-3. The Column Width dialog box lets you set an exact width as a number. (The number doesn't actually have a concrete meaningit's supposed to be a width in characters, but because modern Access uses proportional fonts, different characters are different sizes.) You can also turn on the Standard Width checkbox to reset the width to the standard narrow size , or click Best Fit to expand the column to fit its content (just as when you double-click the edge of the column).

Note: Remember, a column doesn't need to be wide enough to show all its data at once. You can scroll through a lengthy text field using the arrow keys, and if that's too awkward , use the Shift+F2 shortcut to show the full contents of the current field in a Zoom box.

Just as you can resize columns, you can also resize rows. The difference is that Access makes sure all rows have the same size. So when you make one row taller or shorter, Access adjusts all the other rows to match.

You'll mainly want to shrink a row to cram more rows into view at once. You'll want to enlarge a row mostly to show more than one line of text in each text field (see Figure 3-4).

Figure 3-4. If a row's large enough, Access wraps the text inside it over multiple lines, as shown here with the Description column.

3.1.4. Hiding Columns

Many tables contain so many columns that you can't possibly fit them all into view at the same time. This quality's one of the drawbacks to the datasheet, and often you have no choice but to scroll from side to side.

However, in some situations, you may not need to see all the fields at once. In this case, you can temporarily hide the columns that don't interest you, thereby homing in on the important details without distraction. Initially, every field you add to a table is out in the open .

To hide a column, select the column by clicking the column header. (You can also select several adjacent columns by clicking the column header of the first, and then dragging the mouse across the rest.) Then, right-click your selection, and then choose Hide Columns. The column instantly vanishes from the datasheet. (This sudden disappearance can be a little traumatic for Access newbies.)

Fortunately, the field and all its data remain just out of sight. To pop the column back into view, right-click any column header and choose Unhide Columns. Access then shows the Unhide Columns dialog box (Figure 3-5).

Figure 3-5. Using the Unhide Columns dialog box, you can choose to make hidden columns reappear, and (paradoxically) you can hide ones that are currently visible. Every column that has a checkmark next to it is visibleevery column that doesn't is hidden. As you change the visibility, Access updates the datasheet immediately. When you're happy with the results, click Close to get back to the datasheet.

Note: At the bottom of the field list, you'll see an entry named Add New Field. This "field" isn't really a fieldit's the placeholder that appears just to the right of your last field in datasheet view, which you can use to add new fields (Section 2.2.1). If you're in the habit of adding fields using Design view (Section 1.2.3), then you can hide this placeholder to free up some extra space.

If you add a new record while columns are hidden, you can't supply a value for that field. The value starts out either empty or with the default value (if you've defined one for that field, as described in Section 4.1.2). If you've hidden a required field (Section 4.1.1), you receive an error message when you try to insert the record. All you can do is unhide the appropriate column, and then fill in the missing information.

3.1.5. Freezing Columns

Even with the ability to hide and resize columns, you'll probably need to scroll from side to side in a typical datasheet. In this situation, you can easily lose your place. You might scroll to see more information in the Contacts table, but then forget exactly which person you're looking at. Access has one more feature that can help you by making sure important information is always visible frozen columns.

A frozen column remains fixed in place at the Access window's left side at all times. Even as you scroll to the right, all your frozen columns remain visible (Figure 3-6). To freeze a column (or columns), select them, right-click the column header, and then choose Freeze Columns.

Tip: If you want to freeze several columns that aren't next to each other, start by freezing the column that you want to appear at the very left. Then, repeat the process to freeze the column that you want to appear just to the right of the first column, and so on.

Figure 3-6. Top: In this example, the FirstName and LastName field are frozen. They appear initially at the left. (The ribbon's collapsed in this figure to make more room, as described in Section 1.4.2.)
Bottom: When you scroll to the side to see more information, the FirstName and LastName columns stay put.

Frozen columns must always be positioned at the left size of the datasheet. If you freeze a column that's somewhere else, Access moves it to the left side and then freezes it. You can move it back after you unfreeze the column using the column reordering trick in Section 3.1.2. Keep in mind that while a column's frozen, you can't drag it to a different place.

To unfreeze columns, right-click a column header, and then choose Unfreeze All Columns.

Note: Eventually, you'll discover that the customizations provided by the datasheet aren't enough, or you'll need to customize the same table different ways for different people. These signs tell you that you need to step up to forms, a more advanced data display option described in Part Four.