Preventing Duplicate Fields in a Junction Table

[Dave White]

Hello Everyone,

I have a Database with 3 Tables

Students
Lessons
Junction

The Students & Lessons Tables are 1 to many to the Junction Table

I have created a form from a query to input all info.

The problem is that I can accidentally assign the same lesson multiple
times to one student.

Is there some way to insure that I assign each lesson only once to a
student?

Thanks in advance,
Dave

 

[ruralguy via AccessMonster.com]

Why not use a DLookUp() or DCount() function in the BeforeUpdate event of the
control where you assign the Lesson. Either that or make a composit index
with Students and Lessons in the Junction table and make it unique. That
will give you a 3022 error when you try and save it.

 

[Allen Browne]

Create a unique index in the junction table to prevent the duplicate.

Presumably the Junction table has a StudentID field (relating to
Students.StudentID) and a LessonID field (relating to Lessons.LessonID.)

1. Open Junction table in design view.

2. Open the Indexes box (View menu.)

3. On a fresh row in the dialog, enter an Index name and the first field. In
the lower pane, set the Unique property to Yes.

4. On the next row of the dialog, leave the index name blank (this row is
part of the one named on the previous row), so you just enter the field
name. It will look something like this:
StudentidLessonId StudentID
LessonID

5. (Optional) Set the Required property for both fields to Yes to prevent
rows where either field is blank.

 

[Dave White]

Thank you both

The Unique/Composite index worked great.

Have a great day!
Dave