Learn how to add, edit, or delete records

The information that you keep in a database is stored in tables, which contain data about a particular subject, such as assets or contacts. Each record in a table contains information about one item, such as a particular contact. A record is made up of fields, such as name, address, and telephone number. A record is commonly called a row, and a field is commonly called a column. Microsoft Office Access 2007 allows you to add, edit, or delete records in a table directly in Datasheet view. If you designed and created a select query, you can view and often edit the records directly in the datasheet that results from running the query. You can also create a form that is based on any table or query and then use the form to add or edit records.

In this article

Overview

Add a new record

Find a record to edit or delete

Edit an existing record

Delete a record

When is data saved?

How to undo changes

Overview

When you create a database that is based on a template, your initial tables, queries, and forms are automatically created for you. This article explains how to add, edit, and delete records when you are using a database that was created from one of the templates provided with Access.

For information about how to add, edit, and delete records in a database that was not created from a template included in Access, see the following articles:

• Add one or more records to a database

• Delete one or more records from a database

• Update the data in a database

For information about how to use the Collect and Update Data Using E-mail feature, see the article Collect data by using e-mail messages.

To learn how to import data into Access, see these articles:

• Import or link to data in another Access database

• Import or link to data in an Excel workbook

• Import from or link to a SharePoint list

• Import or link to data in a text file

For information about how to add a new field or column in a table, see the article Insert, add, or create a new field in a table.

Top of Page

Add a new record

You add a record to your database when you have a new item to track. For example, you might want to add a new contact to the Contacts table when you have a new friend or colleague whose information you want to record. When you add a new record, Office Access 2007 appends the record to the end of the table.

To add a record, you first open a table or form. Sometimes, a form is automatically opened for you when you open the database. For example, when you open a database that was created from the Contacts template, the Contact List form opens automatically. The Contact List form is the startup form for the Contacts database.

The following sections explain several ways that you can add a new record by using Datasheet view or Form view.

Use a template's startup form

When you create a new database that is based on a template provided with Access, a startup form is typically loaded when you first open that database. For example, if you create a database based on the Contacts template, the Contact List startup form is loaded. The startup form is intended to be a launching point for navigating and using the database. Many of these templates, but not all, allow you to click a button provided for adding new records on the startup form. For example, the Contact List form has a New Contact button, and the Issues List form has a New Issue button.

When you click such a button, a form opens in which you can enter the appropriate information. For example, when you click the New Contact button, the Contact Details form opens.

Add a record by using the New button

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. When the startup form appears, click the button provided for adding a new record.

   For example, in the Contact List form, click New Contact. The Contact Details form opens.

4. Fill in the form. Enter your information in each field, pressing TAB to move to the next field, or pressing SHIFT+TAB to move to the previous field.

   Note: To start a new line in a field that supports more than one line of text, such as a Text field or Memo field, press CTRL+ENTER. You can open the Zoom box if you want more space in which to edit. To open the Zoom box, click the field that you want to edit so that the cursor is in the field and the field is selected. Then, press SHIFT+F2.

5. When you finish entering records, click OK to save the information or, if you want to add additional records, click Save and New, if it is available.

If you are using the Contact List form, you can add a new record by clicking Add From Outlook and then selecting the record from your Microsoft Office Outlook 2007 Address Book.

Add a record by using the Add From Outlook button

Some of the templates include an Add From Outlook button that lets you add a record from your Office Outlook 2007 address book. Note that this button is a feature of certain templates, and is not present by default when you create a new form or table.

1. Click Add From Outlook.

2. If the Choose Profile dialog box appears, select a profile name in the Profile Name box, and then click OK.

   Note: You might see a dialog box that says the following: A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may be a virus and you should choose "No.". It is normal to see this dialog box after you click Add From Outlook, so do not click No. If you plan to choose more than one contact, you might want to select the Allow access for check box and then choose a short period of time, such as 5 minutes. Doing so instructs Outlook to allow access to your stored e-mail addresses for the period of time that you specify. When you finish, click Yes.

3. In the Select Names to Add dialog box, double-click the names that you want to add, and then click OK.

   The names are added to the form.

Save a contact record to your Outlook address book

Some of the templates include a Create Outlook Contact button that lets you save a record as a contact in your Outlook address book. Note that this button is a feature of certain templates, and is not present by default when you create a new form or table.

1. Click Create Outlook Contact.

2. If the Choose Profile dialog box appears, select a profile name in the Profile Name box, and then click OK.

3. Outlook opens a new contact form containing data from the record that you selected. Check the data in the new contact form, and make any required changes or additions.

4. On the Contact tab of the new contact form, in the Actions group, click Save & Close.

Some of the templates expose a Save As Outlook Contact command on the Ribbon, part of the Microsoft Office Fluent user interface, that lets you save a contact record to your Outlook address book. Note that this command is a feature of certain templates and is available only when you are either using a table that stores e-mail addresses or when you are using a query or form, based on such a table, in a database that is based on certain templates.

