Operation must use updatable query

[kunguito]

I get the following error: Operation must use updatable query, when attempting to perform that:



UPDATE TABLE
SET TABLE.efficiency =
(SELECT [query]![efficiency] FROM [query], TABLE WHERE
[query]![CMODEL]=TABLE.CMODEL;)
WHERE (((TABLE.Month)=[vMonth]) AND ((TABLE.Year)=[vYear])));


It's getting on my nerves!

[CreganTur]

This error generally occurs because either your query contains more than one table, or it uses aggregate functions; or it occurs because of a permissions error when accessing your .mdb via ASP.

One way you could address this would be to mix VBA and SQL...you could use a DAO connection to get the value of your sub SELECT query, and then step through those values as a part of your main UPDATE query... something like:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [query].[efficiency] " _
& "FROM [query], TABLE " _
& "WHERE [query].[CMODEL]=TABLE.CMODEL;")
rst.MoveFirst
DoCmd.SetWarnings False 'turn off Update warning message
Do Until rst.EOF
DoCmd.RunSQL "UPDATE Table SET TABLE.efficiency = " & rst!FieldName _
& " WHERE TABLE.Month=vMonth AND TABLE.Year=vYear;"
Loop
DoCmd.SetWarnings True 'turns all warning messages back on

You'll just need to adjust your SQL accordingly.

HTH:thumb

[kunguito]

Yeah, ok, it's what i thought i'd end up doing. It's just I like neat code, you know.

Thanks CreganTur!