Serendipituos Code

Problem

I have a Microsoft Access database application. When updating a record from the screen, users kept getting the error:

This recordset is not updateable

The form datasource is a query with two tables

Solution

This query was not updatable, here is a list of reasons why a query would not be updatable By Allen Browne:

• It has a GROUP BY clause. A Totals query is always read-only.
• It has a TRANSFORM clause. A Crosstab query is always read-only.
• It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.
• It contains a DISTINCT predicate. Set Unique Values to No in the query’s Properties.
• It involves a UNION. Union queries are always read-only.
• It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.
• It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.
• The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.
• The query’s Recordset Type property is Snapshot. Set Recordset Type to “Dynaset” in the query’s Properties.
• The query is based on another query that is read-only (stacked query.)
• Your permissions are read-only (Access security.)
• The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)
• The query calls a VBA function, but the database is not in a trusted location so the code cannot run. (See the yellow box at the top of this Access 2007 page.)
• The fields that the query outputs are Calcluated fields (Access 2010.)

source: http://allenbrowne.com/ser-61.html

The query used two tables:

DMR and OutSideInpect

SELECT DMR.DMRid, DMR.logid, DMR.dmrstatus, DMR.datecompleted, DMR.pnrev,

outsideinspect.dateinspect, outsideinspect.qtyrej

FROM DMR INNER JOIN outsideinspect ON DMR.logid = outsideinspect.logid;

In this case the joining field had two issues:

• OpenSideInspect did not have a unique key
• There was no foreign key relationship between the tables.

Added a primary key to OpenSideInspect:

Added a relationship between the two tables:

With this two changes, the recordset became updatable.