473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
Nov 13 '05 #1
6 3032
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
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
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
"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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Robert | last post by:
How can I query an existing table and update a field in each record in the table with the occurrence count of each record e.g. update field to 1 (= first record occurrence), update field to 2 for...
8
by: Mark | last post by:
When my form goes to a new record, I have a procedure that copies the last record added to the form's underlying table into the form. The intent is that a series of new records may have the same...
3
by: Nhmiller | last post by:
I searched here for an answer. I am entering records into a database, and it would save a lot of time if I could duplicate a record that is very similar to the new one I am about to enter, then...
2
by: stranger | last post by:
My database is set up so people can input parts orders. Sometimes they order the same parts on a monthly basis. I want to be able to duplicate past parts orders and have it pasted in with a new...
6
by: planetthoughtful | last post by:
Hi All, I have a C# ASP.NET page that submits back to itself to insert details from a form into a database table. When / if the user refreshes the page (and gets the standard warning that POST...
9
by: Tom_F | last post by:
To comp.databases.ms-access -- I just discovered, to my more than mild dismay, that some tables in my Microsoft Access 2003 database have duplicate numbers in the "AutoNumber" field. (Field...
3
by: ammie65 | last post by:
I have been trying to create a purchase order database in Access, and I have been successful in creating all the tables, queries and reports that I need. I have only one issue: I need to copy the...
1
by: OxfordConsult | last post by:
Hello, I have created a form that links with a table with the autonumber feature as its primary key. Whenever i create a new record in the form (using a command button ... i have disabled the...
1
by: bemadragon | last post by:
Hello, I have been struggling with this for a while. I am working on a rather simple database for calculation purposes in Access 2003. I have a table called Order that has a primary key field...
6
by: Dave | last post by:
I really don't like the users getting an unhandled expception page, and I'm still to new with ASP.Net and C#. So please accept my appology for the 2 part question. SqlException (0x80131904) ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.