if Data exist for an Emp. ID who already submitted same type leave between two dates

[mukeshpiplai]

I have a table named [tabLeave] in ms access with following fields:-
1. eeID = Number
2. Emp_LeaveType = Text
3. Leave_From = Date [dd-mmm-yy]
4. Leave_Upto = Date [dd-mmm-yy]
5. LeaveEntryType = Text

I also have unbound form named [frmLeave] with following fields where from I submit data in this table.

1. eID = Number
2. L_Type = Text
3. Start_Date = Date [dd-mmm-yy]
4. End_Date = Date [dd-mmm-yy]
5. EntryType = Text

So my question is that what would be vba code (after update) with Start_Date and End_Date after filling up eID and L_Type with same EntryType so that A message box could appear to prevent duplicate data with same leave period if leave period already exist in tabLeave with all 5 fields match.

Thanks in advance.

[Seth Schrock]

You will actually have to perform two tests, one for Start_Date and one for End_Date. I would probably just do two DCount() functions. So something like this (air code):

Dim strStartCriteria As String
Dim strEndCriteria As String
 
strStartCriteria = "eeID = " & Me.eID & " And Emp_LeaveType = '" & Me.L_Type & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#"
strEndCriteria = "eeID = " & Me.eID & " And Emp_LeaveType = '" & Me.L_Type & _
                 "' And Leave_From <= #" & Me.End_Date & "# And Leave_Upto > #" & Me.End_Date & "#"
 
If DCount("*", "tabLeave", strStartCriteria) > 0 Or DCount("*", "tabLeave", strEndCriteria) > 0 Then
    'Duplicated leave
End If

[jforbes]

I would create a Function that tests for the overlap and put it in the BeforeUpdate Event of the Form, having it Cancel the Save if there is an overlap.

I think you can make the function out of a few DCounts. I would looks something like the following. This is air code, you'll have to take ownership of it:

Public Function testOverlap() As Boolean
 
    Dim dLeave_From As Date
    Dim dLeave_Upto As Date
    Dim leeID As Long
 
    If IsDate(Me!Leave_From) And IsDate(Me!Leave_Upto) Then
        leeID = Me!eeID
        dLeave_From = Me!Leave_From
        dLeave_Upto = Me!Leave_Upto
 
        ' Default Test to Passing, then look failure points
        testOverlap = True
        If testOverlap Then If DCount("eeID", "tabLeave", "Leave_From<#" & dLeave_From & "# AND dLeave_Upto>#" & dLeave_From & "#") Then testOverlap = False
        If testOverlap Then If DCount("eeID", "tabLeave", "Leave_From<#" & Leave_Upto & "# AND dLeave_Upto>#" & Leave_Upto & "#") Then testOverlap = False
        '...
        ' Any other testing you want to perform
        '...
    Else
        testOverlap = False
    End If
 
End Function

HAHA, Crossposted with Seth, good times, good times.

[mukeshpiplai]

Thanks to Seth and jforbes for reply

you both not added LeaveEntryType in your formula..may be i Forgot it to mention.
LeaveEntryType is also a text box in table and form which describes about Leave Entry is monthly, PUC, Adjustment etc.

So I tried following code of Seth with above field amendment but It not worked. If I tried formula of "Seth" without LeaveEntryType then it works fine for me. So kindly solve my issue with adding above field..

Here's my code.

'My other coding
 
Else
strStartCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & " And LeaveEntryType = '" & Me.LeaveEntryType & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#"
strEndCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & " And LeaveEntryType = '" & Me.LeaveEntryType & _
                 "' And Leave_From <= #" & Me.End_Date & "# And Leave_Upto > #" & Me.End_Date & "#"
 
    If DCount("*", "tabLeave", strStartCriteria) > 0 Or DCount("*", "tabLeave", strEndCriteria) > 0 Then
        MsgBox " Duplicate Leave"
        Me.End_Date = ""
        Me.End_Date.SetFocus
    Else
        Me!NoDays.Value = Nz((End_Date - Start_Date), 0) + 1
        Me!Leav_Close.Value = Nz(([Leav_Open] - [NoDays]), 0)
    End If
End If

[Seth Schrock]

Sorry, I guess I read entry type and confused that with the leave type. Here would be the criteria then.

