"This recordset is not updateable"

[cyberbiker]

I might be an idiot on this, but I have 3 select queries written in an almost identical manner that will not update.
The message at the bottom of the screen when I run the queries is "this recordset is not updateable".
The question is what did I do to cause this. They are fairly straight forward select queries and I have done a number of these with no issues like this.
In fact, I have an additional 5 select queries in this database that are also very similar and work as intended.
If I copy the SQL from the queries that are not updateable into a new query, the new query is also not updateable.

The underlying tables seem fine. But of course I may be missing something there.

It is pretty obvious that I am overlooking something. My books do not seem to have anything on this particular issue nor did I find what I needed from a search of this site.

The SQL of the offending query is:

SELECT DISTINCTROW RMV_Vend2.VEND_NAME, RMV_Vend2.ADDRESS, RMV_Vend2.CITY, RMV_Vend2.state, RMV_Vend2.ZIP_CODE, RMV_Vend2.ATTN, RMV_Vend2.CONTACT, RMV_Vend2.PHONE_NO, RMV_Vend2.FAX_NO, RMV_Vend2.special, tblRetToVen.RMV, tblRetToVen.Item, tblRetToVen.VendorName, tblRetToVen.VendorRMA, tblRetToVen.Date, tblRetToVen.VendorInvoice, tblRetToVen.Authorized, tblRetToVen.Warranty, tblRetToVen.ReturnVia, tblRetToVen.VendorID, tblRetToVen.Reason_Ret, tblRetToVen.Item_Retrn, tblRetToVen.Quantity_1, tblRetToVen.Part_NO_1, tblRetToVen.Amount_1, tblRetToVen.Item_Ret_2, tblRetToVen.Quantity_2, tblRetToVen.Part_No_2, tblRetToVen.Amount_2, tblRetToVen.Item_Ret_3, tblRetToVen.Quantity_3, tblRetToVen.Part_No_3, tblRetToVen.Amount_3, tblRetToVen.Item_Ret_4, tblRetToVen.Quantity_4, tblRetToVen.Part_No_4, tblRetToVen.Amount_4, tblRetToVen.Item_Ret_5, tblRetToVen.Quantity_5, tblRetToVen.Part_No_5, tblRetToVen.Amount_5, tblRetToVen.Item_Ret_6, tblRetToVen.Quantity_6, tblRetToVen.Part_No_6, tblRetToVen.Amount_6, tblRetToVen.Our_PO_No, tblRetToVen.Option, [RMV_Vend2]![CITY] & " " & [RMV_Vend2]![state] & " " & [RMV_Vend2]![ZIP_CODE] AS Expr1, RMV_Vend2.ext
FROM RMV_Vend2 INNER JOIN tblRetToVen ON RMV_Vend2.VEND_NAME = tblRetToVen.VendorName
WHERE (((tblRetToVen.VendorName)=[Forms]![frmRMVsearch]![cboVenName]));

For comparison, this next one works fine:

SELECT DISTINCTROW Rmv_log1.RMV_NO, Customers.Address, Customers.Address2, Customers.City, Customers.State, Customers.Zip, Rmv_log1.CONTACT, Rmv_log1.ISSUE_DATE, Rmv_log1.RP_OR_WO, Rmv_log1.INVOICE_NO, Rmv_log1.WARRANTY, Rmv_log1.CUST_PO, Rmv_log1.PERCENT_CH, Rmv_log1.SHIP_DATE, Rmv_log1.REASON_RET, Rmv_log1.ITEM_RETRN, Rmv_log1.QUANTITY_1, Rmv_log1.PART_NO_1, Rmv_log1.AMOUNT_1, Rmv_log1.ITEM_RET_2, Rmv_log1.QUANTITY_2, Rmv_log1.PART_NO_2, Rmv_log1.AMOUNT_2, Rmv_log1.ITEM_RET_3, Rmv_log1.QUANTITY_3, Rmv_log1.PART_NO_3, Rmv_log1.AMOUNT_3, Rmv_log1.ITEM_RET_4, Rmv_log1.QUANTITY_4, Rmv_log1.PART_NO_4, Rmv_log1.AMOUNT_4, Rmv_log1.ITEM_RET_5, Rmv_log1.QUANTITY_5, Rmv_log1.PART_NO_5, Rmv_log1.AMOUNT_5, Rmv_log1.ITEM_RET_6, Rmv_log1.QUANTITY_6, Rmv_log1.PART_NO_6, Rmv_log1.AMOUNT_6, Rmv_log1.OUR_PO_NO, Rmv_log1.VEND_INV, Rmv_log1.VEND_RMA, Rmv_log1.RETURN_VIA, [Customers]![City] & " " & [Customers]![State] & " " & [Customers]![Zip] AS Expr1, Customers.Customer, Rmv_log1.Authorize, Rmv_log1.Authorize, Customers.Phone, Customers.Extension, Rmv_log1.CUST_CODE, Rmv_log1.disposition, Rmv_log1.ChCr, Rmv_log1.FREIGHT, Customers.Fax
FROM Customers INNER JOIN Rmv_log1 ON Customers.CustomerID = Rmv_log1.CUST_CODE
WHERE (((Rmv_log1.RMV_NO)=[Forms]![frmRMVsearch]![cboRmv]));
Terry (cyberbiker)

 

