Access Basics:
Table: Datasheet View

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007/2010/2013


The Datasheet View shows the records in a table.  Each row is one record. The columns are the fields from the table's definition. This table looks a lot like a spreadsheet, but it does not behave quite like a spreadsheet.

Datasheet View: Star Wars

Example: starwars.mdb

The gray box at the left of a row is the record selector. Click it to select the whole record. The record selector is blank except in two cases. An arrow Arrow: Current Record indicates the current record. An asterisk Asterisk marks the row for a new record marks the row for entering a new record.

The gray boxes across the top are column selectors. Each column is labeled with the name of the field whose values are in the column.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics  
    Access Objects Arrow: subtopic open
    Icon: StepTable: Design View
    Icon: StepTable: Datasheet View
    Icon: StepManage a Table
    Icon: StepSort & Filter a Table
    Icon: StepQuery Wizard
    Icon: StepAutoForm
    Icon: StepAutoReport
    Icon: StepData Access Page
        About Printing
    Icon: StepPrint Objects
    RelationshipsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics

Project 3: Tables & Queries

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



The Default Datasheet Layout:

  • Fields are in the same order as in the Table Design View.
  • All rows are the same height = 12.75 pts.
  • All columns are the same width = 15.667 pts (1 inch)
  • The default font for the data is Arial, 10 pts. , black text on white background.

Unlike a spreadsheet, there is only one blank row, the last one. The asterisk * in the row header at the left indicates that the row is for a new record. Only the fields with default values will show anything in the new record row.

Change default datasheet layout: You can change the datasheet defaults in the Options dialog on the Datasheet tab. (Tools | Options | Datasheet). These choices apply to all datasheets.
If a datasheet is open, you will have to close it and reopen it for your changes to be applied.

Dialog: Options | Datasheet

Icon: Access 2002 Access 2002: Doers not have the tab Error Checking.


Navigating Records

Navigation bar:
At the bottom of the Table Datasheet window is a navigation bar with buttons to help you move through the records. These buttons are especially helpful when there are a large number of records.

Navigation Bar - labeled

The New Record button Button: New Record moves you to a blank record at the end of the table.
The Last Record button Button: Last Record moves you to the last existing record.

Methods to Navigate a Datasheet:

  • Type the row number of the record you want in the navigation box.
        Icon: ConfusionPoint of Confusion: The row number may not be the same as the AutoNumber field used as a primary key! The records may be sorted or filtered. When records are deleted, the AutoNumber value does not change so there may be gaps in the numbering.

  • Click a navigation arrow.

  • Scroll to see the record that you want.

  • Icon: Keyboard Using keystrokes:

TAB or ENTER next field to the right.
From the last field in a record, moves to the first field in the next record.
SHIFT + TAB backs up one field to the left
HOME first field in current record
END last field in current record
CTRL + HOME first field in the first record
CTRL + END last field in the last record
Arrow keys one field to the left/right
one record up/down in the same column.

Changing Defaults for Keys:
The default behaviors for the ENTER and arrow keys can be changed in the Options dialog on the Keyboard tab. (Tools | Options | Keyboard)

Dialog: Options  - Keyboard tab


Undo Changes: Adding or Editing Records

Unhappily, MS Access does not have a list of actions that you can Undo when adding or editing records in a datasheet or form. 

  • Undo button Button: Undo: Reverses your most recent change, which is all of the edits you made since the last time you saved the record. This can be the whole record!
  • ESC button: Removes changes to a field, if you have not yet left the field.
  • Edit menu commands: Undo Saved Record or Undo current field/record will reverse changes that have been saved - for the whole record.

Once you switch to a different window, apply or remove a filter, or change to a different record, you cannot Undo anything about the record you were last working on.


Icon Step-by-Step 

Step-by-Step: Table Datasheet View

 Icon Step-by-Step

What you will learn:

to open an existing database from File menu
to add records
to save a changed record
to navigate from record to record and field to field


Start with: Access open but no database open, mytrips.mdb from previous lesson

Open an Existing Database from Menu

  1. Menu: File | mytrips.mdb (2003)Menu: File | mytrips.mdb (2002)Click on  File  from the Menu bar.
     
  2. At the bottom of the File menu, click on mytrips.mdb, which will be the #1 document if you have just finished the previous lesson.
     
    Icon: TroubleProblem: Do not see mytrips.mdb in the list:
    You either have not done the previous lesson or you have opened enough databases since then to move mytrips.mdb off of the list.

    Solution: Either create the database - previous lesson
    OR, Choose Open on the File menu instead, and navigate to where you saved mytrips.mdb and open it.
     

Add a Record

