I believe this is a comon problem that novices like me encounter when working on a project. I did some research and found some good ideas on this forum. I just need some more help to get the think to work...
After reading different solutions for cascading combo boxes I decided to use a solution offered by Roger Carlson.
So, here is my setup:
I am using a main form ("Register"), and a sub form ("RegisterSubform") to enter data into a table ("Registrations"). The table tracks school registrations for diferent courses. In addition to RegistrationId, Date, StudentId, etc., the subform has two combos (Course and Teacher). Both combos are unbound, and when the user clicks on Course and selects from the dropdown list, the second combo - Teacher, should offer only list of those teachers who teach the selected course.
So far I am very close to get it working. The second combo now correctly filters the teachers according to the table TeacherSplecialty (only two field: TeacherId, CourseId). Here are the problems:
1. When I finish all the entry on the subform and am ready to make another record, an error pops up: "cannot save or change the record, because a record in "Courses" is required." Table "Courses" lists all available courses and I use it only as a look up table. All the registration records go into the table "Registrations". So, why this error message?
2. When I open the form all previous entries in the field Teacher on the subform are cleared, and when I select a new entry, it fills in the same value for all previous records too. I was not able to verify if it changes the records in the underlying table, but i suspect it does.
Here are details about my combos:
First combo box:
- name: Course
- row sourse type: Table/Query
- row sourse: SELECT DISTINCTROW [Courses].[CourseId], [Courses].[CourseName] FROM Courses;
- bound column: 1
- After Update Event:
Private Sub Course_AfterUpdate()
Me!Teacher = Null
Second combo box:
-row sourse type: Table/Query
-row sourse: SELECT DISTINCTROW [TeachersSpecialty].[TeacherId] FROM TeachersSpecialty WHERE [TeachersSpecialty].[CourseId]=[Forms]![Register]![RegisterSubform].form!Course;
- bound column: 1
- No Events
Another clarification: table TeachersSpecialty has only two columns: TeacherId and CourseId.
Lastly, if you need a picture of the relationship I can make screen shot and post it.
Hope that some will help me get this to work properly.
Looking at your table relationships, I see one problem that is likely the root of other difficulties.
You have set up a relationship loop set up between REGISTRATIONS > TEACHERS > TEACHERS_SP > COURSES > REGISTRATIONS. You can't add a record in any one of those tables because related records are needed in the other tables. Kind of a chicken-egg thing.
Eliminate the relationship between TEACHERS and REGISTRATION. This is redundant as the needed relationships are available through REGISTRATIONS > COURSES > TEACHERSSP > TEACHERS. The TeacherID field in REGISTRATION can also be deleted as it will now be meaningless.