Referencing a field on a form's table with an event handler executed by a subform

[April Marano]

I'm using Access 97 to do the following:

I have a form called frmStudents based on a table called tblStudents which
has a primary key called StudentIDNum as well as fields such as Name, DOB,
etc. There is also a field called CurrentTeacherIDNum which contains the ID
number of the student's most recent teacher.

frmStudents contains a subform (in datasheet view) called sbfTeachers.
sbfTeachers is bound to a table called tblLinkStudentTeacher which contains
in addition to a primary key, the fields StudentIDNum and TeacherIDNum.

Each student will have ONE teacher at a time but will have a "history" of
several teachers. The subform will show all the teachers a student has in
the order in which they were entered. Form and subform are linked by child
and master fields StudentIDNum.

I want to fill the frmStudents field CurrentTeacherIDNum with the
TeacherIDNum of the most recently entered teacher from the subform using an
event handler executed from sbfTeachers using the AfterUpdate event. How do
I do this? I think I'd be OK if I could figure out how I NAME the
CurrentTeacherIDNum field in my code. I've tried

Forms!frmStudents!CurrentStudentIDNum = Me!TeacherIDNum

but this doesn't work. Any ideas?

April

 

[Randy Harris]

I'm using Access 97 to do the following:

I have a form called frmStudents based on a table called tblStudents which
has a primary key called StudentIDNum as well as fields such as Name, DOB,
etc. There is also a field called CurrentTeacherIDNum which contains the ID
number of the student's most recent teacher.

frmStudents contains a subform (in datasheet view) called sbfTeachers.
sbfTeachers is bound to a table called tblLinkStudentTeacher which contains
in addition to a primary key, the fields StudentIDNum and TeacherIDNum.

Each student will have ONE teacher at a time but will have a "history" of
several teachers. The subform will show all the teachers a student has in
the order in which they were entered. Form and subform are linked by child
and master fields StudentIDNum.

I want to fill the frmStudents field CurrentTeacherIDNum with the
TeacherIDNum of the most recently entered teacher from the subform using an
event handler executed from sbfTeachers using the AfterUpdate event. How do
I do this? I think I'd be OK if I could figure out how I NAME the
CurrentTeacherIDNum field in my code. I've tried

Forms!frmStudents!CurrentStudentIDNum = Me!TeacherIDNum

but this doesn't work. Any ideas?

April

This is from the Access Web. I don't use the URL any more, I refer to this
thing so frequently that I've got it hanging on the wall.
http://www.mvps.org/access/forms/frm0031.htm

HTH,
Randy

 

[April Marano]

Thanks Randy,

That's very helpful. That chart is excellent for referencing controls and
properties. How do I reference fields in the form's (and subform's)
underlying tables or queries?

April

 

[Gary Walter]

Hi April,

One gotcha (I *barely* remember) involved
someone trying to reference a field in the
main form's source (that was not bound to
a control) from the subform. He would get

"Error 2486: You can't carry out this action at the present time."
and the application would lock up.

His solution was to create a *hidden* control
on the main form bound to this field, and
only reference this main form's control from
the subform.

Sorry I cannot be more specific, but I
thought it was important to mention.

Good luck,

Gary Walter