By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

how to update one table based on values in other table

P: 62
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub YOUR_COMMAND_Click()
  2. Dim strSql As String
  4. strSql = "UPDATE [TABLE_2]" & _
  5.     "SET [TABLE_2].Date_of_Leaving = '"table_1.[DATE_OF lEAVING]'" WHERE EMP_CODE= ' "TABLE1.EMP_CODE " '"
  7. DoCmd.RunSQL strSql
but its not working
Dec 4 '13 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 5K+
P: 5,397
Might I inquire why?
One does not normally duplicate data in a well normalized database.
Dec 4 '13 #2

P: 62
Just need to know whether its possible
Dec 5 '13 #3

Expert Mod 5K+
P: 5,397
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.
Dec 5 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.