update field from another field

history

Egyptian Pharaoh
Local time
, 02:39
Joined
Jan 26, 2008
Messages
190
Dear All,

in the attached database file :

there is a frm_Deployment Form .

I need when I click submit :

the [To site] Field which is shown on the frm_Deployment Form

update the [Sites_Comp] Field to be [To site] Field in the tbl_Emp Table for each employee in the subfrm_Emp Subform ...

in case the [MobilisedDate] Field on subfrm_Emp Subform is Null.

Thanks a lot for your help
 

Attachments

Code:
Private Sub subCmd_Click()

CurrentDb.Execute "UPDATE tbl_Emp INNER JOIN tbl_EmpDep ON tbl_Emp.ID_Emp = tbl_EmpDep.ID_Emp SET tbl_Emp.Sites_Comp = '" & Me.[To Site] & "' WHERE tbl_EmpDep.MobilisedDate Is Null AND tbl_EmpDep.RefNo = '" & Me.[RefNo] & "';"

End Sub

I think that's what you want.

I noticed that you have Name_Emp and Postion_Emp in the tbl_EmpDep. That's not a good idea as you will have to keep these fields in sync with the fields in the tbl_Emp, a near impossible task. A better way is to remove these fields from the tbl_EmpDep and make your records source for the subfrm_Emp a query which joins the tbl_Emp and tbl_EmpDep. If you have any problems with this let me know.

Also tbl_EmpDep should have a primary key; maybe a composite of ID_Emp and RefNo if that fits you business model.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom