UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cannot Update A Screen's Fields Due To “the Record Set Is Not Updateable” Message, Access 2010    
 
   
Doug Galayda
post Jul 10 2019, 05:38 PM
Post#1



Posts: 318
Joined: 19-May 10
From: Springfield, Illinois


Wed/7/10/2019
Title: Cannot update a screen's fields due to “The record set is not updateable” message

The screen is based on a query, not a table.
I migrated all the elements from a 2003 .mdb database into a new .accdb database.
In one of the forms, there is a screen that is based on 3 table query.

Two of the tables are in a SQL database, and the key table is a linked table that is in a Access network BE.(back end).accdb.
Both the old .mdb and the new accdb screen are in the same directory.

1) I can update this screen with no problem in the old .mdb screen, but I cannot update it in the new,
for example when I try to update a comment field, I get “The record set is not updateable” at the bottom left of the screen.

2) I ran the query, and I opened up the memComments column, and tried to update that, but could not enter anything.

Any suggestions and or ideas are welcome.
Thank you !
This post has been edited by Doug Galayda: Jul 10 2019, 06:22 PM
Go to the top of the page
 
DanielPineault
post Jul 10 2019, 07:43 PM
Post#2


UtterAccess VIP
Posts: 6,764
Joined: 30-June 11



You could try setting the Recordset Type to Dynaset (Inconsistent Updates).
You've ensured that the BE is not in a read-only state, ie: you can enter data into individual tables?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Doug Steele
post Jul 10 2019, 07:57 PM
Post#3


UtterAccess VIP
Posts: 22,182
Joined: 8-January 07
From: St. Catharines, ON (Canada)


You might also check Allen Browne's Why is my query read-only?

--------------------
Doug Steele, Microsoft Access MVP (2000-2018)
Personal webpage
Microsoft profile
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page
 
Doug Galayda
post Jul 11 2019, 05:55 AM
Post#4



Posts: 318
Joined: 19-May 10
From: Springfield, Illinois


Good Morning Daniel,

1)I did see "Dynaset" under the record source in the property sheet

2)Could you please tell me How to check to see if the new backend I created is in a read only state or not?
Where do I go to look?
I need to know a method to change it Out of the read only state.
I will google it too after I get into work.

3) and I will check if can enter data into individual tables

Thanks! appreciate your help.
This post has been edited by Doug Galayda: Jul 11 2019, 06:05 AM
Go to the top of the page
 
Doug Galayda
post Jul 11 2019, 05:58 AM
Post#5



Posts: 318
Joined: 19-May 10
From: Springfield, Illinois


Good Morning Doug,

I did check about 50% of the (Why is my query read-only? LIST) yesterday after we found that on google,
I will check the rest when I get into work.

Thanks!
This post has been edited by Doug Galayda: Jul 11 2019, 06:02 AM
Go to the top of the page
 
dale.fye
post Jul 11 2019, 09:42 AM
Post#6



Posts: 129
Joined: 28-March 18
From: Virginia


Doug,

One of the other things I would check is to make sure that the query contains the PK field from each of the tables involved in the JOIN operation. This field does not have to be displayed on a form, but if you have a one to many relationship, it might be the difference between it being updateable or not.

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
Doug Galayda
post Jul 11 2019, 02:41 PM
Post#7



Posts: 318
Joined: 19-May 10
From: Springfield, Illinois


Dale,
That is a very good idea,
I will make sure I check that!
Thank you!
This post has been edited by Doug Galayda: Jul 11 2019, 02:51 PM
Go to the top of the page
 
Doug Galayda
post Jul 11 2019, 03:12 PM
Post#8



Posts: 318
Joined: 19-May 10
From: Springfield, Illinois


Dale,
The query does not hold true that rule and I am trying to think of how to remedy the situation
The query worked fine in the old .mdb but it does not work the same in the .accdb
It supplies source the form(screen) that is now non-updatable and acts as if in a read-only state.
Thanks.
This post has been edited by Doug Galayda: Jul 11 2019, 03:16 PM
Attached File(s)
Attached File  VSURLogman_query_.JPG ( 97.36K )Number of downloads: 3
 
Go to the top of the page
 
DanielPineault
post Jul 11 2019, 03:55 PM
Post#9


UtterAccess VIP
Posts: 6,764
Joined: 30-June 11



Not Dynaset but Dynaset (Inconsistent Updates)

The read-only status is simple to validate. You need only make an entry directly in a table, if you can all is good, otherwise you have some permission issue that you need to resolve. I don't think this is you issue, but it takes 2 seconds to validate.

Typically, a form is linked to a single table and you used subforms to display related data. This would get around the entire issue.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Doug Galayda
post Jul 11 2019, 05:47 PM
Post#10



Posts: 318
Joined: 19-May 10
From: Springfield, Illinois


You solved the Problem!!

I tried the updating the old query generated data, and COULD update a column on it, --but then then could not update the NEW query generated data.,,,,,

But then when I read your post I realized dynaset (inconsistent types) was an available drop down choice in the forms property sheet.
So after I changed the [Data> RecordSet Type > from Dynaset to Dynaset (Inconsistent Updates)]-- I could update the fields in the form, Yay!
(upper fields are set to update not allowed)

Thank You Daniel!

One Question: I could see the update in the NEW query, after I entered it into the screen, but I could not change it, I could only change the column values in the generated query results via the screen.
~~But the screen is now working as it should, the fields that should be updatable are updatable!
That's all that counts~~that is what the user needs.

You could try setting the Recordset Type to Dynaset (Inconsistent Updates).---Why did I not read this as Change the setting to Dynaset (Inconsistent Updates)?--> I don't know, that's my fault!
You've ensured that the BE is not in a read-only state, ie: you can enter data into individual tables?--I could update the tables, but could not update the results of the query, and still cannot.
but we can update the Tables via the screen, that is the Unexpected

Thanks Again! You have been Very helpful!

May God bless you and UtterAccess team member
This post has been edited by Doug Galayda: Jul 11 2019, 06:44 PM
Attached File(s)
Attached File  Can_Now_Update_531_pm_7_11_2019.JPG ( 109.78K )Number of downloads: 3
Attached File  Chicago_Test.JPG ( 19.89K )Number of downloads: 1
Attached File  Compare_old_and_new_VSRULogmanQuery.JPG ( 39.24K )Number of downloads: 1
Attached File  INCONSISTANT_Types.JPG ( 33.57K )Number of downloads: 0
 
Go to the top of the page
 
Doug Galayda
post Jul 12 2019, 09:46 AM
Post#11



Posts: 318
Joined: 19-May 10
From: Springfield, Illinois


Doug, Daniel solved it,

The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties.
The solution (for me)was to change it from Dynaset to Dynaset (Inconsistent Updates)
I could not change the queries output, but I could update a alpha column all my tables and
then I could update the tables with the screen.~~and this made the screen fields updatable.

Thanks for your check list!
This post has been edited by Doug Galayda: Jul 12 2019, 09:47 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th August 2019 - 07:43 PM