The Recordset is not updateable

[AntonRSL2016]

Hello,

I am a relatively inexperienced Access user running Access 2013, I have done some baasic training and been shown how to avoid some of the common pitfalls of poor database design. I have created a database from scratch containing a range of tables with relationships.
I have now started creating forms to input data. One form has fields that I am populating automatically, via queries, based on the input chosen from another field. This works nicely. However, other fields on the form which are intended to permit users to type in additional findings, are now 'not updateable'. Having read various threads discussing similar problems I am thinking that I simply have to live without the query led automation?
Any advice would be much appreciated.

Anton

[jforbes]

My understanding is that you have a Form that has it's RecordSource set to a Query and the Fields on your Form cannot be edited.

First open the Query itself and see if you can edit the data in the Query view.

If you can, then make sure

• The Form is opened in a Edit Mode.
• The Form's AllowAdditions and AllowEdits properties are st to True.
• The Form's RecordsetType is Dynaset.
• the Field is Enabled.
• the Field is not Locked.

If you can't edit your Query, then check out this link: Why is my query read-only?

[AntonRSL2016]

Thank you very much indeed for your advice. I have followed your instructions line by line and I can report the following:
I can edit the queried data if I open the query seperately.
The form is open in 'form view' as opposed to layout or design.
AllowAdditons and AllowEdits are both set to Yes (True).
RecordsetType is Dynaset (I also tried Dynaset (Inconsistent Updates to no avail).
The field is Enabled and not Locked.

No joy I'm afraid.

I may not have been entirely clear in my description of the form, it is based on one table and two queries, it is the fields directly from the table that I want to edit but cannot. Is that significant? Thank you for your help.
I have taken a step back and re-produced the form without the queried fields, this just means none of my fields auto-populate upon selection of a particular value in a child field.

[jforbes]

I may not have been entirely clear in my description of the form, it is based on one table and two queries, it is the fields directly from the table that I want to edit but cannot. Is that significant?

I would guess the linking to the Two Queries is what is messing things up. I've had situations in the past where all I want is to include a couple calculated fields on a Continuous Form where the calculated fields were already created in a Query. I would create a Query based on the Table the Form was currently using, add the Query with the calculations and link it to the Table by the ID, and set the Form's RecordSource to the new Query. Most times it will work, sometimes it would act up like you are experiencing, other times it would act up only after converting to an .MDE.

Sometimes when attempting to get these to work, cleaning up the Query with the Calculations would do it. Other times, instead of using SELECT * in the Newly created table, discretely selecting the Table and Query fields would work, probably by eliminating the field that was upsetting Access. In the case of the .MDE, I had to base the Form on the Table and then use DLookups() on the Query with Calculations to get the values and still have an editable Form.

So, for you:

• You can try to change the SELECT * of you Form to pick just the fields you need.
• If you want to post the contents of all your Queries, we can see if we can spot what might be the problem. There might be something obvious, but I wouldn't get my hopes up.
• You can base your Form on the Table and replace the all of your Calculated fields with DLookup()s.
• You can attach a stripped down version of your Database and we might be able to root around in it and find the cause.

[AntonRSL2016]

I got carried away with your suggestions of the DLookUp route. I tried to build expressions into one of the queries, but my syntax was incorrect and they didn't work, so I decided I would step back the query to its original format and post a copy of this database for your perusal.....I opened the form just to check it out and now the fields are updateable! However the queries no longer drive the child fields. This confirms the queries are the issue.

[jforbes]

I created a Form called Frm_NCR_Main_No_Queries based on your Form. I removed the Queries altogether from the Form. I based the Form on Tbl_NCR_Main. Then added DLookups for the Serial and Root Cause Fields. It seems to be working the way it should.

You'll notice the slightest delay as Access populates the DLookups. This is normal for Access.

Lastly the column/Field Named "Sub-Set_Description" might give you some trouble as the stroke/minus character is an operator in SQL. You'll want to make sure you surround it with [] Brackets when referring to it, or you'll get strange things happening. Or rename it. I didn't really check into it, but I think this is the field that was causing problems as Access, in the right situation, could think it was a calculated field. There is also a field name with a ? Question Mark, I would rename that one right away as it will give you a hard time.

[AntonRSL2016]

Good morning, thank you very much for your time and expert advice. I have followed your advice and removed the - and ? from all the various occurrences. Unfortunately the DLookUp statements taking the form:
=DLookUp("[*]","Qry_*","*=" &[*])
return a circular reference error. The only one with the format:
=DLookUp("[Sub_Set_Description]","Qry_Serial_Numbers","Serial_ID=" & 1)
does not show the circular reference error. However, I had data in the underlying tables which all display the same result for the Sub_Set_Description so I altered a record, this showed that actually this DLookUp is not bringing in the right value!

I have done some playing with the DLookUp syntax, as well as various Googles to try to understand the circular reference problem but I have had no joy. Why might it show up the error when I open the DB but not with yourself?
In the hope that I have not worn out your patience I have re-attached the DB with the corrections.

[jforbes]

If you ever want to test the Syntax of a DLookup, you can type something like the following into the VBA Immediate Window and it will attempt to run the DLookup and give you a better error (if there is one) than running it in a ControlSource.

?DLookUp("[Sub_Set_Description]","Qry_Serial_Numbers","Serial_ID=" & 1)

was created for testing out the DLookup. It's great for testing, but make sure you replace the 1 with the actual field name, unlike what I did. So on the Form the 1 should have been replaced with [Serial_ID]



I honestly thought I took care of the Circular References. They are a pretty common problem when doing something like this. They can also show up when creating Reports.

When Access tries to display a Control, it will go through a somewhat complex evaluation and it will perform whatever substitutions it can to finish up the evaluation of the Control. It will replace references to other Forms with their Values, it will execute VBA functions, and it will use Fields in the RecordSource. If it can find a stable evaluation, everything is good, if it can't it will display that pretty much useless "#Name?" error. If during the Evaluation, it sees something like Serial_ID and it notices a Field named Serial_ID and a Control Named Serial_ID, it balks and gives the #Name? error.

Even better, Access will let you name a Control on a Form or Report the same name as a Field in the Form/Report's RecordSource. In fact, it does it by default when the Wizard is used or the a Field is dragged onto the Design Surface.

It's unfortunate, but it's easily fixed by keeping it in mind, and if you refer to a Field in Form/Report's RecordSource, rename any Control that Displays that Field. Typically, I do this by appending "txt" in front of TextBoxes, "cbo" in front of ComboBoxes and so on. So for the TextBox that displays Serial_ID, it was named "Serial_ID", renaming it to "txtSerial_ID" gets rid of the Circular Reference.

What I'm surprised about most is that I didn't get the error and you did.

I've made another attempt with the database and hopefully I caught all the Circular References and Typos. =)

[AntonRSL2016]

Thank you very much for all your help. I replicated the name changes in the active database and all the circular references are now resolved. I have tweaked the DLookUp slightly, to the form:
=DLookUp("[Sub_Set_Description]","Qry_Serial_Numbers","Serial_ID=" & Forms![Frm_NCR_Main_Qry_Fed]!Serial_ID)
which now is definitely allowing the child fields to be driven by the selection made in the ID field.

Your help has been absolutely invaluable, thank you very much.

Anton

[zmbd]

my one suggestion here is to build your criteria string first

=DLookUp("[Sub_Set_Description]","Qry_Serial_Numbers","Serial_ID=" & Forms![Frm_NCR_Main_Qry_Fed]!Serial_ID)

becomes

zSQL = "Serial_ID=" & Forms![Frm_NCR_Main_Qry_Fed]!Serial_ID
=DLookUp("[Sub_Set_Description]","Qry_Serial_Numbers",zSQL)

now one can either insert a debug.print zsql just before the DLOOKUP() or a STOP either check the immediate pane ( <ctrl><g> ) or in the immediate pane, when the code hits the break, type ?zSQL to see what the resolved string value is to check for syntax issues.