Solved

Avoid Duplicate entries

Posted on 2013-06-21
9
485 Views
Last Modified: 2013-06-26
Hello,

I have a table with the following fields to monitor Employee leave within my organization:
ID, Employee,dateLeaveStart, DateLeaveFinish, Days, LeaveType, [attachememt]

When I enter new records (i'm using the datasheet view for now), I'd like to restrict entering new records where EMPLOYEE and (dateLeaveStart OR DateLeaveFinish) match.

The idea is that if I have already marked one employee as sick for a particular day (or range of dates), I would not be able to enter another record for the same dates.

appreciate any help you can give!
0
Comment
Question by:colin911
[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
  • 3
  • 2
9 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39266496
Create two unique indexes for the table.
The first index will include the Employee column and the dateLeaveStart column.  Mark it as unique.
The second will include the Employee column and the dateLeaveFinish column.  Mark it as uniqe.
This will prevent entries where the Employee and dateLeaveStart OR Employee and dateLeaveFinish already exist.

OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39266503
Here's what the dialog should look like.
OM Gang

Indexes
0
 

Author Comment

by:colin911
ID: 39266714
Omgang,

thank you for that.  Everything works as you described, however I was hoping to keep new entries for dates between the start and finish dates.

so if an employee was sick from june 1 to june 10, an entry could not be made for a start date of june 1, and end date of june 10, or any start or finish days between june1 and june 10.

Also, is there a way to customize the error message when this error occurs?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 28

Expert Comment

by:omgang
ID: 39266738
You can't do what you're wanting from a table datasheet view.  If you are willing to use a form datasheet, bound to the table, then you can perform validation on the records before they are updated and display custom messages to the user.
OM Gang
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39266759
<so if an employee was sick from june 1 to june 10, an entry could not be made for a start date of june 1>

so this entry from jun1 to june 10 is classified as sick leave, is that correct?
if so, add another field to the index, say the field where you enter "sick leave" or "vacation"
This field can be named "Description"
0
 

Author Comment

by:colin911
ID: 39266810
I am willing to use a form but not very familiar with them (I want to learn though)

The entry from June1 to June 10 has the example of sick leave, but it should not matter the leave type because if the leave was sick leave, it can;t ALSO be vacation leave...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39266882
<but it should not matter the leave type because if the leave was sick leave, it can;t ALSO be vacation leave... >
off course, but that was just an explanation..


if you have a third field in the index, you can be able to enter records with dates from june 1 to june 10 with a different description..
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 39266901
@colin911, I think I understand what you are wanting.  Please take a look at the attached sample database.  Open Form1 and attempt to add a record for you (or me) with a start data between the StartDate or EndDate of an existing record.  I've added a BeforeUpdate event to the form that will display a custom message.  Let me know if you have any questions or if this isn't what you're after.
OM Gang
EmpLeave.mdb
0
 

Author Closing Comment

by:colin911
ID: 39279810
thank you!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

730 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