Form Entry - Don't allow duplicate entry from form

[marklane2001]

Hi

I currently have an input form called ENROL_CLASS which has 3 fields. They are:

txtPupilID
txtClassID
txtTermID

I have a button call btnEnrolStudnet. All fields have to have data in to proceed. There is a table called CLASSES_TAKEN and an insert query which runs when the btnEnrolStudent is pressed. This inserts the data on the form into the CLASSES_TAKEN field.

I want to check for duplicates first in the table before the insert takes place. Duplicates occur when all 3 fields are the same in the database not just one e.g. PUPIL ID - CLASSID -TERMID all have to be the same. The system will allow it if PUPILID and CLASSID are already in the table but the TERMID is not. All 3 fields in the table have to match the form for it to be classed as duplicate.

If it is a duplicate I would like a message box to appear to advise this and then when the message OK is pressed for the fields to be cleared once again.

Please help as I am really struggling with this.

Thanks

 

[MStef]

Make those 3 fields as PRIMARY KEY.

 

[marklane2001]

Thanks for the reply but that won't work as I need to be able to enter duplicate records in each field eg. more than one pupilID in the table. It is only a duplicate when the combination of PUPILID-CLASSID-TERMID have been entered before

 

[MarkK]

You could run a DCount() that checks whether the data already exists in the table. If so, send a message, else do the insert...

if DCount("*", "ClassesTaken", _
  "PupilID = " & me.tbPupilID & " " & _
  "AND ClassID = " & me.tbClassID & " " & _
  "AND TermID = " & me.tbTermID) > 0 Then
  [COLOR="Green"]'a record already exists[/COLOR]
  msgbox("this pupil is already registered for this class for this term.")
else
  [COLOR="green"]'data is unique, do the insert[/COLOR]
  currentdb.execute "INSERT INTO ... "
end if

 

[MStef]

I have said "Make those 3 fields as PRIMARY KEY". It means 1 Primary key, composed of 3 fields.

 

[MarkK]

I would not recommend three fields as a primary key. Perhaps a unique index, but in that case attempting an insert that is not unique generates an error, still something I'd rather check for in advance.