ACC: Error Updating Field When Adding New Record to Form

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you enter data into a new record on a form that is based on a query or an SQL statement, you receive one of the following error messages.

In Microsoft Access version 2.0, 7.0 and 8.0

   Field can't be updated.

In Microsoft Access 97

   Field can’t be updated.

   - or –

   The field can't be updated because another user or process has
   locked the corresponding record or table.

CAUSE

You have tried to edit a read-only field in the record using either the DefaultValue property or Visual Basic for Applications.

NOTE: Visual Basic for Application is called Access Basic in Microsoft Access 2.0.

RESOLUTION

Do not use the DefaultValue property or Visual Basic for Applications to edit read-only controls.

STATUS

This behavior is by design.

MORE INFORMATION

When you create a form based on a query, any control bound to a query field containing an expression is read-only. Because the query expression is not bound to any specific field in the underlying table, you can't edit the values in these fields. However, when you set the DefaultValue property of such a control, Microsoft Access attempts to write a new value to that control every time you add a new record.

Steps to Reproduce Behavior

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier). You may want to back up the Northwind.mdb (or NWIND.MDB) file and perform these steps on a copy of the database.

1. Open the sample database Northwind.mdb (or NWIND.MDB if you are using Microsoft Access version 2.0).

2. Open the Orders Subform form in Design view.

3. Select the Extended Price text box; on the View menu, click Properties and set the DefaultValue property to 0.

4. Close and save the Orders Subform form.

5. Open the Orders form and add a new item to any order.

   Note that you receive one of the error messages described in the "Symptoms" section.

REFERENCES

For more information about calculated field, search the Help Index for "calculated fields, queries" or ask the Microsoft Access 97 Office Assistant.

For more information about calculated controls, search the Help Index for "calculated controls, creating" or ask the Microsoft Access 97 Office Assistant.

For more information about default values for controls, search the Help Index for "DefaultValue property" or ask the Microsoft Access 97 Office Assistant.