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

update lookup field

P: n/a
I realize that lookup fields in a table should not be used but that is
the way this particular creator has done. Could someone help me by
answering my question.

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 the fldTeacher1
field with a new value?

The main table is tblStudent and the lookup table is tblTeacher. The
lookup field in tblStudent is fldTeacher1 and looking at the properties
of fldTeacher1 I see that it has values under the lookup tab of:

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

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.

Can someone please tell me how I am to update tblStudent.fldTeacher1
with a value from tblTeacher through a sql statement in vba? I really
could use help other than the table should not use lookup fields.

Apr 21 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
> I have tried:
DoCmd.RunSQL ("UPDATE tblStudent SET fldTeacher1 = " & Val(txtID) & "
WHERE fldID = " & gintStudentID & ";")
well, that can't be the entire SQL statement - where's the FROM clause? how
about posting *all* the SQL, and explain where you're seeing "the id # in
the field and not display the teacher's name in fldTeacher1 after the
command was run". are you in a form? a query? in tblStudent's table
datasheet view?

hth
"sierra467" <si*******@gmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com... I realize that lookup fields in a table should not be used but that is
the way this particular creator has done. Could someone help me by
answering my question.

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 the fldTeacher1
field with a new value?

The main table is tblStudent and the lookup table is tblTeacher. The
lookup field in tblStudent is fldTeacher1 and looking at the properties
of fldTeacher1 I see that it has values under the lookup tab of:

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

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.

Can someone please tell me how I am to update tblStudent.fldTeacher1
with a value from tblTeacher through a sql statement in vba? I really
could use help other than the table should not use lookup fields.

Apr 22 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.