Help needed for SQL string - UPDATE

[DADAZHU]

Hi,



I need some helps with UPDATE SQL string. I use ADO connection to an access DB. I have some tables as following,

tblUnit: CC, Name, Cluster_ID, Area_ID, Type_ID
tblClusterTable2: Cluster_ID, Cluster_Name
tblArea: Area_ID, Area_Name
tblType: Type_ID, Type_Name

In user interface, users get to see the table1 info:CC, Name, Cluster_Name, Area_Name, Type_Name, and user can change the names except CC. I display the info by using inner join the tables without a problem. However, I ran into problems when I tried to update the tables if an user changes one or many of the names. My SQL is as below,

strSQL = "UPDATE ((tblUnit" _
& " INNER JOIN tblType ON tblUnit.Type_ID=tblType.Type_ID)" _
& " INNER JOIN tblCluster ON tblUnit.Cluster_ID=tblCluster.Cluster_ID)" _
& " INNER JOIN tblArea ON tblUnit.Area_ID = tblArea.Area_ID " _
& " SET tblUnit.Unit_Name ='" & strUName & "'," _
& " tblCluster.Name ='" & strCName & "'," _
& " tblArea.Name ='" & strAName & "'," _
& " tblType.Name ='" & strTName & "'" _
& " WHERE CC ='" & strCC & "'"

I need to update tblUnit only when an unit's profile is changed, eg. from an area to another,a Area_ID replaces another, and no change is to be made to all other tables, . However, when the SQL is executed, it seemed nothing happened, or sometime it went ahead and changed the other tables(eg. tblArea), and create duplated value.

I hope I explain clearly. Please let me know if you need more info.

Many thanks in advance.

[Guus2005]

If you only want to change the tblUnit table, why are you setting the other fields in the other tables as well?

strSQL = "UPDATE ((tblUnit" _
& " INNER JOIN tblType ON tblUnit.Type_ID=tblType.Type_ID)" _
& " INNER JOIN tblCluster ON tblUnit.Cluster_ID=tblCluster.Cluster_ID)" _
& " INNER JOIN tblArea ON tblUnit.Area_ID = tblArea.Area_ID " _
& " SET tblUnit.Unit_Name ='" & strUName & "'," _
& " tblCluster.Name ='" & strCName & "'," _
& " tblArea.Name ='" & strAName & "'," _
& " tblType.Name ='" & strTName & "'" _
& " WHERE CC ='" & strCC & "'"

HTH