A field in my app is unexpectedly read only

Important    Access Services 2010 and Access Services 2013 are being removed from the next release of SharePoint. We recommend you do not create new web apps, and migrate your existing apps to an alternative platform, such as Microsoft Power Apps.

When you use a query to supply data for a view in an Access web app, some fields that you might expect to be able to edit are read-only in the view. Specifically, if you use more than one table and there is at least one join, there are restrictions on which query fields can be updated.

Note:  This topic is about Access web apps. It doesn't apply to desktop databases. If you are having trouble with a query in a desktop database, see the article Introduction to queries for an overview.

Requirements for updateable query fields

• Only fields from the table where the joined field doesn't have unique values – the "most-many" table – can be updated.

• The query output must include the primary key fields for the most-many table.

• The most-many table must be on the inner side of any outer joins (i.e., the left side of a left outer join or the right side of a right outer join).

In this query, the Tasks table is the most-many table because it looks up data in the Projects table.

However, because the primary key for Tasks is not included in the output, none of its fields will be updateable.

Even if the key is added, none of the fields that come from the Projects table is updateable because it isn't the most-many table.

Top of Page

Find and fix an updateable field problem

The reason that your field isn't updateable determines how you can enable updates. To find out that reason, open the query in Design View and examine the query design details.

1. If you're starting from a browser, open the web app in Access: at the top right corner of the app, click Settings > Customize In Access.

2. In Access, if the Navigation Pane isn't open, press F11 to display it. Then, in the Navigation Pane, right-click the query and then click Design View.

   

3. Determine which table in the query is the most-many table. A good rule of thumb is that the most-many table is the table that looks up data in another table.

4. Check that the field you want to update is in the most-many table. If it isn't, there's nothing you can do to the query to work around the problem. As an alternative way of letting people edit the field, consider creating a new, updateable view based only on the table, and then making it easy to open that view as needed. For example, you might provide a button with a macro that opens the view.

5. Check that the primary key for the most-many table is included in the query output. If it isn't there, add the primary key to the query output.

6. Double-click each join and verify that:

   • The join is an inner join; or,

   • If it is an outer join, the most-many table is on the inner side of the join – on the left side of a left join or the right side of a right join.

If a join doesn't meet at least one of the preceding two criteria, change the join to make the query updateable.

Top of Page

Add the most-many primary key to the query output

1. Open the query in Design View.

2. Identify the most-many table.

3. Double-click each field in the primary key. Each key field is marked with a little key.

4. In the query design grid, make sure that the Show box is checked for the key fields you just added:

   

Top of Page

Change a join to make a query updateable

If any join doesn't meet one of the above criteria, it will prevent updates. Here's an example of a non-updateable join:

This is a left outer join – it includes everything from the left table and corresponding records from the right table. However, the most-many table is on the right side of the join – thereby preventing updates.

Ways to make an outer join updateable

There are three basic ways to adjust a join so that its query is updateable. Double-click the join to open the Join Properties dialog box, and then do one of the following:

• Change the outer join to an inner join    Click option 1 in the Join Properties dialog box.

• Switch the tables' positions    Move the most-many table to the inner side of the join and the other table to the outer side (use the combo boxes in the Join Properties dialog box to set the Left and Right table names).

• Change the join's direction    Click option 2 or 3 in the Join Properties dialog box (change it to whichever value it wasn't).

Top of Page