Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

Avoid Duplicate entries

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
colin911
Asked:
colin911
  • 4
  • 3
  • 2
1 Solution
 
omgangCommented:
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
 
omgangCommented:
Here's what the dialog should look like.
OM Gang

Indexes
0
 
colin911Author Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
omgangCommented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
colin911Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
omgangCommented:
@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
 
colin911Author Commented:
thank you!
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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