?
Solved

Prevent duplicate entries

Posted on 2003-02-21
9
Medium Priority
?
516 Views
Last Modified: 2008-03-03
I need to prevent a user from duplicating an entry in MS Access. I would like the app. to check for an exact match and if it finds one warn the user. Ideally the warning will not allow the user to enter the duplicate data. I have a query that will identify the duplicates after they are entered, but need to prevent the actual entry. Entry to the main data table is being performed by an append query. The user copies data from an Excel spreadsheet and paste it into a temporary table. Then clicks a control that appends it to the data table. Several fields must match to determine if it is a duplicate entry.

EmpID   Date   ErrorCd

In other words if that employee committing that error on that date already existed an additional entry for the same employee on the same date with the same error code would be a duplicate. The same employee could commit a different error for the same date though without it being a duplicate
0
Comment
Question by:WishIKnew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 7994489
Normally you would have a field like EmployeeID that's making an employee unique.
Just define that field as key (see key button in table) and access will make sure no duplicates can be entered.
When you have a combination of fields then use the index to make both field unique.

Need more info ?

Nic;o)
0
 
LVL 1

Expert Comment

by:rquaglia
ID: 7994523
If you have the query already made, you can try something like this when the user click button to add record:

--------------
dim rs as recordset  ' maybe dao.recordset in Access 2002
set rs = currentdb().openrecordset("YourQueryName")
if not rs.eof then
       ' errormsg
else
       ' append record
endif
--------------
I suppose that your query "YourQueryName"  returns a recordset if the record your user is trying to append exists...


I hope this helps!
Roby
0
 

Accepted Solution

by:
Suomenlapinkoira earned 160 total points
ID: 7994557
Simple (-istic?) solution: define the table primary key as the three fields in question. Then the database engine itself will not allow a duplicate record to be created. The error message the user gets would probably befuddle them, though! Try it and see what you think.

Alternatively, use VBA to do a table lookup using the three values in each proposed new record, giving a more controlled message to the user if an existing record is found.

Hope this helps - I'm new to giving advice instead of asking for it!
0
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

Author Comment

by:WishIKnew
ID: 7994879
This database is used to track errors from our employees. Since a single employee can have multiple errors over a period of time I can't set the EmployeeID field to no duplicates (Primary Key). Likewise a single employee may have several different errors on the same day. However we will never record the same employee with the same error on the same day. Any new entry into the Errors table would first have to check to see if empl# 1234 already had an error type 201 on 2/19/03. If all 3 fields matched it would be considered a duplicate.
It looks like these solutions won't allow me to enter the employee in the Errors table more than once (defeats the purpose).
I'll try the solution from  rquaglia  and see if that works,
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7995329
This just asks for a main employee form with date and employee liked to a subform with the error records.
Thus you can see all entered errorforms for that employee.

Idea ?

Nic;o)
0
 

Expert Comment

by:Suomenlapinkoira
ID: 8007066
Dear WishIKnew
I'm not sure which suggestion you are responding to, but you CAN have multiple occurrences of the EmpID if the Primary Key consists of all three fields. The only thing that cannot be duplicated is a specific combination of EmpID, Date and ErrorCd - which is exactly what you want, isn't it? You could still use a lookup first, such as rquaglia suggests, to 'soften' the error message.
Regards from the Smiley Dog
0
 

Author Comment

by:WishIKnew
ID: 8008938
Thanks for the help. I have never defined more than one primary key in a table and didn't realize the behavior.
0
 

Expert Comment

by:Suomenlapinkoira
ID: 8009022
Glad to help! Your terminology is slightly incorrect - there can only ever be one Primary Key for a table, but a Primary Key can consist of more than one field (known as a compound PK). Most relational DB applications use compound PKs where a table has a many-to-one relationship to another data entity (Employee in your case). For example, the compound PK of an Order Details table would usually consist of OrderID and LineNo.
0
 

Expert Comment

by:Suomenlapinkoira
ID: 8009026
Glad to help! Your terminology is slightly incorrect - there can only ever be one Primary Key for a table, but a Primary Key can consist of more than one field (known as a compound PK). Most relational DB applications use compound PKs where a table has a many-to-one relationship to another data entity (Employee in your case). For example, the compound PK of an Order Details table would usually consist of OrderID and LineNo.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question