ACC2000: How to Edit Records in Related Tables in a Microsoft Access Database (304474)

MORE INFORMATION

How to Add or Edit Data in a Datasheet (Table or Query) or in a Form

1. Open a table or a query in Datasheet view or a form in Form view.
2. Do one of the following:
   • To add a new record, point to Go to on the Edit menu, and then click New Record. Type the data, and then press TAB to go to the next field. At the end of the record, press TAB to go to the next record.
   • To edit data within a field, click in the field that you want to edit, and then type the data.
   • To replace the entire value, move the mouse pointer to the leftmost part of the field until the pointer changes into the plus pointer, and then click. Type the data.
   NOTE: To correct a typing mistake, press BACKSPACE. To cancel your changes in the current field, press ESC. To cancel your changes in the entire record, press ESC again before you move out of the field. When you move to another record, Microsoft Access saves your changes.

How to Save a Record in a Datasheet or in a Form

Microsoft Access automatically saves the record that you are adding or editing as soon as you move the insertion point to a different record or close the form or table that you are working on.

To explicitly save the data in a record while you are editing it, click Save Record on the Records menu.

How to Delete a Record in a Datasheet or in a Form

1. Open a table or a query in Datasheet view or open a form in Form view.
2. Click the record that you want to delete.
3. On the Edit menu, click Delete Record. NOTE: When you delete data, you might want to delete related data in other tables. For example, if you delete a supplier, you probably want to delete the products that the supplier supplies. In some cases, you can make sure that the proper data is deleted by enforcing referential integrity and turning on cascade deletions. Referential integrity and cascade updates and deletes are explained in further detail later in this article.

How to Undo Changes Adding or Editing in a Datasheet or in a Form

Do one of the following:

• To take back your most recent change, click Undo on the Edit menu.
• If you have already saved changes to the current record or have moved to another record, click Undo Saved Record on the Edit menu. NOTE: As soon as you begin editing another record, apply or remove a filter, or switch to another window, you cannot use these methods for correcting changes.

Referential Integrity

Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you do not accidentally delete or change related data. The following rules apply when you use referential integrity:

• You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you cannot have an order that is assigned to a customer that does not exist, but you can have an order that is assigned to no one by entering a Null value in the CustomerID field.
• You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete an employee record from the Employees table if there are orders assigned to the employee in the Orders table.
• You cannot change a primary key value in the primary table, if that record has related records. For example, you cannot change an employee's ID in the Employees table if there are orders assigned to that employee in the Orders table.

Cascade Updates and Cascade Deletes

For relationships in which referential integrity is enforced, you can specify whether you want Microsoft Access to automatically cascade update and cascade delete related records. If you set these options, delete and update operations that would normally be prevented by referential integrity rules are allowed. When you delete records or change primary key values in a primary table, Microsoft Access makes necessary changes to related tables to preserve referential integrity.

If you click the Cascade Update Related Fields check box when you are defining a relationship, any time you change the primary key of a record in the primary table, Microsoft Access automatically updates the primary key to the new value in all related records. For example, if you change a customer's ID in the Customers table, the CustomerID field in the Orders table is automatically updated for every one of that customer's orders so that the relationship is not broken. Microsoft Access cascades updates without displaying any message.

NOTE: If the primary key in the primary table is an AutoNumber field, setting the Cascade Update Related Fields check box will have no effect, because you cannot change the value in an AutoNumber field.

If you select the Cascade Delete Related Records check box when you are defining a relationship, any time that you delete records in the primary table, Microsoft Access automatically deletes related records in the related table. For example, if you delete a customer record from the Customers table, all the customer's orders are automatically deleted from the Orders table (this includes records in the Order Details table related to the Orders records). When you delete records from a form or datasheet with the Cascade Delete Related Records check box selected, Microsoft Access warns you that related records may also be deleted. However, when you delete records using a delete query, Microsoft Access automatically deletes the records in related tables without displaying a warning.

