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.
--
Steve Schapel, Microsoft Access MVP
"Michael_Randall" <(E-Mail Removed)> wrote in
message news:F7BF63CE-382F-4650-8F9C-(E-Mail Removed)...
> 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?
>
|
Similar Threads
