duplicate record issue

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

re: duplicate record issue

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" <forum@AccessMonster.com> wrote in
message news:b98957978ca1490ea55c6e50999a6ff1@AccessMonste r.com...[color=blue]
> 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[/color]
the skill is not entered twice.[color=blue]
>
> is there anyway of preventing a duplicate skill from being entered. (i[/color]
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)[color=blue]
>
> thanks for your help
> Lee-Anne
>
> --
> Message posted via http://www.accessmonster.com[/color]

re: duplicate record issue

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" <forum@AccessMonster.com> wrote in
message news:b98957978ca1490ea55c6e50999a6ff1@AccessMonste r.com...[color=blue]
> 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[/color]

re: duplicate record issue

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" <forum@AccessMonster.com> wrote in
message news:b98957978ca1490ea55c6e50999a6ff1@AccessMonste r.com...[color=blue]
> 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[/color]
the skill is not entered twice.[color=blue]
>
> is there anyway of preventing a duplicate skill from being entered. (i[/color]
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)[color=blue]
>
> thanks for your help
> Lee-Anne
>
> --
> Message posted via http://www.accessmonster.com[/color]

re: duplicate record issue

"Lee-Anne Waters via AccessMonster.com" wrote:
[color=blue]
> 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)[/color]

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

re: duplicate record issue

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

re: duplicate record issue

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" <forum@AccessMonster.com> wrote in
message news:b98957978ca1490ea55c6e50999a6ff1@AccessMonste r.com...[color=blue]
> 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[/color]