By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,389 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

duplicate record issue

P: n/a
Hello,

just a small problem i'm hoping someone would help me with.

i have a form which adds skills to a person but i want to make sure that the skill is not entered twice.

is there anyway of preventing a duplicate skill from being entered. (i cant set the table value to no duplicates as there are many skills listed for many employee's and some of these would be the same)

thanks for your help
Lee-Anne

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
You could use the DCount function to see if there already is a record with
the skill that was entered for that employee. Use the BeforeUpdate event of
the textbox/combobox that is being used to enter the skill:

Private Sub ControlName_BeforeUpdate(Cancel As Integer)
If DCount("*", "TableName", "EmployeeID=" & Me.EmployeeID.Value _
& " And SkillID=" & Me.ControlName.Value) > 0 Then
MsgBox "This skill already exists for this employee. Choose or enter a
different skill.", _
vbExclamation, "Skill Already Entered"
Cancel = True
End If
End Sub

--

Ken Snell
<MS ACCESS MVP>

"Lee-Anne Waters via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:b9******************************@AccessMonste r.com...
Hello,

just a small problem i'm hoping someone would help me with.

i have a form which adds skills to a person but i want to make sure that the skill is not entered twice.
is there anyway of preventing a duplicate skill from being entered. (i cant set the table value to no duplicates as there are many skills listed
for many employee's and some of these would be the same)
thanks for your help
Lee-Anne

--
Message posted via http://www.accessmonster.com

Nov 13 '05 #2

P: n/a
Hi Lee-Anne. You can use a compound index to prevent duplicates, but it
requires the right data structure.

You should have 3 tables:
Employee table:
EmployeeID primary key
Surname
FirstName etc

Skill table:
SkillID primary key
SkillName etc.

EmployeeSkill table:
EmployeeID foreign key to Employee.EmployeeID
SkillID foreign key to Skill.SkillID

The interface is then a main form bound to the Employee table, with a
subform bound to the EmployeeSkill table. In the subform you enter a for for
each skill the employee has, using a combo box bound to the Skill table.

If that's the way you are set up:
1. Open the EmployeeSkill table in design view.

2. Open the Indexes box (View menu).

3. Enter a name for a new index, and the EmployeeID field.

4. On the next row in the Indexes box, leave the Name column blank, and
choose the SkillID field. It will look something like this (possibly with
other indexes listed as well):
Index Name Field Name Sort Order
========= ========= =======
EmployeeIdSkillId EmployeeID Ascending
SkillID Ascending

5. Select the row with the index name (EmployeeIdSkillId), and in the lower
pane of the dialog, set the Unique property to Yes.

6. Save.

The index will now prevent you from entering duplicates of the combination
of EmployeeID + SkillID.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lee-Anne Waters via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:b9******************************@AccessMonste r.com...
Hello,

just a small problem i'm hoping someone would help me with.

i have a form which adds skills to a person but i want to make sure that
the skill is not entered twice.

is there anyway of preventing a duplicate skill from being entered. (i
cant set the table value to no duplicates as there are many skills listed
for many employee's and some of these would be the same)

thanks for your help
Lee-Anne

Nov 13 '05 #3

P: n/a
You should be able to create an index using two fields, maybe EmployeeID and
Skill, and set its Unique property to True. Before creating that index, you
will have to ensure that the table does not currently have any of the
duplicates you want to prevent, of course.

"Lee-Anne Waters via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:b9******************************@AccessMonste r.com...
Hello,

just a small problem i'm hoping someone would help me with.

i have a form which adds skills to a person but i want to make sure that the skill is not entered twice.
is there anyway of preventing a duplicate skill from being entered. (i cant set the table value to no duplicates as there are many skills listed
for many employee's and some of these would be the same)
thanks for your help
Lee-Anne

--
Message posted via http://www.accessmonster.com

Nov 13 '05 #4

P: n/a
"Lee-Anne Waters via AccessMonster.com" wrote:
i have a form which adds skills to
a person but i want to make sure
that the skill is not entered twice.

is there anyway of preventing a duplicate
skill from being entered. (i cant set the table
value to no duplicates as there are many
skills listed for many employee's and some
of these would be the same)


How you would do this depends on your table design. I'd have a table that
had a foreign key to the Persons Table, and a foreign key to the Skills
Table. I'd make the combination of these foreign keys the Primary Key of
this "intersection" table and the problem would be solved.

From your description, I'd guess that is not how your tables are laid out.
Tell us how and perhaps someone can make a more useful suggestion.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #5

P: n/a
thanks for your help everyone. as usual there are lots of ways to accomplish the same outcome.

Lee-Anne
Perth WA....

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #6

P: n/a
Why not just make the Persons ID and the Skill a unique index or Primary
key.

Simple to do and quite solid since Access handles the dupes for your
automatically.

HTH
Paul

"Lee-Anne Waters via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:b9******************************@AccessMonste r.com...
Hello,

just a small problem i'm hoping someone would help me with.

i have a form which adds skills to a person but i want to make sure that
the skill is not entered twice.

is there anyway of preventing a duplicate skill from being entered. (i
cant set the table value to no duplicates as there are many skills listed
for many employee's and some of these would be the same)

thanks for your help
Lee-Anne

--
Message posted via http://www.accessmonster.com

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.