"This Recordset is not updateable"

[jayC]

I cannot make data changes to a query or its attached form. When I attempt
to change a field there is a sound and a message appears in the lower left,
"This Recordset is not updateable."
How did this come about and what do I need to do to be able to update data
again?

 

[Steve Schapel]

Jay,

There are a number of factors that determine whether a query's data set
is updateable. For example, the nature of the relationships between the
tables in the query, the presence of aggregate functions in the query,
etc. If you would care to post the SQL view of the query here, someone
will be able to point to what is happening in this instance.

 

[jayC]

SELECT [W208-02JAN].Field2, [W208-02JAN].Field1, [W208-02JAN].InDate,
[W208-02JAN].[CUSTOMER NAME], [W208-02JAN].ADDRESS, [W208-02JAN].CITY,
[W208-02JAN].ATTENTION, [W208-02JAN].Code, [W208-02JAN].[ACCT NO],
[W208-02JAN].[Cust Prop No], [W208-02JAN].[JOB LOCATION],
[W208-02JAN].PRICE, [W208-02JAN].[CUST NO],
IIf(IsNull([1tot]),[PRICE],IIf(IsNull([2tot]),[PRICE]+[1tot],IIf(IsNull([3tot]),[PRICE]+[1tot]+[2tot],IIf(IsNull([4tot]),[PRICE]+[1tot]+[2tot]+[3tot],IIf(IsNull([5tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot],IIf(IsNull([6tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot],IIf(IsNull([7tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot]+[6tot],[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot]+[6tot]+[7tot])))))))
AS TOT, [W208-02JAN].QTY1, [W208-02JAN].DES1, [W208-02JAN].EA1,
[W208-02JAN].x1, IIf(IsNull([x1]),[QTY1]*[EA1],[x1]) AS 1tot,
[W208-02JAN].QTY2, [W208-02JAN].DES2, [W208-02JAN].EA2, [W208-02JAN].x2,
IIf(IsNull([x2]),[QTY2]*[EA2],[x2]) AS 2tot, [W208-02JAN].QTY3,
[W208-02JAN].DES3, [W208-02JAN].EA3, [W208-02JAN].x3,
IIf(IsNull([x3]),[QTY3]*[EA3],[x3]) AS 3tot, [W208-02JAN].QTY4,
[W208-02JAN].DES4, [W208-02JAN].EA4, [W208-02JAN].x4,
IIf(IsNull([x4]),[QTY4]*[EA4],[x4]) AS 4tot, [W208-02JAN].QTY5,
[W208-02JAN].DES5, [W208-02JAN].EA5, [W208-02JAN].x5,
IIf(IsNull([x5]),[QTY5]*[EA5],[x5]) AS 5tot, [W208-02JAN].QTY6,
[W208-02JAN].DES6, [W208-02JAN].EA6, [W208-02JAN].x6,
IIf(IsNull([x6]),[QTY6]*[EA6],[x6]) AS 6tot, [W208-02JAN].QTY7,
[W208-02JAN].DES7, [W208-02JAN].EA7, [W208-02JAN].x7,
IIf(IsNull([x7]),[QTY7]*[EA7],[x7]) AS 7tot, [W208-02JAN].QTY8,
[W208-02JAN].DES8, [W208-02JAN].EA8, [W208-02JAN].QTY9, [W208-02JAN].DES9,
[W208-02JAN].EA9, [W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10,
[W208-02JAN].TOT10 AS Expr1, [W208-02JAN].QTY11, [W208-02JAN].DES11,
[W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12,
[W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12,
[W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13,
[W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14,
[W208-02JAN].EA14, [W208-02JAN].QTY15, [W208-02JAN].DES15, [W208-02JAN].EA15
FROM [W208-02JAN]
GROUP BY [W208-02JAN].Field2, [W208-02JAN].Field1, [W208-02JAN].InDate,
[W208-02JAN].[CUSTOMER NAME], [W208-02JAN].ADDRESS, [W208-02JAN].CITY,
[W208-02JAN].ATTENTION, [W208-02JAN].Code, [W208-02JAN].[ACCT NO],
[W208-02JAN].[Cust Prop No], [W208-02JAN].[JOB LOCATION],
[W208-02JAN].PRICE, [W208-02JAN].[CUST NO],
IIf(IsNull([1tot]),[PRICE],IIf(IsNull([2tot]),[PRICE]+[1tot],IIf(IsNull([3tot]),[PRICE]+[1tot]+[2tot],IIf(IsNull([4tot]),[PRICE]+[1tot]+[2tot]+[3tot],IIf(IsNull([5tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot],IIf(IsNull([6tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot],IIf(IsNull([7tot]),[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot]+[6tot],[PRICE]+[1tot]+[2tot]+[3tot]+[4tot]+[5tot]+[6tot]+[7tot]))))))),
[W208-02JAN].QTY1, [W208-02JAN].DES1, [W208-02JAN].EA1, [W208-02JAN].x1,
IIf(IsNull([x1]),[QTY1]*[EA1],[x1]), [W208-02JAN].QTY2, [W208-02JAN].DES2,
[W208-02JAN].EA2, [W208-02JAN].x2, IIf(IsNull([x2]),[QTY2]*[EA2],[x2]),
[W208-02JAN].QTY3, [W208-02JAN].DES3, [W208-02JAN].EA3, [W208-02JAN].x3,
IIf(IsNull([x3]),[QTY3]*[EA3],[x3]), [W208-02JAN].QTY4, [W208-02JAN].DES4,
[W208-02JAN].EA4, [W208-02JAN].x4, IIf(IsNull([x4]),[QTY4]*[EA4],[x4]),
[W208-02JAN].QTY5, [W208-02JAN].DES5, [W208-02JAN].EA5, [W208-02JAN].x5,
IIf(IsNull([x5]),[QTY5]*[EA5],[x5]), [W208-02JAN].QTY6, [W208-02JAN].DES6,
[W208-02JAN].EA6, [W208-02JAN].x6, IIf(IsNull([x6]),[QTY6]*[EA6],[x6]),
[W208-02JAN].QTY7, [W208-02JAN].DES7, [W208-02JAN].EA7, [W208-02JAN].x7,
IIf(IsNull([x7]),[QTY7]*[EA7],[x7]), [W208-02JAN].QTY8, [W208-02JAN].DES8,
[W208-02JAN].EA8, [W208-02JAN].QTY9, [W208-02JAN].DES9, [W208-02JAN].EA9,
[W208-02JAN].QTY10, [W208-02JAN].DES10, [W208-02JAN].EA10,
[W208-02JAN].TOT10, [W208-02JAN].QTY11, [W208-02JAN].DES11,
[W208-02JAN].EA11, [W208-02JAN].TOT11, [W208-02JAN].QTY12,
[W208-02JAN].DES12, [W208-02JAN].EA12, [W208-02JAN].TOT12,
[W208-02JAN].QTY13, [W208-02JAN].DES13, [W208-02JAN].EA13,
[W208-02JAN].TOT13, [W208-02JAN].QTY14, [W208-02JAN].DES14,
[W208-02JAN].EA14, [W208-02JAN].QTY15, [W208-02JAN].DES15, [W208-02JAN].EA15
HAVING ((([W208-02JAN].InDate)="208-03"))
ORDER BY [W208-02JAN].Code, [W208-02JAN].[ACCT NO], [W208-02JAN].[Cust Prop
No];

 

[Tom Wickerath]

Hi Jay,

All I can say after seeing that is Oh.....My.....God!
One reason this query is not updateable is that it includes a Group By clause:

FROM [W208-02JAN]
GROUP BY ............

There may be other reasons as well. Take a look at Access MVP Allen Browne's
list, available here:

Why is my query read-only?
http://allenbrowne.com/ser-61.html

I would start investigating methods of simplifying this query. It looks to
me like a maintanance nightmare. The fact that you require all of the IIF
functions strongly suggest that your data may not be properly normalized.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

<snipped hugemongous SQL statement>

 

[Tony Toews [MVP]]

SELECT [W208-02JAN]

What kind of table is that?

SELECT [W208-02JAN].Field2, [W208-02JAN].Field1,

Field2 and Field1 are not very descriptive names.

[W208-02JAN].QTY1, [W208-02JAN].DES1, [W208-02JAN].EA1,

Why do you have repeating field names? QTY1, QTY2, DES1, DES2 and so
forth? This is usually an indicator of a badly designed table. This
data should be normalized.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Monica Simo]

I have the same "recordset is not updateable" problem with my query. I think it's a result of the way I've joined the 2 tables and the "Balance" query together... any suggestions on how to resolve this problem?

SQL:
SELECT Payments.CustomerID, Payments.PaymentID, Payments.Date, Payments.Amount, Customers.Name, Balance.CustomerBalance
FROM (Customers INNER JOIN Balance ON Customers.CustomerID = Balance.CustomerID) INNER JOIN Payments ON Customers.CustomerID = Payments.CustomerID;

 

[Allen Browne]

See:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

If I had to guess, it could be this one:
The fields in a JOIN are not indexed correctly:
there is no primary key or unique index on the JOINed fields.