"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)

 

[hsitz]

Interesting. I had thought the problem might have been actually having duplicates in the parent table, and instead it's due to having the link on a parent field that merely allows duplicates in its index? I'll try to duplicate that on my test verson. -- Herb

 

[cyberbiker]

That appears at this point to have been the issue.
I still do not know why that would happen though. It does not seem logical.

BTW this is ACCESS 97 and sometimes I get the idea that Bill Gates is a GM automotive engineer. (They seem to like making my life difficult also

I am still working on the errors in my code that I found.
But, at this point I think the non updateable issue is found.

Now all I need to do is figure out why if I call cmdLast_click from the load event I do not go to the last record, but if I click on cmdLast it works perfectly and I move to the last record in the recordset
I added an ORDER By clause to the SQL to order by
"tblRetToVen.RMV" so I could sort by RMV. On my test data vendor Roadrunner has 3 RMV numbers 3,4 and 9.
I want to have the current record as 9 when I load the form.

the applicable code is

Private Sub Form_Load()
DoCmd.Maximize
'assign value to recordsource
Me.RecordSource = strQueryName
'now fire the query
DoCmd.OpenQuery strQueryName

recordcount = DCount("RMV", strQueryName)
If recordcount <> 0 Then
'if any records exist check and see if we got here by vendor or RMV
If boolAllowNew = False Then
'we got here by rmv, no additions also no need to navigate
Call DisableNav
cmdEdit.SetFocus
cmdNew.Enabled = False
cmdNew.Visible = False
Else
'we got here by vendor so add records permitted
cmdNew.Enabled = True
cmdNew.Visible = True
Call cmdLast_Click
End If

Else
' if no records do we add? yes if we got here by vendor no if we got here by RMV
If boolAllowNew = True Then
recordnum = 0
Me.lblRecord.Caption = recordnum & &quot; of &quot; & recordcount

Call cmdNew_Click
Else
cmdEdit.SetFocus
cmdNew.Enabled = False
cmdNew.Visible = False
Call MsgBox(&quot;No records found!&quot;, vbExclamation & vbOKOnly)
End If
End If
End Sub

and......

Private Sub cmdLast_Click()
On Error GoTo Err_cmdlast_Click
recordnum = recordcount
Me.lblRecord.Caption = recordnum & &quot; of &quot; & recordcount
DoCmd.GoToRecord , , acLast
Call calculate
Exit_cmdlast_Click:
Exit Sub

Err_cmdlast_Click:
Call MsgBox(&quot;No records exist for this customer.&quot;, vbOKOnly)
Resume Exit_cmdlast_Click

End Sub

One issue is that the DoCmd object is not available in debug mode so I am having difficutly seeing what is actually happening

Basically, when I step through it either by clicking on cmdLast or by calling cmdLast_click from form load, I reach the line &quot; DoCmd.GoToRecord , , acLast&quot;. where upon I butt heads with the DoCmd object not available at design time issue. Up to then everything seems the same.
I have tried moving to the first record then moving to the last, but it still breaks down and I end up with the first record displayed.

I feel this must be related somehow to the issues I have been having, but again I cannot see it.
My company frowns on me taking code home to work on it or &quot;posting more than a small piece&quot;, so I may not work on this after this afternoon for a few days. I think I need a break anyway.

Again, thank you for your advice and help. You, and the others aimed me in the proper direction re: the nonupateable issue. I would never have found it otherwise.
Terry (cyberbiker)

 

[wild007]

Have you tried looking at the joins? I have had a similar thing where two almost identical queries were different. It all came down to making sure the joins were set up correctly. I don't have time to elaborate, but will check back.

 

[cyberbiker]

Well, I have been looking at the joins and I think that is not the issue now, but any elaboration on what to look for would be appreciated
Terry (cyberbiker)