Others

Fix Non-Updateable Subforms and Calculate Date Differences in Access

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to calculate individual and total time worked and display record counts in an employee database while ensuring the subforms remain editable.

Product: Microsoft Access
Device & OS: not provided
Scenario: Developing an employee database with multiple employment periods that requires duration calculations and record count displays on tab pages.
Observed behavior: Using union and join queries to calculate employment periods results in non-updateable subforms, completely preventing new data entry or edits.

Before you start

Before modifying your database structure, identify exactly which forms are intended for active data entry and which are strictly for displaying summaries or calculations.

Solution 1Recommended

Separate Calculation Queries from Data Entry Forms

Avoid using unions, joins, or aggregation queries as the record source for data entry forms, as these automatically make the recordset non-updateable.

In Microsoft Access, complex query features commonly lock the underlying recordset. To resolve this, you must decouple your data entry interfaces from your calculation queries.

Create single-table data sources

Change the Record Source of your editable subform to bind directly to a single table or a simple single-table query.

Isolate calculation queries

Move any complex logic involving UNION, JOIN, or GROUP BY into separate, dedicated calculation queries rather than embedding them in your form's data source.

Display totals on unbound controls

Use separate, non-editable subforms or domain aggregate functions (such as DCount or DSum) on your main form to display your calculated totals and record counts.

Solution 2

Standardize Date Calculations and Duration Sums

Correctly format date arguments and store durations in base units to ensure mathematically reliable calculations for total time worked.

Solution 3

Manage Referenced Fields in Multi-Table Joins

If you must use a query joining multiple tables in a form, strictly control which fields remain editable to prevent update errors.

Free Microsoft Office alternative

Manage Employee Records Easily with WPS Office

If building complex Microsoft Access databases and dealing with non-updateable query errors is slowing you down, consider managing your employee records with WPS Spreadsheet. It provides a lightweight, flexible interface to calculate dates and track data without the strict constraints of relational databases.

1. Install WPS Office: Download and install the free WPS Office suite on your computer.
2. Open WPS Spreadsheet: Create a new workbook to easily organize your employee records and employment periods.
3. Apply Date Formulas: Use standard spreadsheet formulas to effortlessly calculate total time worked and record counts without worrying about database locks.

Free, lightweight, and incredibly fast Office suite for everyday tasks.Fully compatible with Microsoft Excel formats (.xlsx and .xls).Easily calculate employee durations using built-in date formulas like DATEDIF.Avoid complex non-updateable query errors entirely by using a flexible grid interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Access subform say 'Recordset not updateable'?

Subforms become non-updateable when their Record Source is bound to a query containing UNIONs, complex JOINs, or aggregations (like SUM or COUNT). To fix this, bind the subform to a single table and perform calculations in a separate query or unbound control.

How do I correctly pass arguments to the DateSerial function?

When using the DateSerial function in Access, you must always pass the arguments strictly in the order of year, month, and then day (e.g., DateSerial(2023, 10, 15)).

Can I edit data from multiple joined tables in one form?

Yes, but it is highly recommended that you only allow edits to the primary table. Fields from any referenced or secondary tables should have their properties set to Locked and Disabled to prevent unexpected database errors.

What is the most accurate way to sum employee working times in Access?

Do not attempt to sum durations stored as text strings (like '2 years, 1 month'). Because months and years have variable lengths, you should store and sum durations as total days or Date/Time values, and only format the final total into years and months.

Office Quick Solutions