1. Select the record that you want to save as an Outlook contact.

2. On the Home tab, in the Records group, click More, and then click Save As Outlook Contact.

3. If the Choose Profile dialog box appears, select a profile name in the Profile Name box, and then click OK.

4. Outlook opens a new contact form containing data from the record that you selected. Check the data in the new contact form, and make any required changes or additions.

5. On the Contact tab of the new contact form, in the Actions group, click Save & Close.

Add a record to the startup form

Depending on the template used, you can sometimes enter information directly into the startup form. Access most often displays any existing information in a datasheet. Directly below the last row of data is a new, blank record with an asterisk (*) displayed in the record selector. Enter the information in the record, preceded by the asterisk. Note that if default values are assigned to any fields, the new record will appear with some default values already assigned. To quickly get to the new record at the end of the table, you can click the New (blank) record navigation button, or click New Record in the Records group on the Home tab.

1. Click the Microsoft Office Button , and then click Open.

2. In the Open dialog box, select and open the database.

3. When the startup form appears, if there is no existing data, enter the information in the new, blank record that appears. If there is existing information, do one of the following:

   • On the Home tab, in the Records group, click New.

   • Click New (blank) record, if it is available.

   • Find the record with an asterisk in the record selector, and enter the information there.

4. Fill in the record. Enter your information in each field, pressing TAB to move to the next field, or pressing SHIFT+TAB to move to the previous field.

   Note: To start a new line in a field that supports more than one line of text, such as a Text field or Memo field, press CTRL+ENTER. If you want more space in which to edit, you can open the Zoom Box. To do so, with the cursor in the field that you want to edit, press SHIFT+F2.

5. After you fill in all the fields, press TAB to move to a new, blank record.

   When you view another record or close the form, Access saves the new record that you added.

Use a table or form from the Navigation Pane

In addition to entering a new record by using the template's startup form, you can open a table or form from the Navigation Pane and then enter a new record.

Open a table or form

• In the Navigation Pane, double-click the item that you want to open (or click once if you have changed the Open Objects with option in the Navigation Options dialog box).

  The table or form opens in its default view.

Any existing information will be displayed, most often in rows in a datasheet. Below those rows, a new, blank record with an asterisk is displayed in the record selector. Enter your new information in that row.

Add a record to a table or form    

When the datasheet or form appears, if there is no existing data, enter the information in the new, blank record that appears. If there is existing information, do one of the following:

• On the Home tab, in the Records group, click New.

• Click New (blank) record, if it is available.

• Find the record with an asterisk in the record selector, and enter your new information in that row.

After you fill in all the fields, press TAB to move to a new, blank record.

When you view another record or close the table or form, Access saves the new record that you added.

Why ID numbers sometimes seem to skip a number

When you create a field that is set to the AutoNumber data type, Access automatically generates a value for that field in every new record. The value is incremented by default, so that every new record gets the next available sequential number. The purpose of the AutoNumber data type is to supply a value that is suitable for use as a primary key.

For more information about primary keys, see the article Add, set, change, or remove the primary key.

When you delete a row from a table that contains a field that is set to the AutoNumber data type, the value in the AutoNumber field for that row is not always automatically reused. For that reason, the number that Access generates might not be the number you expect to see, and gaps might appear in the sequence of ID numbers — this is by design. You should rely only on the uniqueness of the values in an AutoNumber field, and not their sequence.

Top of Page

Find a record to edit or delete

You must first find a record before you can edit or delete it. In a table that contains only a small number of records, you can use the record navigation buttons to navigate through the records until you find the one you want. In a larger table, however, you should use other techniques to find the records you want.

Using the record navigation buttons

You can navigate between records by using the navigation buttons. You can click these arrow buttons to navigate to the first, previous, next, or last record, or to add a new record.

1. First record

2. Previous record

3. Current Record

4. Next record

5. Last record

6. New (blank) record

7. Filter indicator

8. Search box

When you click in the Current Record box, you can type a record number and then press ENTER to navigate to that record. The record number is counted sequentially, from the beginning of the form or datasheet — it does not correspond to any field value.

The filter indicator button shows whether a filter has been applied. To remove the filter, you can click this button when it displays Filtered. Similarly, when it displays Unfiltered, you can click this button to apply the last filter that you used.

When you enter text in the Search box, the first matching value is highlighted in real time as you enter each character. You can use this feature to quickly search for a record with a value that matches what you enter in the Search box.

Using the Find feature in the Find and Replace dialog box

You can use the Find feature in the Find and Replace dialog box to locate a matching record. When you find a matching record, that record becomes the current record, and you can then edit or delete it.

Find a matching record

1. Click the field that you want to search.

2. On the Home tab, in the Find group, click Find, or press CTRL+F.

   Keyboard shortcut  Press CTRL+F.

   The Find and Replace dialog box appears.

