• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

Prevent duplicate entries

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
WishIKnew
Asked:
WishIKnew
  • 4
  • 2
  • 2
  • +1
1 Solution
 
nico5038Commented:
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
 
rquagliaCommented:
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
 
SuomenlapinkoiraCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
WishIKnewAuthor Commented:
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
 
nico5038Commented:
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
 
SuomenlapinkoiraCommented:
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
 
WishIKnewAuthor Commented:
Thanks for the help. I have never defined more than one primary key in a table and didn't realize the behavior.
0
 
SuomenlapinkoiraCommented:
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
 
SuomenlapinkoiraCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now