Updating a field using a field on another table

[Jax]

I'm trying to do an update on a field using another field in a different
table.
Something like (on AS400 this work perfectly):

update a set a.id_field = (select b.id from b where a.field1 = b.field2)

But access keeps saying that "Updateable query needed".

a and b are tables
a.id_field is indexed
b.id is primary
a.field1 and b.field2 are indexed

What am I missing?

 

[Gary Walter]

Hi Jax,

general Access syntax for update query:

UPDATE tbl1 INNER JOIN tbl2 ON tbl1.PK = tbl2.PK
SET tbl1.f1 = tbl2.f1, tbl1.f2 = tbl2.f2;

so, you might try:

UPDATE a INNER JOIN b
ON a.field1 = b.field2
SET a.id_field = b.id;