Add, edit, and delete data

When you want to add to or make changes to your data in your Microsoft Office Access 2003 database, you can do much of your work in forms (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.) and datasheets (datasheet: Data from a table, form, query, view, or stored procedure that is displayed in a row-and-column format.). After you design your tables and queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.), you can use a form or datasheet as a window into the data. You can also use a data access page (data access page: A Web page designed for viewing and working with data from the Internet or an intranet. Its data is typically stored in an Access database.) to add or edit data in a database that is on an intranet (intranet: A network within an organization that uses Internet technologies (such as the HTTP or FTP protocol). By using hyperlinks, you can explore objects, documents, pages, and other destinations on the intranet.) or on the Internet (Internet: A worldwide network of thousands of smaller computer networks and millions of commercial, educational, government, and personal computers. The Internet is like an electronic city with virtual libraries, stores, art galleries, and so on.).

This topic explains how to use forms, datasheets, and data access pages for adding and modifying your data. It also provides examples of each method, working with the Northwind Traders sample database.

ShowI can't find the Northwind Traders database

The default folder location of the Northwind Traders sample database is as follows, for these versions of Access:

  • Access 2003    \Program Files\Microsoft Office\Office11\Samples
  • Access 2002    \Program Files\Microsoft Office\Office10\Samples
  • Access 97 and 2000    \Program Files\Microsoft Office\Office\Samples

Open a table or query in Datasheet view

Access provides a Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.) of each table and query. You can use this view to add, edit, or delete data. When you open a table in Datasheet view, Access saves your changes directly in the table. When you open a query in Datasheet view, your changes are saved in the query's underlying tables.

Categories table in Datasheet view

Open a form in Form view

You can create a form based on one or more tables and queries, and then use the form to add or edit the data in the underlying tables. Forms let you customize the user interface for data entry, and using forms is widely regarded as the best way of working with your data.

Categories form in Form view

If a form includes a subform (subform: A form contained within another form or a report.), you can add, edit, or delete its data if the subform's properties support those operations.

For more information about creating and working with forms and subforms, see the following topics:

About forms

Create a form

Create a subform

Open a data access page in your Web browser or in Page view

You create a data access page by using Access, but you can view it either in Access or in a Web browser, such as Microsoft Internet Explorer. Create a data access page if you want to allow users who do not have Access installed to add, edit, or delete data in an Access database that is on an intranet or on the Internet.

Categories data access page in Page view

You can also open a data access page in Page view to add, edit, or delete data in the underlying tables.

For more information on creating and working with data access pages, see these topics:

About data access pages

Create a data access page

Update multiple records at once

You can also bulk update or delete records in a table by using queries.

For more information on using queries to modify data, see these topics:

Create an update query (MDB)

Find, eliminate, or hide duplicate records in Access

Update one table based on another table (MDB)

Example: Changing the address of a customer

You are notified by a customer that their address has changed, so you want to update the data in your Northwind Traders database.

  1. Open the Customers form in the Northwind Traders database.
  2. Click Next until you see the customer record.

Customers form

The record indicator symbol, in the left margin of a form or a data access page, indicates the status of the current record. If you don't see the record indicator, make sure that the form's RecordSelectors property is set to True, or that the RecordSelector property of a page's group is set to Yes.

The three record indicator symbols that you will see most frequently are shown here.

Symbol  Meaning
Current record symbol Current record, no unsaved changes
Editing pencil symbol Current record, being edited
Locked record symbol Current record, locked by another user and not available for editing
  1. Click inside the Address field, and replace the current text with the new address.

The current record indicator symbol changes to the editing indicator symbol to show that the record has unsaved changes.

 Note   In a multi-user environment, when a record is locked by another user, the current record indicator changes to the locked indicator symbol to indicate that the record can't be changed.

  1. Click Previous, and then click Next to revisit the edited record. When you leave an edited record, Access validates its contents and saves the record. The next time you view the record, you will see the changes that you made.

Here are some topics that you might want to read before adding, editing, or deleting data. These topics cover tasks that you often perform while adding or modifying data:

Find the record you want

Move between records or fields

Undo your changes

Repeat the value from the previous record

Insert a default value in a field

Add or edit a hyperlink value

Use images in your Access forms, reports, and controls

