options in field entry from another table

I am "getting started" in MS Access, but not in programming.

My problem, a database for entering transactions about trainee teachers that
do some supervised lessons in schools.
Can be "dirty programmed" in terms of interface, but should be easy to use
by me, catch some errors etc

Table Trainee: // 10 records
ID : auto number and main index
name
school1 : text
school2 : text // some trainees may be assigned to 2 schools, and give
lessons in both of them
...


Table Lessons: // lots of records!
ID: same as ID in Trainee
date:
school: // one of the two schools the trainee is assigne to *** here is
my problem ***
class: text // free
...

Naturally a relationship established between Lessons.ID to Trainee.ID
I created a form for input of "Lessons" transactions, that displays the
Trainee.name when ID is entered.
*********************************************
What I want to do, but don't know how:
*********************************************
a) pressing on the name field to show a dropdown list with the name's in
Trainee to choose from, which will update the Lessons.ID. This is not so
important (there are only 10 entries in the Trainee table, and can have them
printed together with the ID's. Displaying the name solves the problem of
making an error

b) pressing on the school field, display a list or combo with
Trainee.school1 and Trainee.school2 to choose from and update the
transaction.
This is my main problem!!!

Any help?

TIA

Alberto

AH, the problems of non-normalized data!

How about this approach?
tblTrainee - Identifies trainee, includes TraineeID, no fields for
school.
tblSchool - A list of all possible schools, including SchoolID
tblTrainee_School - 2 fields, TraineeID and SchoolID
An entry in this school indicates this trainee is assigned to this
school
tblLessons - pretty much as designed, with foreign keys for TraineeID
and SchoolID

Now, your form will be based on tblLessons, and have a combobox for Trainee,
based on tblTrainee.
It can also have a combobox for School, based on tblSchool.
If you like, you can filter the School combobox when something is
entered in the Trainee combobox, so the School combobox shows only schools
available for that trainee.

Please post back if you need details on implementing any of this.
HTH
- Turtle

wrote in message
...
I am "getting started" in MS Access, but not in programming.

My problem, a database for entering transactions about trainee teachers
that
do some supervised lessons in schools.
Can be "dirty programmed" in terms of interface, but should be easy to use
by me, catch some errors etc

Table Trainee: // 10 records
ID : auto number and main index
name
school1 : text
school2 : text // some trainees may be assigned to 2 schools, and give
lessons in both of them
...


Table Lessons: // lots of records!
ID: same as ID in Trainee
date:
school: // one of the two schools the trainee is assigne to *** here is
my problem ***
class: text // free
...

Naturally a relationship established between Lessons.ID to Trainee.ID
I created a form for input of "Lessons" transactions, that displays the
Trainee.name when ID is entered.
*********************************************
What I want to do, but don't know how:
*********************************************
a) pressing on the name field to show a dropdown list with the name's in
Trainee to choose from, which will update the Lessons.ID. This is not so
important (there are only 10 entries in the Trainee table, and can have
them
printed together with the ID's. Displaying the name solves the problem of
making an error

b) pressing on the school field, display a list or combo with
Trainee.school1 and Trainee.school2 to choose from and update the
transaction.
This is my main problem!!!

Any help?

TIA

Alberto