Navigating Records in a Datasheet

Wanting to make changes to records after you’ve entered them is not unusual. You may want to change records for several reasons:

• You receive new information that changes existing values.

• You discover errors in existing values.

• You need to add new records.

When you decide to edit data in a table, the first step is to open the table—if it isn’t already open. From the list of tables in the Navigation pane, double-click on tblProducts to open it in Datasheet View. If you’re already in Design View for this table, click the Datasheet View button to switch views.

When you open a datasheet in Access that has related tables, a column with a plus sign (+) is added to access the related records, or sub-datasheets.

Moving between records

You can move to any record by scrolling through the records and positioning your cursor on the desired one. 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 box and the scroll-bar arrows.

Use the five Navigation buttons to move between records. You simply click these buttons to move to the desired record. If you know the record number (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 ribbon’s Find group 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 ribbon’s Find group.

• 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. 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 the table name 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 three types of wildcards:

* (any number of characters)

? (any one character)

# (any one number)

To look at how these wildcards work, first suppose that you want to find any value in the Description field of tblProducts beginning with 2001; for this, you type 2001*. Then suppose that you want to search for values ending with Sedan, so you type *Sedan. If you want to search for any value that begins with 2001, ends with Sedan, and contains any number of characters in between, you type 2001*Sedan.

The Match drop-down list contains three choices that eliminate the need for wildcards:

• Any Part of Field

• Whole Field

• Start of Field

The default is Whole Field, which finds only the whole value you enter. For example, the Whole Field option finds the value FORD only if the value in the field being searched is exactly FORD. If you select Any Part of Field, Access searches to see whether the value is contained anywhere in the field; this search finds the value FORD in the field values FORDMAN, 2001 FORD F-150, and FORD. A search for FORD using the Start of Field option searches from the beginning of the field, and returns no values because the Description field always begins with a year (1999, 2003, and so on).

In addition to these combo boxes, you can use two checkboxes 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 checkbox, you must then enter the search string in the exact case of the field value. (The data types Number, Currency, and Date/Time do not have any case attributes.)

  If you have checked Match Case, Access does not use the value Search Fields As Formatted (the second checkbox), 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 checkbox, 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, 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 6-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 type FORD in the search box, the datasheet moves as you type each letter. First, it finds an F; then it finds FO and so on. Once it finds the value, it stops searching. To find more than one instance, use the Find and Replace dialog box.