Copy or move data

Keyboard shortcuts for working with data

Prevent a data-entry form or page from showing existing records

Specify whether a user can add, edit, or delete data

Calculate a value using a formula

Validate or restrict data entry in tables (MDB)

Add a record

  1. To add a record, do one of the following:
    • If you are working with a form, open the corresponding form in Form view.
    • If you are working with a data access page, open the corresponding page in Page view. If Access is not installed on your computer, open the page in your Web browser.
    • If you are working with a table, open the corresponding table in Datasheet view.
  2. In Datasheet or Form view, click the New button on the navigation toolbar. To add a record to a subform, click New on the subform's navigation toolbar. If you don't see the navigation toolbar inside the subform, or if the button is disabled, you will not be able to add records to the subform. In a data access page, click New on the record navigation toolbar.

 Note   If the New button appears disabled on a page, it means that you do not have permissions to add new records. Contact the owner of the database. If you don't see the New button on a data access page, it either means that the page is read-only, or that the designer of the page has customized the appearance of the button. Look for custom instructions about using the page, or contact the page's designer.

  1. Enter your data, and then press TAB to go to the next field.

Tips    

  • To start a new line in a field that supports multiple lines of text, press CTRL+ENTER.
  • To prevent Access from automatically completing the value as you type in a combo box, set the control's AutoExpand property to No.

When you try to move out of a field after entering data, Access validates the data, making sure that the value you entered is allowed in that field. If the value isn't allowed, Access alerts you with a validation message.

For more information, see Troubleshoot editing data in a field.

To move out of a field after you see a validation message, you must either change the data to an acceptable value or undo your changes by pressing the ESC key. This is known as field-level validation.

  1. When you have finished entering data in each of the fields in the record, click New to add another record, or move to a different record.

 Note   You cannot insert a new record between two existing records. However, you can rearrange the order of records by sorting them.

When you move to a different record, Access first tests whether the entire record can be saved (record-level validation), and then saves your changes. If the record can’t be saved, you must either make any necessary corrections or undo your changes.

Edit a record

You can change the data in one or more fields in a record. For example, if you want to correct the spelling of a customer name, you can open the Customers form in Form view, move to the record that corresponds to the customer, and edit the Name field in the form. When you move to a different record, Access will automatically update the field in the underlying table.

  1. Open the form (in Form view), page (in your Web browser or in Page view), or table (in Datasheet view) that has the record that you want to edit.
  2. Move to the record by using the buttons on the navigation toolbar.
  3. Use the mouse or the TAB or arrow keys to move to the field that you want to edit.

To replace the entire contents of a field in a datasheet, click near the left edge of the field when the pointer turns to a plus, as shown in the following example. In a form, click the field's label.

Selecting a field in Datasheet view

To add to the existing contents of a field, place the insertion point where you want to enter the new characters. You can move the insertion point by using the arrow keys. In the following example, the insertion point is at the end of the current contents of the field, so anything that you type will be appended to the string "Alfreds FutterKiste".

Insertion point in Customers form

  1. Enter your text in the field. If text in the field was selected before you started typing, the characters that you type will replace that text.

 Note   If you make a typing mistake, press the BACKSPACE key. If you want to cancel all editing changes to a field, press the ESC key.

When you try to move out of a field after entering data, Access validates the data, making sure that the value you entered is allowed in that field. If the value isn't allowed, Access alerts you with a validation message. For more information, see Troubleshoot editing data in a field.

To move out of the field, you must either change the data to an acceptable value or undo your changes by pressing the ESC key.

When you move to another record, Access performs record-level validation, and then saves your changes. If the record can’t be saved, you must either make any necessary corrections or undo your changes.

Delete the contents of a field or an entire record

You can delete the text in a field to either leave the field blank (if the field supports Null values), or to replace the text with another value. You can also permanently delete one or more records in a table:

  • To delete text in a field, select the text and then press DEL.

For information on how to select a field, see Select fields and records.

  • To delete one or more records in a datasheet or a form, select the records and then press DEL.
  • To delete a record on a data access page, click the Delete button on the record navigation toolbar.

 Note   To temporarily hide one or more records in a view, apply one or more filters.

For more information on filtering data, see Create a filter (MDB).

 
 
Applies to:
Access 2003