Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    65

    Unhappy Unanswered: Cascading combo boxes

    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
    Me!Teacher.Requery
    End Sub

    Second combo box:
    -name: Teacher
    -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.

    Thanks,
    Boka

  2. #2
    Join Date
    Jan 2003
    Posts
    46
    Boka.

    You indicate that both the main form and subform are based on a single table "Registrations" IF this is the case, you need to split your table or combine the forms.

    That doesn't exactly answer your questions though. If you can e-mail a copy of the relevant part of the application, I can sort out your problem in a few minutes.

    Cheers,
    zambezibill

  3. #3
    Join Date
    Feb 2003
    Posts
    65

    Arrow

    Actually my main form is based on tbl "Students", and the subform on tbl "Registrations". Please refer to the attached image of the table relationship.

    Hope this will clarify the situation.

    Thanks again for your help.
    Boka
    Attached Thumbnails Attached Thumbnails db - table relationship.bmp  

  4. #4
    Join Date
    Jan 2003
    Posts
    46
    Boka,

    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.

    Cheers,
    zambezibill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •