Restrict to Generate Leave Application if Not within schedule!

[cap.zadi]

Hi



I need help to know the way to manage this scenario!

I have Tables:

"TblEmpLeaveSchedule";
SchID (PK)
EmpId (FK)
EmpName
SStartDate
SEndDate
Etc

Note: Prepared for next year.

"TblEmpLeaveApp";
AppID (PK)
APPDate
EmpId (FK)
EmpName
VStartDate
VEndDate
Etc


Requirement:

a. The system shall not be able to generate the application for the Employee who schedule is not away more than 30 days. More over system can recommend to issue the leave applications who are just 30 days away from schedule start date?

b. The system shall not allow to issue duplicate applications during the same year?

Kindly advise the needful.

[Homegrownandy]

in the where condition for employee schedule you could put <date() +30

Its hard to understand what you're after. perhaps you could attach a sample copy of your DB?

[ranman256]

CONDITIONS:

1. deny if Emp has had any for the year
2. DENY if emp app Elapse < 30 days
3. ok if emp app date < 30 days STARTDATE


make a query,qsEmpLeaveYr, to get Emps and their leave year, :
select EmpID, Year(sStartDate) as Yr from TblEmpLeaveSchedule


On the entry form, the user enters leave data then
validate the App request by clicking button:

sub btnAppy_click()
dim vHasThisYr, vElapse, vDaysAway

    'check all conditions above from the entry form:

vHasThisYr = Dlookup("[EmpId]","qsEmpLeaveYr","[EmpId]=" & txtID & " and [Yr]=" & Year(txtStartDate))

if IsNull( vHasThisYr ) then
   'ok for app this year ,rule 1
   'now check elapsed leave time    
 
   vElapse = DateDiff("d",txtStartDate, txtEndDate)
   if vElapse < 30 then    'rule 3, ok if leave <30
       msgbox "Leave is too short"
   else
       vDaysAway= DateDiff("d",date(), txtStartDate)
       if vDaysAway <30 then
            msgbox "Leave request is too close to leave"
       else
           docmd.openquery "qaAddRqst2Schedule"
       endif
   endif
ELSE
   msgbox "Applicant aleady took leave this year"
endif

end sub

[cap.zadi]

Hi

Its been long time.

The requirements:

a. Shall restrict duplicate entries for Annual Vacations schedule.
*After update of Combo Box (Fed by TblEmp) by selecting the employee, system should give alert if this man is already in schedule for given year schedule.

b. When to initiate a vacation leave app, then again while selecting the emp from the cbo system should not allow if this man is not in schedule of this year.