Search Individual Record for Duplicates?

  • Thread starter Thread starter Les Coover
  • Start date Start date
L

Les Coover

Access 2000

Each Student Record has many
Course Records, but in any one
Student Record there can not
be duplicates.

i.e. A student can't be enrolled
in the same class twice.

How can I make a routine that
will print a "Duplicate Course for
Student" message if a duplicate
is entered?

The following is my attempt using
the Exit Event for the
COURSE_NO field

Private Sub COURSE_NO_Exit(Cancel As Integer)
Dim myField As Field
For Each myField In Me![LINE SUBFORM]
IF SOMETHING TRUE THEN
MsgBox ("Duplicate Course for Student")
Else
End Sub
Next
End Sub

Any help welcomed, thanks Les
 
Les, this design is wrong. You need at least these tables:

Student table:
StudentID AutoNum primary key

Unit table:
UnitID primary key
UnitName

Class table:
ClassID AutoNum primary key
UnitID Number foreign key (FK) to Unit.UnitID
StartDate Date/Time date of the first lecture for this class.

Enrol table:
EnrolID AutoNum primary key
ClassID Number FK to Class.ClassID
StudentID Number FK to Student.StudentID
EnrolDate Date/Time date this student enrolled in this class.

This structure allows you to have multiple instances of a unit (e.g. offer
Psych 101 each year). The students enrol in an instance of a unit (called a
Class above). The Enrol table lets a student enrol in many classes, one per
row.

With that structure, it is very easy to prevent a student enrolling in a
class twice:
1. Open the Enrol table in design view.

2. Open the Indexes (View menu).

3. Enter an index named (say) ClassIdStudentId, with ClassID in the 2nd
column.

4. On the next row in the Indexes box, leave the Name column blank, and
choose StudentID in the 2nd column.

5. In the lower pane of the Indexes box, set Unique to Yes.

This index prevents the entry of duplicate ClassID + StudentID combinations.
 
Thanks Again Allen

Les

Allen Browne said:
Les, this design is wrong. You need at least these tables:

Student table:
StudentID AutoNum primary key

Unit table:
UnitID primary key
UnitName

Class table:
ClassID AutoNum primary key
UnitID Number foreign key (FK) to Unit.UnitID
StartDate Date/Time date of the first lecture for this class.

Enrol table:
EnrolID AutoNum primary key
ClassID Number FK to Class.ClassID
StudentID Number FK to Student.StudentID
EnrolDate Date/Time date this student enrolled in this class.

This structure allows you to have multiple instances of a unit (e.g. offer
Psych 101 each year). The students enrol in an instance of a unit (called a
Class above). The Enrol table lets a student enrol in many classes, one per
row.

With that structure, it is very easy to prevent a student enrolling in a
class twice:
1. Open the Enrol table in design view.

2. Open the Indexes (View menu).

3. Enter an index named (say) ClassIdStudentId, with ClassID in the 2nd
column.

4. On the next row in the Indexes box, leave the Name column blank, and
choose StudentID in the 2nd column.

5. In the lower pane of the Indexes box, set Unique to Yes.

This index prevents the entry of duplicate ClassID + StudentID combinations.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Les Coover said:
Access 2000

Each Student Record has many
Course Records, but in any one
Student Record there can not
be duplicates.

i.e. A student can't be enrolled
in the same class twice.

How can I make a routine that
will print a "Duplicate Course for
Student" message if a duplicate
is entered?

The following is my attempt using
the Exit Event for the
COURSE_NO field

Private Sub COURSE_NO_Exit(Cancel As Integer)
Dim myField As Field
For Each myField In Me![LINE SUBFORM]
IF SOMETHING TRUE THEN
MsgBox ("Duplicate Course for Student")
Else
End Sub
Next
End Sub

Any help welcomed, thanks Les
Click to expand...
Click to expand...
 
Back
Top