Moving within a datasheet

You easily move within the Datasheet window using the mouse to indicate where you want to change or add to your data—just click a field within a record. In addition, the ribbons, scroll bars, and Navigation buttons make it easy to move among fields and records. Think of a datasheet as a spreadsheet without the row numbers and column letters. Instead, columns have field names, and rows are unique records that have identifiable values in each cell.

Table 5.1 lists the navigational keys you use for moving within a datasheet.

Using the Navigation buttons

The Navigation buttons (shown in Figure 5.2) are the six controls located at the bottom of the Datasheet window, which you click to move between records. The two leftmost controls move you to the first record or the previous record in the datasheet, respectively. The three rightmost controls position you on the next record, last record, or new record in the datasheet, respectively. If you know the record number (the row number of a specific record), you can click the record-number box, enter a record number, and press Enter.

Examining the Datasheet Ribbon

The Datasheet Ribbon (shown in Figure 5.3) provides a way to work with the datasheet. The Home Ribbon has some familiar objects on it, as well as some new ones. This section provides an overview of the groups on the Ribbon; the individual commands are described in more detail later in this chapter.

Saving the record

Moving to a different record saves the record you're editing. Tabbing through all the fields, clicking on the Navigation buttons, clicking Save in the Record group of the Ribbon, and closing the table all write the edited record to the database. You'll know the record is saved when the pencil disappears from the record selector.

To save a record, you must enter valid values into each field. The fields are validated for data type, uniqueness (if indexed for unique values), and any validation rules that you've entered into the Validation Rule property. If your table has a primary key that's not an AutoNumber field, you'll have to make sure you enter a unique value in the primary key field to avoid the error message shown in Figure 5.6. One way to avoid this error message while entering data is to use an AutoNumber field as the table's primary key.

Now you know how to enter, edit, and save data in a new or existing record. In the next section, you learn how Access validates your data as you make entries into the fields.

Understanding automatic data-type validation

Access validates certain types of data automatically. Therefore, you don't have to enter any data validation rules for these data types when you specify table properties. The data types that Access automatically validates include:

• Number/Currency
• Date/Time
• Yes/No

Access validates the data type when you move off the field. When you enter a letter into a Number or Currency field, you don't initially see a warning not to enter these characters. However, when you tab out of or click on a different field, you get a warning like the one shown in Figure 5.7. This particular warning lets you choose to enter a new value or change the column's data type to Text. You'll see this message if you enter other inappropriate characters (symbols, letters, and so on), enter more than one decimal point, or enter a number too large for the specified numeric data type.

Access validates Date/Time fields for valid date or time values. You'll see a warning similar to the one shown in Figure 5.7 if you try to enter a date such as 14/45/05, a time such as 37:39:12, or an invalid character in a Date/Time field.

Yes/No fields require that you enter one of these defined values:

• Yes: Yes, True, On, –1, or a number other than 0 (which displays as –1)
• No: No, False, Off, or 0

Of course, you can define your own acceptable values in the Format property for the field, but generally these values are the only acceptable ones. If you enter an invalid value, the warning appears with the message to indicate an inappropriate value.

Knowing how properties affect data entry

Because field types vary, you use different data-entry techniques for each type. In the “Saving the record” section earlier in this chapter, you learned that some data-type validation is automatic. Designing tblContacts, however, means entering certain user-defined format and data validation rules. The following sections examine the types of data entry.

Long Text field data entry

The Features field in tblProducts is a Long Text data type. This type of field allows up to 1GB of text for each field. As you enter text into a Long Text field, you see only a few characters at a time—the rest of the string scrolls out of sight. Pressing Shift+F2 displays a Zoom window with a scroll bar (see Figure 5.8) that lets you see more characters at a time. Click the Font button at the bottom of the window to view all the text in a different font or size. (The font in Figure 5.8 has been enlarged considerably over the 8-point default font size for the Zoom window.)

When you first display text in the Zoom window, all the text is selected. You can deselect the text by clicking anywhere in the window. If you accidentally delete all the text or change something you didn't want to, click Cancel to exit back to the datasheet with the field's original data.

Moving between records

You can move to any record by scrolling through the records and positioning your cursor on the desired record. With a large table, scrolling through all the records might take a while, so you'll want to use other methods to get to specific records quickly.

