This is a limitation in Access.
There is no logical reason why the UPDATE query won't run; it's just that
Access doesn't cooperate (perhaps erring on the side of caution for some
other non-obvious scenario.)
You may be able to use a function call instead of a subquery. DLookup()
isn't powerful enough if you need the ORDER BY clause (as in the example),
so we wrote a replacement that does allow you to specify how to sort the
records (which defines which is the TOP 1.) See:
ELookup() - an extended replacement for DLookup()
at:
http://allenbrowne.com/ser-42.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
M.L. Abram said:
I am looking for a query such as this so I had set up a DB with the
scenario
Arne had provided to test. When I executed the query I received an error
"Operation must use an updateable query". Reviewing the query it is setup
as
an Update Query and I also reviewed the Help with no additional success.
What
am I missing?