Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bridge Entity (Access 2002)

    I have returned to using Access after a 3 yr break & I'm having a problem with the basic set out of the tables

    I have two tables for a dance school
    tblStudents with fields StudentID StudentName StudentDOB etc
    and
    tblClasses with fields ClassID ClassName ClassTime etc

    As Students and Classes have a many to many relationship, I created a bridge entity; a table called
    tblStdentActivity with fields StudentID ClassID
    with both fields selected as the primary key.

    I then created a query that has the three tables
    They are linked and Referential Integrity has been selected
    The only fields that are selected in the query are the two fields from tblStudentActivity

    I have created a form from the query using the wizard
    The form I have has the StudentID (showing one Student) at the top and the ClassID in a subform (showing a list of all their classes)

    I want to be able to select classes for the student from the list of Classes that exist in tblClasses

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>1. When I type a new class for the student I get a warning
    <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> Field cannot not be updated <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    I click OK but then I am allowed to keep typing in that field

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>2. Then I can enter a ClassID that doesn't exist despite selecting "enforce referential integrity" in the relationships window. This then appears as a new ClassID in the table tblClasses

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>3. If I enter a ClassID that does exist I get a warning
    <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15> The changes ... were not successful ... duplicate values in the index, primary key, or relationship ... <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bridge Entity (Access 2002)

    For a many-to-many relationship, you cannot enter data for both tables in one form. You need a main form and a subform. Depending on what you want to do, you can have a main form representing the classes, and a subform representing the students attending a class, or a main form representing the students, and a subform representing the classes they attend.

    You'll find a very simple demo attached to <post:=364,203>post 364,203</post:>.

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bridge Entity (Access 2002)

    Thank you for the example
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

    It took awhile to find out what I was doing wrong as my forms and subforms looked like the ones in the example

    I had created the form and subform from a single query.

    I changed it so the subform's data source is the bridge Entity (tblStudentActivity) and and the form's data source is the student table.

Posting Permissions

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