Use the vertical scroll bar to move between records. The scroll bar arrows move one record at a time. To move through many records at a time, drag the scroll box or click the areas between the scroll thumb and the scroll bar arrows.

Use the five Navigation buttons (refer to Figure 5.2) to move between records. You simply click these buttons to move to the desired record. If you know the record number (the row number of a specific record), click the record number box, enter a record number, and press Enter.

Also, use the Go To command in the Find group of the Ribbon to navigate to the First, Previous, Next, Last, and New records.

Finding a specific value

Although you can move to a specific record (if you know the record number) or to a specific field in the current record, usually you'll want to find a certain value in a record. You can use one of these methods for locating a value in a field:

• Select the Find command (a pair of binoculars) from the Find group of the Ribbon.
• Press Ctrl+F.
• Use the Search box at the bottom of the Datasheet window.

The first two methods display the Find and Replace dialog box (shown in Figure 5.9). To limit the search to a specific field, place your cursor in the field you want to search before you open the dialog box. Change the Look In combo box to Current Document to search the entire table for the value.

The Find and Replace dialog box lets you control many aspects of the search. Enter the value you want to search for in the Find What combo box, which contains a list of recently used searches. You can enter a specific value or choose to use wildcard characters. Table 5.2 lists the wildcard characters available in the Find dialog box.

You can combine wildcard characters for more robust searches. For example, 196[!2–8] will find 1961 and 1969, but nothing in between.

The Match drop-down list contains three choices:

• Any Part of Field: If you select Any Part of Field, Access searches to see whether the value is contained anywhere in the field. This search finds Ford anywhere in the field, including values like Ford Mustang, 2008 Ford F-150, and Ford Galaxy 500.
• Whole Field: The default is Whole Field, which finds fields containing exactly what you've entered. For example, the Whole Field option finds Ford only if the value in the field being searched is exactly Ford, and nothing else.
• Start of Field: A search for Ford using the Start of Field option searches from the beginning of the field and returns all the rows containing Ford as the first four characters of the description.

In addition to these combo boxes, you can use two check boxes at the bottom of the Find and Replace dialog box:

