Prevent duplicate records

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

Student can apply multiple scholarships but how to prevent user enter same
student and same scholarship more than once?

I have SID (student ID) and dropdown box to select Scholarship. I know there
should be BeforeUpdate event to check if same student already has a record
for that scholarship but I don't know how to write code. Please help.
Thanks.
 
Writing code is not the way to do it. Rather this should be done at
the database level, with a unique index on the appropriate fields.

-Tom.
Microsoft Access MVP
 
But this involve 2 tables with one to many relationship:
1. tblApplicant (SID Text type)
2. tblScholarship (ID AutoNumber)
 
But this involve 2 tables with one to many relationship:
1. tblApplicant (SID Text type)
2. tblScholarship (ID AutoNumber)
Click to expand...

It had jolly well better involve another table - tblApplications let's say -
unless each scholarship is available to one and only one student. Is that the
case?
 
John W. Vinson said:
It had jolly well better involve another table - tblApplications let's
say -
unless each scholarship is available to one and only one student. Is that
the
case?
Click to expand...

Each scholarship is available to many students and each students can apply
more than 1 scholarships. What I'm trying to avoid is entering the same
student with same scholarship more than once.
 
Each scholarship is available to many students and each students can apply
more than 1 scholarships. What I'm trying to avoid is entering the same
student with same scholarship more than once.
Click to expand...

In that case you need THREE tables, not two:

tblApplicant
SID <Text, Primary Key>
LastName
FirstName
<other biographical data>

tblScholarship
ID <Autonumber, Primary Key>
ScholarshipName <Text>
<other info about the scholarship>

tblApplications
SID <link to tblApplicant, who's applying>
ScholarshipID <link to tblScholarship ID, what are they applying for>
ApplicationDate
<other information about this application for this scholarship>

Open tblApplications in design view, and ctrl-click both SID and
ScholarshipID. Click the key icon on the toolbar to make these two fields a
joint primary key. This will prevent the duplicates you mention, but still
allow each applicant to apply for multiple scholarships and each scholarship
to receive multiple applicants.
 
Back
Top