Autopopulate table field based on selection in previous field

  • Thread starter Thread starter Michael_Randall
  • Start date Start date
M

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?
 
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.
 
Back
Top