Question Unable to update ID values

[mauni]

Hi

I have 2 tables:

USER
Userid
Username
Cityid

CITY
Cityid
Cityname


I search all users that have city set:

Select t1.userid, t1.username, t1.cityid, t2.cityid, t2.cityname FROM user as t1, city as t2

Where t1.cityid = t2.cityid


Search outputs all information into a query window:
DoCmd.OpenQuery "User search results", acViewNormal

I want to modify the data via the query window. I am able to change the name values (username, cityname) but not the id values.

If i want to change the user to have another cityid, i get error: "Integrity constraint violation. Primary key for table 'city' is not unique"

I have set all updates to be cascaded. Also the relations have been set to ms access tables properly. Also i have set the setting "Recordset type: Dynaset (Inconsistent updates)".

How can i update the id values from the query window? I don't know why it doesn't cascade the id value update as it should. It only cascades the name changes.

 

[mauni]

STOP THE PRESS

I was able to find solution.

I only have to update the USER table, not CITY table.

This means only the field USER -> CITYID must be in the result view.