Others

Fix: Access Query Allows Updates but Not New Records

Natalie Taylor Oct 10, 2026 870 views

Question details

The user needs to add new records using an MS Access query that currently only permits updating existing records.

How to Fix Access Query That Allows Updates but Not New Records

Product: Microsoft Access
Device & OS: not provided
Scenario: Attempting to insert new records into an Access database via a multi-table query or a form bound to that query, which includes calculated fields and an ORDER BY clause.
Observed behavior: Typing into a text field or selecting from a drop-down leaves the field completely blank, and new data fails to be inserted.

Before you start

Open the problematic MS Access database, locate the specific query or form in the Navigation Pane, and test the query directly in Datasheet view to see if the insertion issue happens outside of the form.

Solution 1Recommended

Enable Allow Additions in Form Properties

Use this solution to ensure that the form bound to your query is not artificially restricting data entry through its property settings.

Often, the inability to add new records is not caused by the query itself, but by the properties of the form being used for data entry. Ensuring the form is configured to allow additions can instantly resolve the issue.

Open Form in Design View

Right-click the problematic form in the Navigation Pane on the left side of your Access window and select 'Design View'.

Access the Property Sheet

Press F4 on your keyboard or click 'Property Sheet' in the Design ribbon to open the form properties pane.

Modify the Allow Additions Property

Click the 'Data' tab in the Property Sheet, locate the 'Allow Additions' field, and change its value from 'No' to 'Yes'.

Enable Allow Additions in Form Properties

Testing Your Fix: After saving the form, switch to Form View and attempt to type a new record into the blank row at the bottom.

Solution 2

Simplify and Adjust the Query Structure

Use this solution if testing the query in Datasheet view reveals that the query itself is blocking new record insertions.

Free Microsoft Office alternative

Switch to WPS Office for a Lightweight and Free Alternative

While WPS Office does not feature a direct relational database replacement for MS Access, it provides powerful spreadsheet capabilities with WPS Spreadsheet that can handle extensive data entry, sorting, and querying without complex SQL. It is highly compatible with Microsoft Office formats, offering a seamless and cost-effective productivity suite.

1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
2. Install WPS Office: Run the downloaded installer and follow the simple on-screen instructions to set up the software.
3. Manage Your Data: Launch WPS Spreadsheet to open your XLSX or CSV files exported from databases for easy and flexible data entry.

Free and lightweight suite for Word, Excel, and PowerPoint alternativesHigh compatibility with Microsoft Office formats (DOCX, XLSX, PPTX)Manage data effortlessly with advanced WPS Spreadsheet filters and pivot tablesFamiliar user interface requiring no steep learning curve

Frequently Asked Questions

Why can I update existing records but not add new ones in Access?

This happens when an Access query is only partially updateable. Complex multi-table joins, the absence of required primary keys in the query grid, or calculated fields can allow Access to find and edit existing rows but prevent it from generating valid new rows.

What makes an Access query completely read-only?

A query becomes entirely read-only (un-updateable) if it uses aggregate functions (like SUM or COUNT), contains a GROUP BY, DISTINCT, or UNION clause, or if you lack write permissions to the underlying tables or database file.

Does the ORDER BY clause prevent adding new records?

In certain complex queries or specific join types, an ORDER BY clause can interfere with the way Access manages the recordset cursor, temporarily disabling record additions. Removing the ORDER BY clause often restores the ability to add new records.

Office Quick Solutions