[rry2k]

Terry,

I'm not sure if this is what your looking for since the error messages are a little different but I found this in Access help.

I'm getting the message "Operation must use an updatable query."
This error occurs when the current query's Update To row includes a field from either a crosstab query or select query in which an aggregate (total) was calculated for the field (using either the Totals row or a domain function in the Field row). To update a field using the aggregate of another field, calculate the aggregate in the update query itself, not a different query.

hth

 

[cyberbiker]

Thanks hth, but that does not seem to be the issue. These queries are based only on tables not another query. Any other ideas? I am really stuck here. Terry (cyberbiker)

 

[RegionsRob]

There are certain types of select queries that will not let you make changes to (update)the recordset. I don't understand exactly when it happens and end up with trial-and-error fixes to get around it. Anyway, if you search the Access help for Updating Queries, then select the choice for "When can I update data from a query?", you will get their answer. You can also search the MS Knowledge Base.

 

[hsitz]

I believe the problem is that you can get an updateable query when using DISTINCTROW when joining tables in a one-to-many relationship if the only fields returned by the query are from the table on the 'one' side of the relationship.

But if you include fields from the 'many' side the query will not be updateable.

I'm not sure whether this explains why your first query is not updateable and why your second one is. Does it? -- Herb

 

[cyberbiker]

Thanks Herb and Rob
I have looked at both of your responses and studied the help but still am not getting this.

In queries that do work (see above) the table "customers" is the one side and the table RMV_log1 is the many side.
When I check the relationship the default value of select only those fields where they are = is checked.
To further clarify (or obscure the issue maybe). I have a table for a vendor with information on that vendor.
This table is called RMV_Vend2. Each record will be unique. I hopefully have that as the 1 side in my join if I am correct in my understanding
No vendor will have the same name as another. (I know, a unique ID number would be safer to use, but that is a different issue and one we will not solve here).
The table tblRetToVen is the many side. There may be 0 to an unlimited number of recfords.
My query is to search for those records and to add new ones if required.
The other query has the table "customer" as the 1 side.
Each customer record is unique and has a unique customer id number. The table RMV_log1 is the returns from that particular customer and may have from 0 to an unlimited number of returns. This works fine.
These are simple select queries where I have 2 tables joined. At this point in this project, I have a total of 22 queries. Many of which have 2 tables joined. 3 do not permit me to add new records.
I cannot find the differences between them (except for field names and search criteria of course)
The 3 queries that do not permit me to update the recordset are the only ones that are associated with the tables RMV_vend2 and tblRetToVen.
They are very close to identical except for search citeria.
I designed the first query and copied it then modified the copy.
I am suspecting that the issue may be the tables, but I cannot find anything there.
I appreciate the answers anyway though and hope some one can figure out what exactly I need to do to find this.

Terry (cyberbiker)

 

[hsitz]

Terry -- You say, "When I check the relationship the default value of select only those fields where they are = is checked." This has nothing to do with whether a link is one to one or one to many.

I'm wondering whether the queries that are giving updateable recordsets are only doing so because even though the relationships are theoretically one-to-many, in the many table there just happens to never be more than one child record. If so, then you should be able to "break" those queries by adding records to make an actual one-to-many relationship.

Other than that, I don't have to many ideas. Don't know if it will add anything to what you've already read in the docs, but you might check out this link:

http://richardbowles.tripod.com/access/lesson3.htm

-- Herb

 

[hsitz]

Actually, what I just said about the udateable queries being to a parent table that does not actually have more than one record linked a child was wrong.

I think the main thing is whether __the criteria in the query__ are returning more than one record for any record in the parent table. If at most one record is meeting the criteria, then the recordset is updateable. If more than one record in the child table meets the criteria, then you get an unupdateable recordset. That's what I'm guessing at this point, anyway. -- Herb

 

[cyberbiker]

