Autopopulate table field based on selection in previous field

[Michael_Randall]

I am trying to autopopulate a field in a table based on a selection in the
previous field. Here are my tables:

CLASS(ClassID(PK),CourseNumber(FK),InstructorID(FK))
COURSES(CourseNumber(PK), CourseName)
INSTRUCTORS(InstructorID, FName, LName)
INSTRUCTORCOURSES(InstructorID,CourseNumber) - junction table with composite
PKs

I've already filled out the INSTRUCTOR, COURSE, and INSTRUCTORCOURSES tables
and their relationships/ref integrity are already set up in Access. What I
would like to do is, in the CLASS table select a CourseNumber and based on
that CourseNumber, the InstructorID field is populated with a drop-down list.

Currently, I'm not using a Lookup for the CourseNumber in the table CLASS.
I'm considering basing that field (row source) on a query or the COURSE
table. I'm open to other solutions.


First, is this possible? Second, is this good database design? Third, how do
you do it, if you can?

 

[Steve Schapel]

Michael,

Yes, this can be done.

You should be using a form for your data entry.

You can use a query as the Row Source of the InstructorID combobox. The SQL
view of such a query will look something like this...
SELECT InstructorCourses.InstructorID, [LName] & ", " & [FName] AS
InstructorName
FROM InstructorCourses INNER JOIN Instructors ON
InstructorCourses.InstructorID = Instructors.InstructorID
WHERE InstructorCourses.CourseNumber = [Forms]![NameOfForm]![CourseNumber]

If you also have a combobox for the CourseNumber, then you will probably
need to use macro or VBA code to Requery the InstructorID combobox on the
After Update event of the CourseNumber control.