Now you can put in your first record. It's not much fun working directly in the datasheet, but it can be done! The TAB key is your friend here! It will move you to the next field to the right.

  1. With the table design view open, click on Button: Views (Datasheet) the View button on the toolbar, which currently shows the Datasheet view icon. The blank datasheet for the Trips table opens. The first field is highlighted, TripID.

    View: Datasheet (Trips table) - blank

    The only field with anything in it is Cost, which show the default value of zero. None of the other fields have a default value.
     
    Icon: Trouble Problem: Cannot type in field TripID: Since that field is an AutoNumber, you cannot type in anything. Access will automatically enter a number once you have data in the record. If you try to type in an AutoNumber field, you will hear the error sound and the Status bar displays an error message:
     Status Bar: Control can't be edited; it's bound to AutoNumber field 'ItemID'
     

  2. Press the TAB key. The cursor moves from the TripID field to the next field to the right, TripName.
     
  3. Type the data below, using the TAB key to move on to the next column:
    Note: The datasheet columns cannot show all of the text in the memo fields. That is OK! It's still there. Just keep typing. Later we will create a form that will handle this better.
     
    TripName Kauai
    DateStarted 2/26/1990
    DateEnded 3/5/1990
    Description Convention. Flew TWA from Nashville to Honolulu, then Hawaiian Air to the island of Kauai. Stayed at the Westin Kauai.
    Cost $950.00
    Travel Agent Arranged through company sponsoring the convention.
    Diary Just us two. Beautiful weather. Rented car and drove around the island, well… as far as you can get on a road! Lovely canyon park - Grand Canyon of Hawaii. Smelled and looked just like the Great Smokey Mountains. Who knew! Flew over to the Big Island and and took a helicopter tour of the volcano. It was erupting with smooth lava flow that fell into the sea! Such heat and steam!!

    View: Datasheet (Trips table) - one record entered

    Your datasheet window may not be wide enough to show all of the fields at once.
     

  4. Close the table by clicking Button: Close its Close button, but do not close the database.

Open Existing Table

  1. In the Database Window, double-click on the Trips table.
     
    mytrips.mdb showing Trips Datasheet with one recordThe table opens in Datasheet View with the first field in the first record selected.
     

Add More Records

A table with only 1 record is not of much use. You need to add some more trips!

  1. At the bottom of the window, click on Button: Next Record the Next Record arrow to move to the next record. The cursor appears in the first field of the blank record.
    You cannot type in this first field because it is an AutoNumber field.
     
  2. Press the TAB key to move to the second field, TripName.
     
  3. Type the following in the appropriate fields to create a new record:
     
    TripName Alaska cruise
    DateStarted 8/7/2005
    DateEnded 8/14/2005
    Description Norwegian Star, port Seattle. Flew in on Southwest through Dallas.
    Cost $5,500.00
    Travel Agent Jorge Martinez, World Travel Inc, jmartinez@worldtravelinc.net
    Diary Just us two. Rainy to start but the Inside Passage was smooth and beautiful. Saw Ketchikan, Juneau, and Skagway. Jumping salmon right by the ship. Mendenhall Glacier in Juneau was so cool! We took a boat out to look for whales and saw three different kinds.

Some columns are too narrow to show all of the text. Some are wider than needed. You will work on that in the next lesson.


Save Records

Your new records are saved as you go!

  1. Save the changes by moving to another record. (You can TAB or use arrow keys or just click in another record).

    When you leave a record, all of your data changes are automatically saved!!

    The Status bar has the message Calculating... Status bar: Message area = Calculating while it is saving the record. Did you see it? For small tables the process can be too fast to show the message.

WarningA database programmer can change Access' behavior so that records are not automatically saved. Know your database!!

TipTo save data changes without leaving the record:
                 
from the menu,  Records | Save Record
        Icon: Keyboard from the keyboard, SHIFT + ENTER.


Add Another Record

  1. Type the following in the appropriate fields to create a new record:
     
    TripName Disney World
    DateStarted 6-4-03
    DateEnded 6-11-03
    Description Drove to Orlando. Stayed at Disney Polynesian Resort.
    Cost $5,000.00
    Travel Agent none
    Diary Left 2 hours late due to a flat tire. Arrived in Orlando in a rain storm. Reservations were not correct, but the desk clerk was very cooperative and straightened it out. No yelling required! Kids slept like logs but woke up at first light. So excited!!

Note that when you type the date as 6-4-03, it is automatically reformatted to the style mm/dd/yyyy.

Datasheet view: Trips - 3 records

Trips table after entering more trip records


Navigating Records

There are several methods for navigating between fields and between records. Now that you have some records in your table, you can do a little practice.

  1. Practice moving from field to field with the TAB key and the other keystroke methods listed at the top of this page.
     

  2. Practice moving from record to record with the navigation buttons at the bottom of the Datasheet window.


LessonsWorking with Databases Previous Page Next Page




Teachers: Request permission to use this site with your class
 
Copyright © 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?


~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~


Last updated: 30 Apr 2012