How do I auto populate fields in a form from information in anoth.

[MrsSmythe]

I have created a data base that list all the students in our school and the
information we need to know about them and all of the significant
academic/administrative records we need. I need to know how to auto populate
different fields in my forms based on data from other tables. For example, A
teacher may be inputting student records and when the type the student ID I
want the form to find it in the General Information Table and fill in the
First Name and Last Name of the student. Another example is when entering the
students schedule I enter the teachers name I would like the room number to
auto populate but I also need to be able to edit this if a teacher is in a
different room for some reason. Lastly I have no clue how to write or edit
VB. If you can't answer me on the thread I am at (e-mail address removed).

Thanks,
Barb

 

[John Vinson]

I have created a data base that list all the students in our school and the
information we need to know about them and all of the significant
academic/administrative records we need. I need to know how to auto populate
different fields in my forms based on data from other tables. For example, A
teacher may be inputting student records and when the type the student ID I
want the form to find it in the General Information Table and fill in the
First Name and Last Name of the student. Another example is when entering the
students schedule I enter the teachers name I would like the room number to
auto populate but I also need to be able to edit this if a teacher is in a
different room for some reason. Lastly I have no clue how to write or edit
VB. If you can't answer me on the thread I am at (e-mail address removed).

Thanks,
Barb

You're making a very common error about how relational databases work.

Your student records table should contain the student ID - and it
should NOT contain *any* other fiels such as the name. Copying the
name to your second table would be redundant, and would cause problems
if the student's name needs to be changed.

You can *display* the name on the form; the simplest way is to just
use a Combo Box which stores the ID but displays the name, or you can
include the ID and the first and last names in the combo box's Row
Source query and put textboxes on the form with Control Source such as

=comboStudent.Column(n)

where comboStudent is the Name property of the combo box, and (n) is
the *zero based* position of the field in the combo's query.

Your second example is a valid one, though, since you *WANT* to be
able to have the room stored differently in the two tables. To do so,
put code like the following in the Combo Box's AfterUpdate event.
Click the ... icon and invoke the Code Builder, and edit to something
like:

Private Sub cboTeacher_AfterUpdate()
If IsNull(Me!txtRoomNo) Then ' don't step on existing data
Me!txtRoomNo = cboTeacher.Column(2)
End If
End Sub

This assumes that the combo has three columns, TeacherID, TeacherName,
and RoomNo - adjust as needed.

John W. Vinson[MVP]