Thanks Herb, your last reply made sense. I am still working on it and will check that link.
My test data has only one record in the parent table that meets the criteria I am using.
ie: vendor M.Mouse exists only once in that table RMV_vend2 (as Ven_Name), but may have from 0 to whatever number of records in tblRetToVen with the vendor being M.Mouse (as VendorName).
I have done this type of query many times and never had this happen. I know that this time I have made some goofy error that I just cannot see. Terry (cyberbiker)

 

[hsitz]

Is there some reason in particular that you're using the DISTINCTROW setting? (Which is what you get when you select yes for "unique records" in the properties form of the query). If you don't know why it's set, then try the queries after you set that setting to 'NO'.

 

[cyberbiker]

Well, tried that and have been reading the link you suggested. I think I will need to sleep on this a bit more. But keep the ideas coming. I am certain this is one of those things that drive me nuts until I find it and then I wonder how we missed it Terry (cyberbiker)

 

[shiggyshag]

Hi

Check that the tables Your updating are the same Ie text fields are text and Interger are Intergers

Cheers

 

[hsitz]

I think all my remarks about 'DISTINCTROW' are irrelevant. The DISTINCTROW (or "unique records&quot designation does absolutely nothing if the query is selecting records from all of the tables that are joined in it. It applies only when some the fields returned by the query are limited to a subset of them.

Another stab: Sounds like you've already ensured this, but in the Unupdateable queries are you sure that the parent table has a unique key that's being used to link to the child table? If there are two records in parent table with same key (linking to same records in child table), then it would not be updateable.

 

[cyberbiker]

Thanks again. But the text fields are text. The fields are totally unique in each table except for vendor name and there is only one of each of those in each table. Both are indexed and both are text with the field size set to 30.
While writing this I thought of something and changed the index of the vendor table (RMV_Vend2) to "Yes(no duplicates)" instead of "Yes(duplicates allowed)".
I can now enter a new record that way for a vendor that has no records in the return table (tblRetToVen).
ie: vendor MMouse has no returns so I can enter a new one.

but if a record exists, I receive an error message that says "records in table "tblRetToVen" would have no record on the "one" side."

ie: roadrunner exits in the RMV_vend2 and has one record in
tblRetToVen. The form loads and finds that record.
cmdNew_click fires either by clicking on it or by using the navigation buttons and answering yes to the message box I display.
I then can enter data in the fields.
However I do not seem to be creating a new record. The changes appear in the origional record.
Obviously I am very confused.
Your help is greatly appreciated. All the answers have lead me to this point. I will get it worked out, but any ideas will still be greatly appreciated.
Terry (cyberbiker)

 

[Craig0201]

Simple.....

The recordset isn't updateable because it contains fields from 2 tables. The one that works contains only fields from 1 and is using the second table for criteria only.

Craig

 

[cyberbiker]

Craig,
Could you elaborate a bit on what you said.
I may be being dense (not unusual for me sometimes when I become frustrated) Terry (cyberbiker)

 

[Craig0201]

In your SQL, in the first you get fields from tblRetToVen and from RMV_Log1. In the second, you only get fields from
RMV_Log1. That's the difference. If you take away [RMV_Vend2]![CITY] & " " & [RMV_Vend2]![state] & " " & [RMV_Vend2]![ZIP_CODE] AS Expr1, RMV_Vend2.ext from the first SQL, the first SQL would then be updateable.

Craig

 

[cyberbiker]

not quite Craig. In the first query (the one that is goofy) I take fields from tables RMV_Vend2 and tblRteToVen.
In the second query, I take fields from tables Customers and RMV_log1.
I still do not see the differences Terry (cyberbiker)

 

[hsitz]

Terry -- You've got me curious about what the difference can possibly be. If you want, you could email me the .mdb or a stripped down version of it, and I'll take an actual look at it along with you. -- Herb, hsitz@nwlink.com

 

[cyberbiker]

Thanks Herb. I am working on things now that seem to be a bit "goofy" in my code.
The non updateable issue seems to have been the useage of indexed property = "yes(duplicatesOK)" instead of "yes(noduplicates)"
Once I changed that I seem to be creating new records but am putting the RMV number from the previous record on to the form. Since that number is required = yes and indexed = "yes" (no duplicates). I think I am getting goofy results which are confusing the issue.
It will take me a while to make all the changes I need to make to the form, but I will post again as soon as I can (probably this pm or tomorrow am) and let everybody know how this came out.
Everybody has been a big help so far. Your advice has not only lead me to find these errors but have increased my very limited knowlege of ACCESS quite a bit.
Thanks for all Terry (cyberbiker)