strStartCriteria = "eeID = " & Me.eID & " And Emp_LeaveType = '" & Me.L_Type & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#" & _
                   " And LeaveEntryType = '" & Me.EntryType & "'"
strEndCriteria = "eeID = " & Me.eID & " And Emp_LeaveType = '" & Me.L_Type & _
                 "' And Leave_From <= #" & Me.End_Date & "# And Leave_Upto > #" & Me.End_Date & "#" & _
                 " And LeaveEntryType = '" & Me.EntryType & "'"

[mukeshpiplai]

Sorry to post... I tried after correct in above code and its works fine... but there a problem.

Problem is that an employee already applied a leave type for 01-Jan-16 to 05-Jan-16.. now I goes to apply for leave from 01-Jan-16 to any further date then code prevent it. but it not prevent if I applied 02-Jan-16 to any future date Or 30-Dec-15 to any future date. This code works only if My start date is same with existing database date for above criteria.

So need some guidelines in this regard.


Thanks again.

[mukeshpiplai]

My full code is following:-

Private Sub End_Date_AfterUpdate()
 
On Error GoTo ErrorHandler
 
Dim strStartCriteria As String
Dim strEndCriteria As String
Dim HPLBal As String
Dim StartDt As Date
Dim EndDt As Date
 
If IsNull(L_Type) Then
MsgBox " Enter Leave Type"
End_Date = ""
Exit Sub
 
ElseIf IsNull(Start_Date) Then
MsgBox " Enter Leave Starting Date"
End_Date = ""
Exit Sub
 
ElseIf End_Date < Start_Date Then
MsgBox " Leave End Date can't be Less then Leave starting Date...Kindly check."
End_Date = ""
Exit Sub
 
End If
 
 
NoDays = Nz((End_Date - Start_Date), 0) + 1
Leav_Close = Nz(([Leav_Open] - [NoDays]), 0)
 
If Leav_Open < NoDays Or Leav_Close < 0 Or Leav_Close < -1 Then
MsgBox " No sufficient Leave Balance...Kindly Check Or Update Leave Balance"
End_Date = ""
NoDays = ""
Leav_Close = ""
Exit Sub
 
 
 
Else
strStartCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#" & _
                   " And LeaveEntryType = '" & Me.LeaveEntryType & "'"
strEndCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & _
                 "' And Leave_From <= #" & Me.End_Date & "# And Leave_Upto > #" & Me.End_Date & "#" & _
                 " And LeaveEntryType = '" & Me.LeaveEntryType & "'"
 
    If DCount("*", "tabLeave", strStartCriteria) > 0 Or DCount("*", "tabLeave", strEndCriteria) > 0 Then
        MsgBox " This Leave period overlaped with existing Leave in database for this employee with this type of Leave. Kindly check previous record of this employee."
        Me.End_Date = ""
        Me.Start_Date = ""
        Me.NoDays = ""
        Me.Leav_Close = ""
        Me.End_Date.SetFocus
    Else
        Me!NoDays.Value = Nz((End_Date - Start_Date), 0) + 1
        Me!Leav_Close.Value = Nz(([Leav_Open] - [NoDays]), 0)
    End If
End If
 
If L_Type = "Commuted Leave" Then
 
HPLBal = Nz((TempVars!HPL.Value), 0)
Me!NoDays.Value = Nz((End_Date - Start_Date), 0) + 1
Me!Leav_Close.Value = Nz((HPLBal - ([NoDays] * 2)), 0)
MsgBox "Kindly read " & Leav_Close & " Half Pay Leave Balance instead of " & Leav_Close & " Commuted Leave after this leave period."
 
End If
 
ExitSub:
    Exit Sub
ErrorHandler:
    Resume ExitSub
 
 
End Sub

[Seth Schrock]

Please place the following lines of code between lines 47 and 49:

Debug.Print "SCr: " & strStartCriteria
Debug.Print "ECr: " & strEndCriteria
Debug.Print "SCo: " & DCount("*", "tabLeave", strStartCriteria)
Debug.Print "ECo: " & DCount("*", "tabLeave", strEndCriteria)

This will put some output in your Immediate window of the code editor (press Ctrl + G if not visible). Please post the results back to this thread so that we can see what is going on.

Also, please verify that the EmployeeID, Entry Type and Leave type are all matching the existing leave record.

[mukeshpiplai]

