Prevent duplicate records

[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.

 

[Tom van Stiphout]

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

 

[Song Su]

But this involve 2 tables with one to many relationship:
1. tblApplicant (SID Text type)
2. tblScholarship (ID AutoNumber)

 

[John W. Vinson]

But this involve 2 tables with one to many relationship:
1. tblApplicant (SID Text type)
2. tblScholarship (ID AutoNumber)

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?

 

[Song Su]

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?

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.

 

[John W. Vinson]

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.

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.