For additional information about relationships, click the article numbers below to view the articles in the Microsoft Knowledge Base:

About Restricting or Validating Data

Microsoft Access provides a variety of ways to control how a user enters data into a database. For example, you can limit the data that a user can enter into a field by defining a validation rule for that field. If the data that a user enters into the field breaks the rule, Access will display a message telling the user what kind of entries are allowed. Another method of controlling data entry is to create an input mask to restrict the kind of values that can be entered in positions across the field. You can perform these simple forms of validation and restriction by setting properties for fields in tables, or by setting properties for controls on forms.

In most cases, it is preferable to define data validation and restriction by setting a field's properties in table Design view. That way, whenever you use that field in a form, the field's validation rule and other properties will apply to data entry performed by using the form.

However, if the data entered into a control on a form is not bound to a field in a table, and you need to restrict or validate data entry, you must define those properties in the form. Additionally, there are situations where you must use macros or Microsoft Visual Basic for Applications code in conjunction with a form to perform more complex validation. For example, you might want to be able to override your validation rule or compare values from different tables.

For additional information about table field properties, click the article number below to view the article in the Microsoft Knowledge Base: For more information about validation in tables and forms, click Microsoft Access Help on the Help menu, type validation in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For additional information about validation in Microsoft Access, click the "Working with data" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site:

About subdatasheets

In a subdatasheet, you can view and edit related or joined data in a table, a query, or a form datasheet, or in a subform. For example, in the Northwind sample database, the Suppliers table has a one-to-many relationship with the Products table; so for each row of the Suppliers table in Datasheet view, you can view and edit the related rows of the Products table in a subdatasheet. Click the expand indicator to expand or collapse a subdatasheet.

For additional information about subdatasheets in Microsoft Access, click the "Working with datasheets and subdatasheets" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site: For more information about subdatasheets in tables and forms, click Microsoft Access Help on the Help menu, type subdatasheets in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

When You Can Update Data from a Query

In some cases, you can edit data in query Datasheet view to change the data in the underlying table. In other cases, you cannot. The following information shows whether a query's results can be updated, and if not, whether there is an alternative.

Data is updatable:

You can update a query or query field in the following cases:

• A query based on one table
• A query based on tables with a one-to-one relationship
• The query's results contain a Memo, Hyperlink, or OLE Object

Data is updatable under certain conditions:

If a query is based on tables with a one-to-many relationship, you might not be able to edit the data for the following query fields.

Query field	Solution
Join field from the "one" side	Enable cascading updates between the two tables.
New records, if the "many" side join field doesn't appear in the datasheet	Add the join field from the "many" side to your query to allow adding new records.
Join field from the "many" side, after you've updated data on the "one" side	Save the record; then you'll be able to make changes to the "many" side join field.
Blank field from the table on the "one" side of a one-to-many relationship where an outer join exists	Enter values in fields from the table on the "many" side, but only if the joined field from the "one" side contains a value for that record.
New records, if entire unique key of ODBC table isn't output	Select all primary key fields of ODBC tables to allow inserts into them.

Data can be deleted but not updated:

Query or query field	Solution
Query (or underlying table) for which Update Data permission isn't granted	To modify data, permissions must be assigned.
Query (or underlying table) for which Delete Data permission isn't granted	To delete data, permissions must be assigned.

Data can't be updated:

Query or query field	Solution
Query based on three or more tables in which there is a many-to-one-to-many relationship	Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
Crosstab query	None
SQL pass-through query	None
Query that calculates a sum, average, count or other type of total on the values in a field, or an update query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions	By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
Union query	None
Query whose UniqueValues property is set to Yes	None
Query that includes a linked ODBC table with no unique index, or a Paradox table without a primary key	None
Query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view	You must join the tables properly in order to update them.
Calculated field	None
Field is read-only; the database was opened as read-only or is located on a read-only drive	None
Field in record that has been deleted or locked by another user	A locked record should be updatable as soon as it is unlocked.