• Match Case: Match Case determines whether the search is case sensitive. The default is not case sensitive (not checked). A search for SMITH finds smith, SMITH, or Smith. If you check the Match Case check box, you must then enter the search string in the exact case of the field value. (The data types Number, Currency, and Date/Time don't have any case attributes.)

  If you've checked Match Case, Access doesn't use the value Search Fields As Formatted (the second check box), which limits the search to the actual values displayed in the table. (If you format a field for display in the datasheet, you should check the box.)

• Search Fields As Formatted: The Search Fields As Formatted check box, the selected default, finds only text that has the same pattern of characters as the text specified in the Find What box. Clear this box to find text regardless of the formatting. For example, if you're searching the Cost field for a value of $16,500, you must enter the comma if Search Fields as Formatted is checked. Uncheck this box to search for an unformatted value (16500).

The search begins when you click the Find Next button. If Access finds the value, the cursor highlights it in the datasheet. To find the next occurrence of the value, click the Find Next button again. The dialog box remains open so that you can find multiple occurrences. Choose one of three search direction choices (Up, Down, or All) in the Search drop-down list to change the search direction. When you find the value that you want, click Close to close the dialog box.

Use the search box at the bottom of the Datasheet window (refer to Figure 5.1) to quickly search for the first instance of a value. When using the search box, Access searches the entire datasheet for the value in any part of the field. If you enter FORD in the search box, the datasheet moves to the closest match as you type each letter. First, it finds a field with F as the first character, then it finds FO, and so on. Once it finds the complete value, it stops searching. To find the next instance, press the Enter key.

Manually replacing an existing value

Generally, you enter a field with either no characters selected or the entire value selected. If you use the keyboard (Tab or Arrow keys) to enter a field, you select the entire value. (You know that the entire value is selected when it's displayed in reverse video.) When you begin to type, the new content replaces the selected value automatically.

When you click in a field, the value is not selected. To select the entire value with the mouse, use any of these methods:

• Click just to the left of the value when the cursor is shown as a large plus sign.
• Click to the left of the value, hold down the left mouse button, and drag the mouse to select the whole value.
• Click in the field and press F2.

Changing an existing value

If you want to change an existing value instead of replacing the entire value, use the mouse and click in front of any character in the field to activate Insert mode; the existing value moves to the right as you type the new value. If you press the Insert key, your entry changes to Overstrike mode; you replace one character at a time as you type. Use the arrow keys to move between characters without disturbing them. Erase characters to the left by pressing Backspace, or to the right of the cursor by pressing Delete.

Table 5.3 lists editing techniques.

Changing the field order

By default, Access displays the fields in a datasheet in the same order in which they appear in the table design. Sometimes, you want to see certain fields next to each other in order to better analyze your data. To rearrange your fields, select a column by clicking the column heading, and then drag the column to its new location (as shown in Figure 5.12).

You can select and drag columns one at a time, or select multiple columns to move at the same time. Suppose you want ModelYear to appear before Features in the tblProducts datasheet. Follow these steps to make this change:

1. Position the mouse pointer over the ModelYear column heading. The cursor changes to a down arrow.
2. Click to select the column. The entire ModelYear column is now highlighted.
3. Release the mouse button.
4. Click the mouse button on the column heading again. The pointer changes to an arrow with a box under it.
5. Drag the column to the left edge of the datasheet between the Description and Features fields. A thin black column appears between them (refer to Figure 5.12).
6. Release the mouse button. The column moves in front of the Features field of the datasheet.

With this method, you can move any individual field or contiguous field selection. To select multiple fields, click and drag the mouse across multiple column headings. Then you can move the fields left or right or past the right or left boundary of the window.

Changing the field display width

You can change the field display width (column width) either by specifying the width in a dialog box (in number of characters) or by dragging the column border. When you hover the mouse over a column border, the cursor changes to the double-arrow symbol.

To widen a column or to make it narrower, follow these steps:

1. Place the mouse pointer between two column names on the field separator line. The mouse pointer turns into a small line with arrows pointing to the left and right—if you have it in the correct location.
2. Drag the column border to the left to make the column smaller or to the right to make it larger.

Alternatively, you can resize a column by right-clicking the column header and selecting Field Width from the pop-up menu to display the Column Width dialog box, as shown in Figure 5.13. Set the Column Width box to the number of characters you want to fit in the column or click the Standard Width check box to set the column to its default size. Click Best Fit to size the column to the widest visible value.

Changing the record display height

You might need to increase the row height to accommodate larger fonts or text that uses multiple lines. Change the record (row) height of all rows by dragging a row's border to make the row height larger or smaller.

When you drag a record's border, the cursor changes to the vertical two-headed arrow you see at the left edge of Figure 5.14.

To increase or decrease a row's height, follow these steps:

1. Place the mouse pointer between record selectors of two rows. The cursor changes to the double-pointing arrow (up and down).
2. Drag the row border up to shrink all row heights or down to increase all row heights.

You can also resize rows by choosing More Row Height in the Records group of the Ribbon. The Row Height dialog box appears; there you enter the row height in point size. Check the Standard Height check box to return the rows to their default size.

Changing display fonts

By default, Access displays all data in the datasheet in the Calibri 11-point Regular font. Use the commands and drop-down lists in the Text Formatting group of the Ribbon (shown in Figure 5.15) to change the datasheet's text appearance.

Setting the font display affects the entire datasheet. If you want to see more data on the screen, you can use a very small font. You can also switch to a higher-resolution display size if you have the necessary hardware. If you want to see larger characters, you can increase the font size or click the Bold button.

Displaying cell gridlines and alternate row colors

Normally gridlines appear between fields (columns) and between records (rows). You can set how you want the gridlines to appear using the Gridlines command in the Text Formatting group of the Ribbon (shown in Figure 5.15). Choose from the following options in the Gridlines drop-down list:

• Gridlines: Both
• Gridlines: Horizontal
• Gridlines: Vertical
• Gridlines: None

Use the Background Color and Alternate Row Color drop-down lists, also in the Text Formatting group, to change the background colors of the datasheet. The Background Color palette changes the color of all the rows in the datasheet. The Alternate Row Color palette changes the color of the even-numbered rows. When Alternate Row Color is set, the Background Color palette only affects the odd-numbered rows. To remove coloring from alternate rows, set Alternate Row Color to No Color.

After changing the gridline settings or alternate row colors, Access will ask whether to save the changes to the datasheet's layout. Be sure to click Yes if you want to make the changes permanent.

The Datasheet Formatting dialog box (shown in Figure 5.16) gives you complete control over the datasheet's look. Open this dialog box using the Datasheet Formatting command in the bottom-right corner of the Text Formatting group of the Ribbon. Use the Flat, Sunken, and Raised radio buttons under Cell Effect to change the grid to a 3-D look. Click the Horizontal and Vertical check boxes under Gridlines Shown to toggle which gridlines you want to see. Change the Background Color, Alternate Background Color, and Gridline Color using the available color palettes. The sample in the middle of the dialog box shows you a preview of changes.

Use the Border and Line Styles drop-down lists to change the look of the gridlines. You can change the styles for the Datasheet Border and the Column Header Underline. Choose a different line style for each of the selections in the first drop-down list. The different line styles you can select from include:

• Transparent Border
• Solid
• Dashes
• Short Dashes
• Dots
• Sparse Dots
• Dash-Dot
• Dash-Dot-Dot
• Double Solid

Figure 5.17 shows a datasheet with dashes instead of solid lines and a higher contrast between alternating rows. You can use the various colors and styles to customize the datasheet's look to your liking.

Aligning data in columns

Align the data to the left or right, or center it within a column using the alignment buttons. Choose alignments different from the default alignments Access chooses based on a field's data type (text aligns left, numbers/dates align right). Follow these steps to change the alignment of the data in a column:

1. Position the cursor anywhere within the column that you want to change the alignment.
2. Click the Align Left, Align Center, or Align Right commands in the Text Formatting group of the Ribbon (refer to Figure 5.15) to change the alignment of the column's data.

Hiding and unhiding columns

Hide columns by dragging the column gridline to the preceding field or by setting the column width to 0:

1. Position the cursor anywhere within the column that you want to hide.
2. Choose More Hide Fields in the Records group of the Ribbon. The column disappears because the column width is simply set to 0. You can hide multiple columns by first selecting them and then choosing More Hide Fields.

After you've hidden a column, you can redisplay it by choosing More Unhide Fields in the Records group of the Ribbon. A dialog box appears, letting you selectively unhide columns by checking next to each field (see Figure 5.18). Click Close to return to the datasheet showing the desired columns. You can also use this dialog box to hide one or more columns by unchecking the check box next to each field you want to hide.

Freezing columns

When you want to scroll left and right among many columns but want to keep certain columns from scrolling out of view, choose More Freeze Fields in the Records group of the Ribbon. With this command, for example, you can keep the ProductID and Description fields visible while you scroll through the datasheet to find the product's features. The frozen columns are visible on the far-left side of the datasheet while other fields scroll horizontally out of sight. The fields must be contiguous if you want to freeze more than one at a time. (Of course, you can first move your fields to place them next to each other.) When you're ready to unfreeze the datasheet columns, simply choose More Unfreeze All Fields.

Saving the changed layout

When you close the datasheet, you save all your data changes but you might lose all your layout changes. As you make all these display changes to your datasheet, you probably won't want to make them again the next time you open the same datasheet. If you make any layout changes, Access prompts you to save the changes to the layout when you close the datasheet. Choose Yes to save the changes. You can also save the layout changes manually by clicking Save on the Quick Access toolbar.

Saving a record

Access saves each record when you move off it. Pressing Shift+Enter or selecting Save from the Records group of the Ribbon saves a record without moving off it. Closing the datasheet also saves a record.

Sorting your records with QuickSort

Sometimes you might simply want to sort your records in a desired order. The QuickSort Ribbon commands let you sort selected columns into either ascending or descending order. To use these commands, click in a field you want to sort by, and then click Ascending or Descending. The data redisplays instantly in the sorted order. Right-clicking on a column and selecting either of the Sort buttons also sorts the data. The captions on the right-click menu change depending on the data type of the field. Short Text fields display Sort A to Z and Sort Z to A, while numeric fields display Sort Smallest to Largest and Sort Largest to Smallest. Some fields, such as OLE and Long Text fields, can't be sorted.

To sort your data on the basis of values in multiple fields, highlight more than one column: Highlight a column (see the “Changing the field order” section earlier in this chapter) and before you release the mouse button, drag the cursor to the right or left. When you select one of the QuickSort commands, Access sorts the records into major order (by the first highlighted field) and then into orders within orders (based on subsequent fields). If you need to select multiple columns that aren't contiguous (next to each other), you can move them next to each other (see the “Changing the field order” section earlier in this chapter).

Filtering a selection

Filter by Selection lets you select records on the basis of the current field value. For example, using tblProducts, place your cursor in a row containing Trucks in the Category column, press the Selection command in the Sort & Filter group of the Ribbon, and choose Equals “Trucks.” Access filters the datasheet to show only those records where the Category is trucks.

Access gives you four choices when you click the Selection command:

• Equals “Trucks”
• Does Not Equal “Trucks”
• Contains “Trucks”
• Does Not Contain “Trucks”

The area to the right of the Navigation buttons—at the bottom of the Datasheet window—tells you whether the datasheet is currently filtered; in addition, the Toggle Filter command on the Ribbon is highlighted, indicating that a filter is in use. When you click this command, it removes the filter. The filter specification doesn't go away; it's simply turned off. Click the Toggle Filter command again to apply the same filter.

Filtering by selection is additive. You can continue to select values, each time pressing the Selection command.

If you want to further specify a selection and then see everything that doesn't match that selection (for example, where the Make field isn't Chevrolet), move the cursor to the field (the Make field where the value is Chevrolet), right-click the datasheet, and then select Does Not Equal “Chevrolet” from the filter options that appear in the right-click shortcut menu.

When using the Selection command on numeric or date fields, select Between from the available command to enter a range of values. Enter the smallest and largest numbers or oldest and newest dates to limit the records to values that fall in the desired range.

Imagine using this technique to review sales by salespeople for specific time periods or products. Filtering by selection provides incredible opportunities to drill down into successive layers of data. Even when you click the Toggle Filter command to redisplay all the records, Access still stores the query specification in memory. Figure 5.20 shows the filtered datasheet, with the Filter by Select list still open on the Category field.

When a datasheet is filtered, each column has an indicator in the column heading letting you know if a filter is applied to that column. Hover the mouse over the indicator to see a tooltip displaying the filter. Click the indicator to specify additional criteria for the column using the pop-up menu shown in Figure 5.21. Click the column heading's down-arrow for an unfiltered column to display a similar menu.

The menu contains commands to sort the column ascending or descending, clear the filter from the field, select a specific filter, and check values you want to see in the datasheet. The available commands change based on the data type of the column. In this case, Text Filter lets you enter a criterion that filters the data based on data you type in.

The check boxes in this menu contain data that appear in the column. In this case, the choices are: (Select All), (Blanks), and one entry for each Make in the table. The Mack entry is unchecked in this example. Click (Select All) to see all the records regardless of this field's value. Click (Blanks) to see the records that don't contain data. Select any of the data values to limit the records where the field contains the selected values. Place a check next to each Make you'd like to see and remove the check for each Make you want to exclude.

If you want to filter data but you can't find the value that you want to use and you know the value, click the Text Filters (or Number Filters, Date Filters, and so on) command and choose one of the available commands (Equals, Does Not Equal, Begins With, and so on) to display a dialog box where you type in the desired value.

Filtering by form

Filter by Form lets you enter criteria into a single row on the datasheet. Clicking the Filter by Form button transforms the datasheet into a single row containing a drop-down list in every column. The drop-down list contains all the unique values for the column. An Or tab at the bottom of the window lets you specify OR conditions for each group. Choose Advanced Filter by Form in the Sort & Filter group of the Ribbon to enter Filter by Form mode, shown in Figure 5.22.

Select values from the combo boxes or type values you want to search for in the field. If you want to see records where the Category is Trucks or SUVs, select Trucks from the Category drop-down list, select the Or tab at the bottom of the window, and then select SUVs from the Category drop-down list. To see records where Category is SUV and QtyInStock is 1, select SUV from the Category drop-down and type 1 in QtyInStock. Once you enter the desired criteria, click the Toggle Filter command to apply the filter. (The Toggle Filter button is shown in Figure 5.19.)

Enter as many conditions as you need using the Or tab. If you need even more advanced manipulation of your selections, you can choose Advanced Advanced Filter/Sort from the Sort & Filter group of the Ribbon to get an actual Query by Example (QBE) screen that you can use to enter more-complex criteria.