There is following result if Start Date is 30-Dec-15 and End Date 02-Jan-16... There's already leave with same details with Leave Period 01-Jan-16 to 05-Jan-16...so now code should prevent duplicate value. but it not works with Start Date 30-Dec-15 and End Date 02-Jan-16... It works good with Start Date 01-Jan-16 and End Date 02-Jan-16....or any future date...


Immediate window result is:

SCr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And Leave_From <= #30-12-2015# And Leave_Upto > #30-12-2015# And LeaveEntryType = 'PUC'
ECr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And Leave_From <= #03-01-2016# And Leave_Upto > #03-01-2016# And LeaveEntryType = 'PUC'
SCo: 0
ECo: 0

[Seth Schrock]

I believe that this should fix the problem. Change strStartCriteria to the following:

strStartCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & _
                   "' And Leave_From <= #" & Me.Start_Date & "# And Leave_Upto > #" & Me.Start_Date & "#" & _
                   " And LeaveEntryType = '" & Me.LeaveEntryType & "'"
                   " And (#" & Me.Start_Date & "# Between Leave_From And Leave_Upto" & _
                   " Or Leave_From Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#" & _
                   " Or Leave_Upto Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#" & _
                   " Or #" & Me.End_Date & "# Between Leave_From And Leave_Upto)"

Now on line 49 of your post, remove the second DCount() test. I combined them into one so it should work that way.

[mukeshpiplai]

Not working respected seth-
Now immediate window reply is following:-

SCr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And Leave_From <= #30-12-2015# And Leave_Upto > #30-12-2015# And LeaveEntryType = 'PUC' And (#30-12-2015# Between Leave_From And Leave_Upto Or Leave_From Between #30-12-2015# And #02-01-2016# Or Leave_Upto Between #30-12-2015# And #02-01-2016# Or #02-01-2016# Between Leave_From And Leave_Upto)
SCo: 0
 

[mukeshpiplai]

Here's problem-

SCr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And LeaveEntryType = 'PUC' And (#01-01-2016# Between Leave_From And Leave_Upto Or Leave_From Between #01-01-2016# And #02-01-2016# Or Leave_Upto Between #01-01-2016# And #02-01-2016# Or #02-01-2016# Between Leave_From And Leave_Upto)
SCo: 1
SCr: eeID = 112233 And Emp_LeaveType = 'Earned Leave' And LeaveEntryType = 'PUC' And (#02-01-2016# Between Leave_From And Leave_Upto Or Leave_From Between #02-01-2016# And #03-01-2016# Or Leave_Upto Between #02-01-2016# And #03-01-2016# Or #03-01-2016# Between Leave_From And Leave_Upto)
SCo: 0
 

Here code gives result in 1 if start date is 01-01-2016 but result 0 if start date is 02-01-2016. Leave period in database is from 01-01-2016 to 05-01-2016 then second date [02-01-2016] should give result in 1 also . But there's some problem which I am not understanding....

[Seth Schrock]

Try this.

strStartCriteria = "eeID = " & Me.LeID & " And Emp_LeaveType = '" & Me.L_Type & _
                   " And LeaveEntryType = '" & Me.LeaveEntryType & "'"
                   " And (#" & Me.Start_Date & "# Between Leave_From And Leave_Upto" & _
                   " Or Leave_From Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#" & _
                   " Or Leave_Upto Between #" & Me.Start_Date & "# And #" & Me.End_Date & "#" & _
                   " Or #" & Me.End_Date & "# Between Leave_From And Leave_Upto)"

I forgot to remove one of the lines of code.

[mukeshpiplai]

Thanks a lot Seth Schrok.... Code worked fine...

Thanks a lot

[Seth Schrock]

Good! I was starting to pull my hair out :) Good luck on your project.

You might want to reset the best answer for any others who might be having the same problem.

[mukeshpiplai]

Hello Seth....


I have another question regarding my project.


I want to create a query to export data in excel, word or pdf format from existing tables using FORM.

Form should have two list box.. First List box on form have all fields name exists in database.. So user can choose required fields from list box 1 and selected fields should appear in listbox 2 on sameform. Now create button should export selected fields in desired format. I think there will be a combo box with Value :Excel; Word; PDF.. when user choose value i.e. excel from combo box after selected fields in listbox 2... button create/export data in excel with selected fields.

[Seth Schrock]

Per site rules, only one question per thread. Please post your question in a new thread and we will answer it there.