Update a Lookup feild

[sierra467]

I have a table (tblStudent) containing a field named fldTeacher1. Looking at the properties of fldTeacher1 I see that it has a lookup:

Bound Column: 1
Column Count: 2
Column Widths: 0";1" (id number; teacher's Name)

I am trying to run a DoCmd.RunSQL command in code and My question is how do I write a sql statement in code that updates that field with a new value?

I have tried:
DoCmd.RunSQL ("UPDATE tblStudent SET fldTeacher1 = " & Val(txtID) & " WHERE fldID = " & gintStudentID & ";")

All it does is place the id # in the field and not display the teacher's name in fldTeacher1 after the command was run.

 

[sierra467]

Sorry for the double post, I can not find a way to delete this one.