Restrict double entries to subform (1 Viewer)

[steven patrick arrow]

Looking for help with a bit of code!

I have 3 tables tblEmployees, which records staff names – each is assigned a staff reference no [SRID]

tblTrainingLog – records TrainingEvents - i.e CourseID and CourseDate

tblJctEmpRecord – which records the staff who attended training




When I record training I use a form with a subform –

Form is for tblTrainingLog and subform is for tableJctEmpRecord.


The problem that I have is double entries –

When recording I need to have 3 items to make a record individual – CourseDate, CourseName and Employee who took course

Many people can take the same course on the same date, all entries will get a Training Log Number [TRID], but as every TRID is individual do not know how to restrict entry to the subform – which enters the employee name.

Any ideas?

 

[arnelgp]

make the "name" textbox on the subform as combobox and will only save the SRID.
validate the entry (BeforeUpdate event) to check if same SRID is already entered on same course
on same date.

 

[theDBguy]

I would also suggest an additional safety measure of creating a unique multi-field index to your table's design.

 

[Pat Hartman]

We use autonumbers as the pk for efficiency and convenience if there isn't an actual candidate key. But, whenever there is a combination of unique values that will enforce a business rule, it is necessary to create a compound unique index in addition to the autonumber PK as theDBguy suggested.

To make a multi-field index, you must use the indexes dialog.