Update a Lookup field

[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.

 

[Rich]

You shouldn't use LookUp fields in tables, you only need to store the Pk from the foreign table in your Primary table

 

[sierra467]

I realise that lookups in tables is not a sound idea but I was trying to do a favor for someone and did not feel like getting into their design issues. Is there a way to write a sql statement that will enter the data in correctly?