Update Value based on ComboBox on Split Form

[ShredDude]

Newbie question...

How do I facilitate updating the records with a combobox? I've attached example of my attempt.

Simple example attached, two tables

tblEmployees
ID
Lastname
DeptID

tblDepartments
DeptID
DeptName
DeptCode

I want to have a Split Form showing all employees, with the ability to select which Department should be assigned to their record from a combobox containing all possible Department Names.

I've read posts on the Evils of Lookup fields, and have attempted to facilitate this without them by including a combobox field on the example form. Played with AfterUpdate event variations but still am not accomplishing what I"m guessing is a simple task.

Set up the form to have a combo box which includes Department Names, bound to hidden first column containing DeptID.

Can someone point me in the right direction please? clearly I'm missing something.

 

[ShredDude]

I may have sorted it. This works, but wondering if there's another way.

Unbound the combobox and then made its after Update this:

Private Sub cboDeptName_AfterUpdate()
Dim sSQL As String
sSQL = "UPDATE tblEmployees SET tblEmployees.DeptID=" & Me.cboDeptName.Column(0) & " WHERE tblEmployees.ID =" & Me!ID & ";"
CurrentDb.Execute (sSQL)
Forms("frmEmpDept").Refresh
End Sub

Is it necessary to the Update Query, or is there another way?

 

[ShredDude]

Bound it to the Dept ID field and eliminated all code. i knew there must be a simple way. Originally I had bound it to the Dept Name field and was destroying the Dept table by putting the Dept Id in the Name field.