Operation must use an updateable query?

[JB37]

Hello,

I was hoping someone on this forum might be able to help me.

I am trying to update a table with info in another table, using a nested query.

strSQL = "UPDATE tblTemp SET AdminNum = '" & cboAdmin.Text & "', " & _
"Size = (SELECT Size FROM tblEquipList WHERE AdminNum = '" & cboAdmin.Text & "')"

DoCmd.RunSQL strSQL

cboAdmin is a combo box on my form that is populated by tblEquipList.AdminNum.

When run this gives me:
Runtime Error '3073':
Operation must use an updateable query.

The SELECT statement works by itself, and the first part of the UPDATE (without the nested select) works by itself, but not when I combine them. I also need to update other things in the same manner in which Size is being updated, but when I can get this to work it shouldn't be a problem just added the updates for the other fields in there.

Thanks in advance,
Jeremiah


Edit:
Also, I figured I should add that when I remove the SELECT statment and put in a number (IE: Size = '400'), the query runs just fine. So I know the problem lies in the nesting, but am unsure how.

 

[neileg]

Have you tried doing this a stored query instead of in code? Stored queries are compiled, where as queries built in code are done on the fly.

 

[JB37]

Yes, I tried putting this in a stored query and running it:

UPDATE tblTemp SET tblTemp.AdminNum = 'CAT-A', tblTemp.Size = (SELECT Size FROM tblEquipList WHERE tblEquipList.AdminNum = 'CAT-A');

It gives me the same message "Operation must use an updateable query."

 

[Jon K]

The subquery would make the Update query non-updateable. Try changing it to DLookup():-

strSQL = "UPDATE tblTemp SET AdminNum = '" & cboAdmin.Text & "', " & _
"Size ='" & DLookup("Size", "tblEquipList", "AdminNum = '" & cboAdmin.Text & "'") & "'"
.

 

[JB37]

The subquery would make the Update query non-updateable. Try changing it to DLookup():-

strSQL = "UPDATE tblTemp SET AdminNum = '" & cboAdmin.Text & "', " & _
"Size ='" & DLookup("Size", "tblEquipList", "AdminNum = '" & cboAdmin.Text & "'") & "'"
.

That worked!

Thank you Jon!

 

[Pat Hartman]

1. This query will update every row in tblTemp. Is that what you want?
2. Most DLookup()s can be replaced by joins. DLookup()s are extremely inefficient and joins are much faster.

 

[Jon K]

Originally Posted by Pat Hartman
2. Most DLookup()s can be replaced by joins. DLookup()s are extremely inefficient and joins are much faster.

Pat,

To test the performance of DLookup, I knocked up the attached database, which contained a table modified from the Northwind sample.

I found that on my slow 700 megahertz Pentium III system with 128 MB memory, DLookup was able to return the last record from the non-indexed 21,000-record table immediately after I hit the command button.

I think DLookup is to be avoided only when it is used in a correlated way with an outer table/query where DLookup has to be performed as many times as there are records in the outer table/query. In that case, a join should be used if it is possible.

I don't think a join will make much difference to an independent one-time lookup.

Jon
.

 

[Pat Hartman]

The query has no selection criteria. It will update every row in the table to the form's value. And something I didn't notice earlier is that it is using the .text property. The .text property is only available when a control has the focus so that is likely causing a problem also. I believe you can only reference the default control property in queries so you don't need to qualify the field at all but if you do, use .value.

A single DLookup() isn't likely to cause a problem but using them in queries is to be avoided at all costs.