Chapter 6: Working with Datasheet View
In Chapter 2, you created a database named My Access Auto Auctions to hold the tables, queries, forms, reports, and macros you’ll create as you learn Access. You also created a table named tblContacts using the Access 2007 table designer.
In this chapter, you’ll use a datasheet to enter data into an Access table and display the data many different ways. Using Datasheet View allows you to see many records at once, in a the common spreadsheet-style format. In this chapter, you’ll work with tblContacts and tblProducts to add, change, and delete data, as well as learn about different features available in Datasheet View.
This chapter uses the database named Chapter06.accdb. If you haven’t already copied it onto your machine from the CD, you’ll need to do so now.
Understanding Datasheets
Using a datasheet is just one of the ways to view data in Access. A datasheet is similar to a spreadsheet because it displays data as a series of rows and columns. Figure 6-1 shows a typical Datasheet View of data. Each row represents a single record, and each column represents a single field in the table. Scroll up or down in the datasheet to see the rows (records) that don’t fit on the screen; scroll left or right to see the columns (fields) that don’t fit.
Datasheets are completely customizable, which allows you to view data in many ways. Changing the font size, column widths, and row heights makes more or less of the data fit on the screen. Rearranging the order of the rows and/or columns lets you organize the records and fields logically. Locking columns makes them stay in position as you scroll to other parts of the datasheet, and hiding columns makes them disappear. Filtering the data hides records that don’t match a specific criteria.
Figure 6-1
A typical Datasheet View. Each row represents a single record in the table; each column represents a single field (like Description or RetailPrice) in the table.
Quick Review of Records and Fields
A table is a container for storing related information—patient records, a card list (birthday, holiday), birthday reminders, payroll information, and so on. Each table has a formal structure comprised of fields, each with a unique name to identify and describe the stored information and a specific data type—text, numeric, date and time, and so on—to limit what users enter in these fields. When displayed in a datasheet (a two-dimensional sheet of information), Access displays these fields in columns.
The table is composed of records, which hold information about a single entity (like a single customer or a single product). One record is made up of information stored in all the fields of the table structure. For example, if a table has three fields—name, address, and phone number—then the first record only has one name, one address, and one phone number in it. The second record also has one name, one address, and one phone number in it. A datasheet is an ideal way of looking at all the table’s contents at once. A single record appears as a row in the datasheet; each row contains information for that specific record. The fields appear as columns in the datasheet; each column contains an individual field’s contents. This row-and-column format lets you see lots of data at once.
The Datasheet Window
The Datasheet window appears in the center of the Access window shown in Figure 6-1. This Datasheet window displays the data in rows and columns. Each record occupies one row, and each column—headed by a field name in the field title area—contains each field’s values. The display arranges the records initially by primary key and the fields by the order in the table design.
At the top of the Access window, you see the title bar (displaying the database filename and Microsoft Access), the Quick Access toolbar, and the ribbon. At the bottom of the Access window, you see the status bar, which displays assorted information about the datasheet. For example, it may contain field description information (as in Figure 6-1, “Up to 100 character description of the product”), error messages, warnings, or a progress bar. If you gave the field a description when creating it, the field description that you enter for each field is displayed in the status bar. If a specific field doesn’t have a field description, Access displays the words Datasheet View. Generally, error messages and warnings appear in dialog boxes in the center of the screen rather than in the status bar. If you need help understanding the meaning of a button in the toolbar, move the mouse over the button, hovering over it, and a ToolTip appears with a one- or two-word explanation.
The right side of the Datasheet window contains a scroll bar for moving quickly between records (up and down). As you scroll between records, a ScrollTip (shown in Figure 6-1) tells you precisely where the scroll bar takes you. In Access 2007, the size of the scroll bar thumb gives you a proportional look at how many of the total number of records are being displayed. In Figure 6-1, the scroll bar thumb takes up about 12 percent of the scroll area, and 28 of 60 records are shown on-screen. The bottom of the Datasheet window also contains a proportional scroll bar for moving among fields (left to right). The Navigation buttons—for moving between records—also appear in the bottom-left corner of the datasheet window.
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 and record location. 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 6-1 lists the navigational keys that you can use for moving within a datasheet.
The Navigation buttons
The Navigation buttons (shown in Figure 6-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. The three rightmost controls position you on the next record, last record, or new record in the datasheet. 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.
If you enter a record number greater than the number of records in the table, an error message appears stating that you can’t go to the specified record.
Figure 6-2
The Navigation buttons of a datasheet
The Datasheet ribbon
The Datasheet ribbon (shown in Figure 6-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 Home ribbon; the individual commands are described in more detail later in this chapter.
Figure 6-3
The Datasheet ribbon’s Home tab
The Home ribbon is divided into the following groups:
• Views: The first group is the Views group, which allows you to switch between Datasheet View, PivotTable View, PivotChart View, and Design View. You can see all four choices by clicking the View command’s down arrow (triangle pointing down). Clicking Design View permits you to make changes to the object’s design (table, query, and so on). Clicking Datasheet View returns to the datasheet.
• Clipboard: The Clipboard group contains the Cut, Copy, and Paste commands. These commands work like the commands in other applications (Word, Excel, and so on). The Paste command’s down arrow gives you three choices: Paste, Paste Special, and Paste Append. Paste Special gives you the option of pasting the contents of the clipboard in different formats (Text, CSV, Records, and so on). Paste Append pastes the contents of the Clipboard as a new record—provided a row with a similar structure was copied.
• Font: The Font group lets you change the look of the datasheet. Use these commands to change the font, size, bold, italic, color, and so on. Use the Align Left, Align Right, and Align Center commands to justify the data in the selected column. Click the Gridlines command to toggle gridlines on and off. Use the Alternate Fill/Back Color command to change the colors of alternating rows or to make them all the same.
• Rich Text: The Rich Text group lets you change a memo field’s data if the field’s Text Format property is set to Rich Text. Use these commands to add bullets or numbered lists and change the indentation levels. Highlighting text in a Rich Text field, and then selecting commands in the Font group, changes the highlighted text instead of the entire datasheet.
• Records: The Records group lets you save, delete, or add a new record to the datasheet. It also contains commands to show totals, check spelling, freeze and hide columns, and change the row height and cell width.
• Sort & Filter: The Sort & Filter group lets you change the order of the rows as well as limit the rows being displayed—based on criteria you want.
• Find: The Find group lets you find and replace data and go to specific records in the datasheet. Use the select command to select a record or all records.
Opening a Datasheet
Follow these steps to open a datasheet from the Database window:
1. Using the Chapter06.accdb database from the CD, click Tables in the Navigation Pane.
2. Double-click the table name you want to open (in this example, tblProducts).
An alternative method for opening the datasheet is to right-click on tblProducts and select Open from the pop-up menu.
If you are in any of the design windows, click on the Datasheet View command in the ribbon’s View group to view your data in a datasheet.
Entering New Data
All the records in your table are visible when you first open it in Datasheet View. If you just created your table, the new datasheet doesn’t contain any data. Figure 6-4 shows an empty datasheet. When the datasheet is empty, the first row contains an asterisk (*) in the record selector—indicating it’s a new record.
Figure 6-4
An empty datasheet. Notice that the first record is blank and has an asterisk in the record selector.
The new record appears at the bottom of the datasheet when the datasheet already contains records. Click the New Record command in the ribbon’s Record group, or click the new record navigation button to move the cursor to the new row—or simply click on the last row, which contains the asterisk. The asterisk turns into a pencil when you begin entering data, indicating that the record is being edited. A new row—containing an asterisk—appears below the one you’re entering data into. The new-record pointer always appears in the last row of the datasheet. Figure 6-5 shows adding a new record into tblProducts.
Figure 6-5
Entering a new record into the datasheet of tblProducts
To add a new record to the open Datasheet View of the tblProducts, follow these steps:
1. Click the New Record button.
2. Type in values for all fields of the table, moving between fields by pressing the Enter key or the Tab key.
When adding or editing records, you may see three different record pointers:
• Record being edited
• Record is locked (multiuser systems)
• New record
If the record contains an AutoNumber field, Access shows the name (New) in the field. You cannot enter a value in this type of field; rather, simply press the Tab or Enter key to skip this field. Access automatically puts the number in when you begin entering data.
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 ribbon’s Record group, 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 have 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 6-6. Using an AutoNumber field as a table’s primary key ensures you won’t get this error message when entering data.
Figure 6-6
The error message Access displays when attempting to save a record with a duplicate primary key value entered into the new record. Use an AutoNumber field as your primary key to avoid this error.
The Undo button in the Quick Access toolbar reverses changes to the current record and to the last saved record. After you change a second record, you cannot undo the saved record.
You can save the record to disk without leaving the record by pressing Shift+Enter.
Now you know how to enter, edit, and save data in a new or existing record. Next 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 validates automatically 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 6-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 a certain Number data type.
Figure 6-7
The warning Access displays when entering data that doesn’t match the field’s data type. Access gives you a few choices to correct the problem.
Access validates Date/Time fields for valid date or time values. You’ll see a warning similar to the one shown in Figure 6-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, True, -1, or a number other than 0 (it displays as a -1) for Yes; or No, False, Off, or 0 for No. Of course, you can define your own acceptable values in the Format property for the field, but generally these are the only acceptable values. If you enter an invalid value, the warning appears with the message to indicate an inappropriate value.
Display a check box in Yes/No fields to prevent users from entering invalid data.
Understanding how properties affect data entry
Because field types vary, you use different data-entry techniques for each type. Previously 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.
Standard text data entry
The first field—ContactID—in tblContacts is an AutoNumber field; the next 13 fields are Text fields. After skipping ContactID, you simply enter a value in each field and move on. The ZipCode field uses an input mask (00000\-9999;0;) for data entry. The Phone and Fax fields also use an input mask (!\(999”) “000\-0000;0;). These are the only fields that use any special formatting via the input mask. Text fields accept any characters, unless you restrict them with an input mask.
To enter multiple lines in a Text or Memo field, press Ctrl+Enter to add a new line. This is useful, for example, in large text strings for formatting a multiple-line address field.
Date/Time data entry
The OrigCustDate and LastSalesDate fields in tblContacts are Date/Time data types, which both use a Short Date format (3/16/2007). However, you could have defined the format as Medium Date (16-Mar-07) or Long Date (Friday, March 16, 2007). Using either of these formats simply means that no matter how you type in the date—using month and year; day, month, and year; or month, day, and year—it always displays as the format specified (short date [3/16/07], medium date [16-Mar-07], or long date [Friday, March 16, 2007]). So if you type 4/8/08 or 8 Apr 08, Access displays the value in the defined format when you leave the field. The value 4/8/2008 is really stored in the table.
Formats only affect the display of the data. They do not change storage of data in the table.
Number/Currency data entry with data validation
The CreditLimit field in tblContacts has a validation rule assigned to it. It has a Validation Rule property to limit the amount of credit to $250,000. If the rule is violated, a dialog box appears with the validation text entered for the field. If you want to allow a contact to have more than $250,000 credit, change the validation rule in the table design.
OLE object data entry
You can enter OLE (Object Linking and Embedding) Object data into a datasheet, even though you don’t see the object. An OLE Object field holds many different item types, including:
• Bitmap pictures
• Sound files
• Business graphs
• Word or Excel files
Any object that an OLE server supports can be stored in an Access OLE Object field. OLE objects are generally entered into a form so you can see, hear, or use the value. When OLE objects appear in datasheets, you see text that tells what the object is (for example, you may see Bitmap Image in the OLE Object field). You can enter OLE objects into a field in two ways:
• Pasting from the Clipboard
• Right-clicking on the OLE Object field and clicking on Insert Object from the pop-up menu
For thorough coverage of using and displaying OLE objects, see Chapter 24.
Memo field data entry
The second-to-last field in the table is Notes, which is a Memo data type. This type of field allows up to 65,536 characters of text for each field. Recall that you entered a long string (about 260 characters) into the Memo field. As you entered the string, however, you saw only a few characters at a time. The rest of the string scrolled out of sight. Pressing Shift+F2 displays a Zoom window with a scroll bar (see Figure 6-8) that lets you to 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.
Figure 6-8
The Zoom window. Notice you can see a lot more of the field’s data—not all 65,536 characters, but still quite a lot.
When you first display text in the Zoom window, all the text is selected and highlighted. 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.
Use the Zoom window (Shift+F2) when designing Access objects (tables, forms, reports, queries) to see text that normally scrolls out of view.
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 subdatasheets.
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.
Watch the ScrollTips when you use scroll bars to move to another area of the datasheet. Access does not update the record number box until you click a field.
Use the five Navigation buttons (refer to Figure 6-2) 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 (shown in Figure 6-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 the table name to search the entire table for the value.
Figure 6-9
The Find and Replace dialog box. The fastest way to activate it is to simply press the Ctrl+F key combination.
If you highlight the entire record by clicking the record selector (the small gray box next to the record), Access automatically searches through all fields.
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.
For more information on using wildcards, see Chapter 5.
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 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 do not have any case attributes.)
If you have checked Match Case, Access does not 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).
Checking Search Fields As Formatted may slow the search process.
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.
Changing Values in a Datasheet
If the field that you are in has no value, you can type a new value into the field. When you enter new values into a field, follow the same rules as for a new-record entry.
Replacing an existing value manually
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 is 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.
• Double-click in the field. (This only works if the field doesn’t contain spaces.)
• 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.
You may want to replace an existing value with the value from the field’s Default Value property. To do so, select the value and press Ctrl+Alt+Spacebar. To replace an existing value with that of the same field from the preceding record, press Ctrl+’ (single quote mark). Press Ctrl+; (semicolon) to place the current date in a field.
Pressing Ctrl+- (minus sign) deletes the current record.
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 6-2 lists editing techniques.
Fields that you can’t edit
Some fields can’t be edited, such as:
• AutoNumber fields: Access maintains AutoNumber fields automatically, calculating the values as you create each new record. AutoNumber fields can be used as the primary key.
• Calculated fields: Access uses calculated fields in forms or queries; these values are not actually stored in your table.
• Locked or disabled fields: You can set certain properties in a form to prevent editing for a specific field.
• Fields in multiuser locked records: If another user locks the record, you can’t edit any fields in that record.
Using the Undo Feature
The Undo button on the Quick Access toolbar is often dimmed because there’s nothing to undo. As soon as you begin editing a record, however, you can use this button to undo the typing in the current field. You can also undo a change with the Esc key; pressing Esc cancels either a changed value or the previously changed field. Pressing Esc twice undoes changes to the entire current record.
After you type a value into a field, click the Undo button to undo changes to that value. After you move to another field, you can undo the change to the preceding field’s value by clicking the Undo button. You can also undo all the changes to an unsaved current record by clicking the Undo button after you undo a field. After you save a record, you can still undo the changes by clicking the Undo button. However, after the next record is edited, changes to the previous record are permanent.
Don’t rely on the Undo command to save you after you edit multiple records. When working in a datasheet, changes are saved when you move from record to record and you can only undo changes to one record.
Copying and Pasting Values
Copying or cutting data to the Clipboard is a Microsoft Windows task; it is not a specific function of Access. After you cut or copy a value, you can paste into another field or record by using the Paste command in the ribbon’s Clipboard group. You can cut, copy, or paste data from any Windows application or from one task to another in Access. Using this technique, you can copy entire records between tables or databases, and you can copy datasheet values to and from Microsoft Word and Excel.
The Paste command’s down arrow gives you three choices:
• Paste: Paste inserts the contents of the Clipboard into one field.
• Paste Special: Paste Special gives you the option of pasting the contents of the Clipboard in different formats (Text, CSV, Records, and so on).
• Paste Append: Paste Append pastes the contents of the Clipboard as a new record—provided a row with a similar structure was copied.
Select a record or group of records using the record selector to cut or copy one or more records to the Clipboard. Then use Paste Append to add them to a table with a similar structure.
Replacing Values
To replace an existing value in a field, you can manually find the record to update or you can use the Find and Replace dialog box. Display the Find and Replace dialog box using these methods:
• Select the Replace command from the ribbon’s Find group
• Press Ctrl+H
This dialog box allows you to replace a value in the current field or in the entire datasheet. Use it to find a certain value and replace it with a new value everywhere it appears in the field or table.
After the Find and Replace dialog box is active, you should first click the Replace tab and type in the value that you want to find in the Find What box. After you have selected all the remaining search options (turn off Search Fields As Formatted, for example), click the Find Next button to find to the first occurrence of the value. To change the value of the current found item (under the cursor), enter a value in the Replace With box and click the Replace button. For example, Figure 6-10 shows that you want to find the value Motor Homes in the Category field of tblProducts and change it to Camper.
Figure 6-10
Find and Replace dialog box with the Replace tab showing. In this case, you want to replace the value Motor Homes with Camper.
You can select your search options in the Find tab and then click the Replace tab to continue the process. However, it is far easier to simply do the entire process using the Replace tab. Enter the value you want to find and the value that you want to replace it with. After you have completed the dialog box with all the correct information, select one of the command buttons on the side:
• Find Next: Finds the next field that has the value in the Find What field.
• Cancel: Closes the form and performs no find and replace.
• Replace: Replaces the value in the current field only. (Note: You must use the Find Next button first.)
• Replace All: Finds all the fields with the Find What value and replaces them with the Replace With value. Use this if you’re sure that you want to replace all the values; double-check the Look In box to make sure you don’t replace the values in the entire datasheet if you don’t want to.
Adding New Records
There are a number of ways to add a record to the datasheet:
• Click on the datasheet’s last line, where the record pointer is an asterisk.
• Click the new record Navigation button (the furthest button on the right).
• Click the New command from the ribbon’s Records group.
• Click the Goto ⇒New command from the ribbon’s Find group.
• Move to the last record and press the down-arrow (↓) key.
• Press Ctrl++ (plus sign).
Once you move to a new record, enter data into the desired fields and save the record.
Deleting Records
To delete records, select one or more records using the record selectors, then press the Delete key or click the Delete command in the ribbon’s Records group. The Delete command’s drop-down contains the Delete Record command, which deletes the current record, even if it’s not selected. When you press Delete or choose the ribbon command, a dialog box asks you to confirm the deletion (see Figure 6-11). If you select Yes, the records are deleted. If you select Cancel, no changes are made.
The Default value for this dialog box is Yes. Pressing the Enter key automatically deletes the records. If you accidentally erase records using this method, the action can’t be reversed.
Figure 6-11
The Delete Record dialog box warns you that you are about to delete x number of records—the default response is YES (OK to delete) so be careful when deleting records.
If you have relations set between tables and checked Enforce Referential Integrity—for example, the tblContacts (Customer) table is related to tblSales—then you can’t delete a parent record (tblContacts) that has related child records (in tblSales) unless the you also check the Cascade Delete check box. Otherwise, you receive an error message dialog box that reports The record can’t be deleted or changed because the table ‘<tablename>’ includes related records.
To select multiple contiguous records, click the record selector of the first record that you want to select and drag the mouse to the last record that you want to select. Or click to select the first record, then hold Shift and click on the last record you want in the selection.
Displaying Records
A number of techniques can increase your productivity when you add or change records. Change the field order, hide and freeze columns, change row height or column width, change display fonts, and change the display or remove gridlines to make data entry easier.
Changing the field order
By default, Access displays the fields in a datasheet in the same order that they appear in a table or query. 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 6-12).
Figure 6-12
Selecting and dragging a column to change the field order
You can select and drag columns one at a time, or select multiple columns to move at the same time. Suppose you want QtyInStock to appear before Description in the tblProducts datasheet. Follow these steps to make this change:
1. Position the mouse pointer over the QtyInStock column heading.
The cursor changes to a down arrow.
2. Click to select the column.
The entire QtyInStock 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 Product ID and Description field.
A thin black column appears between them (see Figure 6-12).
6. Release the mouse button.
The column moves in front of the Description 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.
Moving fields in a datasheet does not affect the field order in the table design.
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 drag 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.
You can instantly resize a column to the best fit (based on the longest visible data value) by double-clicking the right column border after the cursor changes to the double arrow.
Resizing the column doesn’t change the number of characters allowed in the table’s field size. You are simply changing the amount of viewing space for the data contained in the column.
Alternatively, you can resize a column by right-clicking the column header and selecting Column Width from the pop-up menu to display the Column Width dialog box, as shown in Figure 6-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 on Best Fit to size the column to the widest visible value.
Figure 6-13
The Column Width dialog box
You can hide a column by dragging the column gridline to the gridline of the next column to the left, or by setting the column width to 0 in the Column Width dialog box. If you do this, you must choose More⇒Unhide Columns in the ribbon’s Records group to redisplay the columns.
Changing the record display height
Sometimes you may need to increase the row height to accommodate larger fonts or text data displays of multiple lines. Change the record (row) height of all rows by dragging a row’s border to make the row height larger or smaller, or you can choose More⇒Row Height in the ribbon’s Records group.
When you drag a record’s border, the cursor changes to the vertical two-headed arrow you see at the left edge of Figure 6-14.
Figure 6-14
Changing a row’s height. Simply put the mouse pointer between two rows. When the mouse pointer changes to arrows pointing up and down, drag to the desired height.
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 upward to shrink all row heights, or drag the border downward to increase all row heights.
The procedure for changing row height changes the row size for all rows in the datasheet. You can’t have rows with different heights.
You can also resize rows by choosing More⇒Row Height in the ribbon’s Records group. 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.
If you drag a record’s gridline up to meet the gridline immediately above it in the previous record, all rows are hidden. This also occurs if you set the row height close to 0 (for example, a height of 0.1) in the Row Height dialog box. In that case, you must use the Row Height dialog box to set the row height to a larger number to redisplay the rows.
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 ribbon’s Font group (shown in Figure 6-15) to change the datasheet’s text appearance.
Figure 6-15
Changing the datasheet’s font directly from the ribbon. Choose font type style, size, and other font attributes for the entire datasheet.
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 ribbon’s Font group (shown in Figure 6-15). Choose from the following options in the Gridlines drop-down list:
• Gridlines: Both
• Gridlines: Horizontal
• Gridlines: Vertical
• Gridlines: None
Use the Fill Color and Alternate Fill/Back Color drop-down lists to change the background colors of the datasheet. The Fill Color palette changes the color of the odd-numbered rows in the datasheet. The Alternate Fill/Back Color palette changes the color of the even-numbered rows. If you don’t want alternating row colors, select No Color from the Alternate Fill/Back Color palette and the even-numbered rows will match the odd-numbered rows.
The Datasheet Formatting dialog box (shown in Figure 6-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 ribbon’s Font group. Use the Flat, Sunken, and Raised radio buttons under Cell Effect to change the grid to a 3D 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.
Figure 6-16
The Datasheet Formatting dialog box. Use this dialog box to customize the look of the datasheet.
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
• Dash-Dot
• Dash-Dot-Dot
• Dashes
• Dots
• Double Solid
• Short Dashes
• Solid
• Sparse Dots
• Transparent Border
Figure 6-17 shows a datasheet with dots 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.
Figure 6-17
Different line styles and row colors for the datasheet
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). To change the alignment of the data in a column, follow these steps:
1. Position the cursor anywhere within the column that you want to change the alignment.
2. Click on the Align Left, Align Center, or Align Right commands in the ribbon’s Font group (shown in Figure 6-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. To hide a single column, follow these steps:
1. Position the cursor anywhere within the column that you want to hide.
2. Choose More⇒Hide Columns in the ribbon’s Records group.
The column disappears. Actually, the column width is simply set to 0. You can hide multiple columns by first selecting them and then choosing More⇒Hide Columns.
After you’ve hidden a column, you can redisplay it by choosing More⇒Unhide Columns in the ribbon’s Records group. This action displays a dialog box that lets you unhide columns selectively by checking next to each field. When you’re finished, click Close; the datasheet appears, showing the desired fields. 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 fields but want to keep certain fields from scrolling out of view, choose More⇒Freeze in the ribbon’s Records group. 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; other fields scroll out of sight horizontally. 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.
When you unfreeze columns, the column doesn’t move back to its original position. You must move it manually.
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 of 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; choose No to preserve the layout when you opened the table. Save the layout changes manually by clicking Save on the Quick Access Toolbar.
If you’re following the example, don’t save the changes to tblProducts.
Saving a record
Access saves each record when you move off it. Pressing Shift+Enter or selecting Save from the ribbon’s Records group saves a record without moving off it. Closing the datasheet also saves a record.
Sorting and Filtering Records in a Datasheet
The ribbon’s Sort & Filter group (shown in Figure 6-18) lets you rearrange the order of the rows and narrow down the number of rows. Using the commands in this group, you’ll display the records you want in the order you want them. The following sections demonstrate how to use these commands.
Figure 6-18
The Sort & Filter group. Change the record order and narrow the number of rows using commands in this group.
Using the QuickSort feature
Sometimes you may simply want to sort your records into 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, then click Ascending (A–Z) or Descending (Z–A). The data redisplays instantly in the sorted order. Right-clicking on a column and selecting either Sort A to Z or Sort Z to A also sorts the data.
To sort your data on the basis of values in multiple fields, highlight more than one column: Highlight a column (as previously discussed), hold down the Shift key, and drag the cursor to the right. 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, as discussed earlier in this chapter.
To display the records in their original order, use the Clear All Sorts command.
Using Filter by Selection
Filter by Selection lets you select records on the basis of the current field value. For example, using tblProducts, move your cursor to the Category column and click the Ascending (A to Z) command. Access sorts the data by the vehicle’s category. Now place your cursor in the Category column with the value Minivans. Press the Selection command in the ribbon’s Sort & Filter group and choose Equals “Minivans”; Access displays the records where the Category is Minivans. Access gives you four choices when clicking the Selection command:
• Equals “Minivans”
• Does Not Equal “Minivans”
• Contains “Minivans”
• Does Not Contain “Minivans”
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 does not go away; it is 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.
Right-click the field content that you want to filter by and then select from the available menu choices.
If you want to further specify a selection and then see everything that doesn’t match that selection (for example, where Description doesn’t equal 2003 Mini Van), move the cursor to the field (the Description field where the value is 2003 Mini Van) that you want to say doesn’t match, right-click on the datasheet, and then select Does Not Equal “2003 Mini Van”. You are now left with six records—all minivans except the 2003 Mini Van.
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 6-19 shows the filtered datasheet.
Figure 6-19
Using Filter by Selection. In this case, you see all records for minivans except 2003 Mini Van records.
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 on the indicator to specify additional criteria for the column using the pop-up menu shown in Figure 6-20. Click on the column heading’s down-arrow for an unfiltered column to display a similar menu.
Figure 6-20
Filtering the Category field. Use the column filter menu to select criteria for a field.
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 appears in the column. In this case, the choices are (Select All), (Blanks), Minivans, Cars, Motor Homes, SUV, and Trucks. 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. Click on Minivans and Cars to display the records where Category is equal to Minivans or Cars.
If you want to filter data but you can’t find the value that you want to use, but 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.
Using Filter by Form
Filter by Form lets you to enter criteria into a single row on the datasheet. Each field becomes a combo box enabling you to select from a list of values in that field. An Or tab at the bottom of the window lets you specify OR conditions for each group. Choose Advanced⇒Filter by Form in the ribbon’s Sort & Filter group to enter Filter by Form mode, shown in Figure 6-21.
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 field is Minivans or Cars, select Minivans from the Category drop-down list, click the Or tab at the bottom of the window, and then select Cars 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.
Figure 6-21
Using Filter by Form lets you set multiple conditions for filtering at one time. Notice the Or tab at the bottom of the window.
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 ribbon’s Sort & Filter group to get an actual QBE (Query by Example) screen that you can use to enter more complex criteria.
Chapters 4 and 5 discuss queries and using operators and expression.
Printing Records
You can print all the records in your datasheet in a simple row-and-column layout. In Chapter 9, you learn to produce formatted reports. For now, the simplest way to print is to click the Print icon in the Quick Access toolbar. This prints the datasheet to the Windows default printer. Click on the Microsoft Office Button to view other print options, shown in Figure 6-22.
Figure 6-22
The Microsoft Office Print menu
The printout reflects all layout options that are in effect when the datasheet is printed. Hidden columns don’t print. Gridlines print only if the cell gridline properties are on. The printout also reflects the specified row height and column width.
Only so many columns and rows can fit on a page; the printout takes up as many pages as required to print all the data. Access breaks up the printout as necessary to fit on each page. For example, the tblProducts printout may be six pages. Three pages across are needed to print all the fields in tblProducts; each record requires three pages in length. Each record of tblContacts may need four pages in length. The number of pages depends on your layout and your printer.
Printing the datasheet
You can also control printing from the Print dialog box, which you open by clicking the Microsoft Office Button, and then clicking on Print. From this dialog box, customize your printout by selecting from several options:
• Print Range: Prints the entire datasheet or only selected pages or records
• Copies: Determines the number of copies to be printed
• Collate: Determines whether multiple copies are collated
You can also click the Properties button and set options for the selected printer or select the printer itself to change the type of printer. The Setup button allows you to set margins and print headings.
Using the Print Preview window
Although you may have all the information in the datasheet ready to print, you may be unsure of whether to change the width or height of the columns or rows, or whether to adjust the fonts to improve your printed output. To preview your print job, either click the Print Preview command under the Print menu to display the Print Preview window. The default view is the first page in single-page preview. Use the ribbon commands to select different views and zoom in and out. Click Print to print the datasheet to the printer. Click the Close Print Preview command on the right side of the ribbon to return to Datasheet View.
Summary
In this chapter, you learned how to open and navigate around in a datasheet using the keyboard, ribbons, and navigation buttons. You learned to enter new records and edit data in existing records, as well as how to undo changes you made to the data. You saw what happens when Access validates each field based on its data type.
You also customized the fonts, colors, column widths, row heights, and other visual aspects of the datasheet. You froze and unfroze columns and hid them from view. Then, you limited the number of records using different types of filters and sorted the records using the QuickSort commands.