how to update one table based on values in other table

[rahul2310]

i have table consider table 1 which contains employee code which is unique and date of leaving
and there is another table consider table 2 where employee code is foreign key and contains Date of leaving(which is blank).
i want on click of button all the employee code present in table 2 to get updated with date of leaving present in table 1
i am trying following code

Private Sub YOUR_COMMAND_Click()
Dim strSql As String

strSql = "UPDATE [TABLE_2]" & _
    "SET [TABLE_2].Date_of_Leaving = '"table_1.[DATE_OF lEAVING]'" WHERE EMP_CODE= ' "TABLE1.EMP_CODE " '"

DoCmd.RunSQL strSql

but its not working
PLZ HELP

[zmbd]

Might I inquire why?
One does not normally duplicate data in a well normalized database.

[rahul2310]

Just need to know whether its possible

[zmbd]

it's possible.
"cheat" on the sql
Do this first using the query editor.
I'll make a copy of both tables, use the QE to create a select query that returns the correct records, then convert to an update and run on the copies. If all goes well, I then switch to the SQL view and copy the sql for use in the VBA code altering to suit variables as needed.
HOWEVER< I rarely do this to copy one value from one table to another, especially if there is a related field.