This issue gets at a common misunderstanding between Fields and Controls.
Fields are attributes of a table, and have a data type--Text, Number,
Date/Time, etc. Form and report controls DO NOT have a datatype, they are
merely a container in which to display data. If they are additionally Bound
to a field in the form's RecordSource, then data entered into the control is
additionally saved to the table field.
A control can have NO ControlSource, it can be bound to a field, OR it can
be set to the result of an expression. In the latter case, the control will
display the result of the expression, if it can be evaluated. This was what
I was suggesting, that you set the ControlSource of an unbound textbox to the
result of the expression that uses the list box' Column property. The full
syntax is:
YourListBoxName.Column(x), where x is the column number, starting with 0.
So the email address is to be displayed on your form, providing the user
information, but not stored to the Students table.
It also touches on the topic of Database Normalization. In a relational
database, each "thing" has its own table, and each "attribute" of the "thing"
is a different field. Each table should have a primary key, the value of
which uniquely identifies each record.
Since the email address is an attribute of the teacher, not the student, it
belongs in the Teachers table. All that is necessary to relate this student
to that teacher is to store the primary key of the teacher in a field of the
same type in the Student table. If you use an AutoNumber primary key in
Teachers, then it would be an Integer field type in the corresponding field
in Students. This is thus called a foreign key, and it "unlocks" or gives
"Access" to all of the field values of this teacher record via a query.
To add the textbox, open your form in Design View. Verify that the Toolbox
is displayed (View, and select Toolbox if it is not already selected). The
textbox icon is the one with the small "ab" followed by a vertical line.
Select, pick a spot on your form for its upper left hand corner, and then
drag to the bottom right corner. Use the Align and Size commands on the
Format menu to resize and position the control relative to the others. Then
set the ControlSource to the expression in my last post. Be sure that
"YourListBox" is changed to the Name of the list box control (the Name
property).
Hope that helps. For more information on database normalization, see the
following resources. In my opinion, it's the most fundamental topic in
developing relational applications. If your tables are not normalized, you
will be in for a host of problems.
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172
http://support.microsoft.com/?id=129519
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html
Sprinks