3. Click the Find tab.

4. In the Find What box, type the value that you want to match.

5. Optionally, use the Look In list to change the field that you want to search, or to search the entire underlying table instead.

6. Optionally, in the Match list, select Any Part of Field. Selecting this option provides the broadest possible search.

7. In the Search list, select All, and then click Find Next.

For more information about using the Find and Replace dialog box, see the article Use the Find and Replace dialog box to change data.

Applying a filter

You can apply a filter to limit the records that are displayed to those that match your criteria. Applying a filter makes it easier to find the record that you want to edit or delete. For example, to quickly narrow the records displayed, right-click a field whose value you want to match, and then click Equals, Not Equals, Contains, or Does Not Contain at the bottom of the shortcut menu.

Apply a filter based on a selection

1. Open a table or form.

2. To ensure that the table or form is not already filtered, on the Home tab, in the Sort & Filter group, click Advanced, and then click Clear All Filters (if that command is available).

3. Navigate to the record that contains the value that you want to use as part of the filter, and then click the field. To filter based on a partial selection, select only the characters that you want.

4. On the Home tab, in the Sort & Filter group, click Selection, and then click the filter you want to apply.

   -or-

   Right-click the field, and then click Equals, Not Equals, Contains, or Does Not Contain at the bottom of the shortcut menu.

5. To filter other fields based on a selection, repeat steps 3 and 4.

To explore other forms of filtering, use the commands in the Sort & Filter group on the Home tab.

For more information about applying a filter, see the article Filter: Limit the number of records in a view.

Top of Page

Edit an existing record

Sometimes, you might want to make changes to an existing record — for example, to change an address. To edit an existing record, you open a form or table, navigate to the record you want to change, and then make your changes.

Open a table or form

• In the Navigation Pane, double-click the item that you want to open (or click once if you have changed the Open Objects with option in the Navigation Options dialog box).

  The table or form opens in its default view.

When you open a form or table, any existing information is displayed, and the first field of the first record is usually selected (highlighted). If you begin typing while data is selected, the characters that you type will replace the selected data.

Replace the value in a field

1. Select the contents of the field that you want to replace.

   Tip: In Datasheet view, you can select a field by clicking near the left border of the field when the mouse pointer becomes a plus (+) sign. In Form view, you can click a field's label to select the field.

2. Type or select the new value for the field.

   The characters that you type or the value you select replaces the value in the field. When you move to another record or close the form or table, your changes are saved.

To enter information in a field, click the field and then place the cursor where you want to insert the information. When the cursor is in the field, you can move the cursor by using the arrow keys.

Note: You can insert the current date in a field by pressing CTRL+SEMICOLON (;). To insert the current time, press CTRL+SHIFT+COLON (:).

Insert new information in a field

1. Click the field (or navigate to the field by using the TAB key or arrow keys and then pressing F2).

2. Place the cursor where you want to enter information.

3. Type the text that you want to insert. If you make a typing mistake, press BACKSPACE.

   When you move to another record or close the form or table, Access saves your changes.

Top of Page

Delete a record

When you delete a record, the information is permanently deleted from your table. Therefore you should be certain that you want to delete a record before you delete it. If you need to delete only some information but not the entire record, select only the data that you want to delete and then press DELETE.

Delete a record

1. Select the record or records that you want to delete.

   To select a record, click the record selector next to the record, if the record selector is available.

   To extend or reduce the selection, drag the record selector (if it is available), or press SHIFT+DOWN ARROW or SHIFT+UP ARROW.

2. Press DELETE.

   -or-

   On the Home tab, in the Records group, click Delete.

For more information about deleting records, see the article Delete one or more records from a database.

Top of Page

When is data saved?

When you edit data in a form or table, your changes are automatically saved when you navigate to another record or when you close the form or table. When you are editing a record, Access displays the pencil symbol in the record selector on the left side of the current record. When you move to another record, the pencil symbol disappears, indicating that your changes have been saved.

The following table shows some of the record selector symbols you might see.

Symbol	Meaning
	This is the current record; the record has been saved as it appears. The current record is indicated by a change in color in the record selector.
	You are editing this record; changes to the record aren't yet saved.
	This record is locked by another user; you can't edit it.
	This is a new record in which you can enter information.
	This is the primary key field and contains a value that uniquely identifies the record.

Note: If you want to save changes to a record without moving to another record, on the Home tab, in the Records group, click Save Record.

Top of Page

How to undo changes

If you type data incorrectly, you can often undo your changes. For example, when you edit data in a field, you can click Undo on the Quick Access Toolbar, or press ESC to undo your changes. To undo all changes to the record, press ESC again. Even after you save changes or move to another record, you can still undo the changes to the previously edited record by clicking Undo on the Quick Access Toolbar. However, as soon as you begin editing another record, apply or remove a filter, or switch to another window or document tab, your changes become permanent.

Top of Page