MS Access - Leave Management

Dear All,

Im Working on an HR Software and i have a challenge. The challenge is in the Leave Management Module of the HR Software. Please, find my challenge below:

I have a Form and subform (Main & Detail form). The Main Form has Employee Name which is lookup Combobox from Employee Table, Dept, Job Class, while the Detail Form (subform) has ID, Leave Type, Days Entitled, Days Request, Outstanding. The Data source is a query

Here are my greatest headache that im facing on this project:

1. When an Employee Name is selected, it looks up Employee Job Class automatically from Employee Table. The Job Class field data are: 1, 2, 3, 4 & 5. Employee with Job Class from 1 to 2 are Non-Manager while Employee with Job Class 3, 4 to 5 are Manager. I want a situation where if Employee Job Class is =< 3 the content (RowSource) of LeaveType should be filtered to only those leaveType that are for Non-Manager while if Job Class =>3 the LeaveType should have those LeaveType that are meant for Managers

Note: The only problem is that we have Casual Leave = 5 days, Annual Leave for Managers =25day, Study Leave =5 days, etc. The only issue is when you want to issue leave for Non Manager, Annual Leave for Manager should not be part of the available leave and vice versa.

2. Before updating the Leave Data, the system should check and make sure that each employee has not been issued more days than what they are entitled to. Example, if the Annual Leave for Manager is 25 days in a year, therefore, Before the next leave is issued, the system should check that total Annual Leave For Manager i.e Sum(Days Entitled) is not more than 25 while that of Non Manager should not be more than 20 days.

Note: In the Leave Type Table, i already defined the number of Days an employee is entitled so this should serve as the maximum number of day each employee is entitled to each year.

3. The Before update event should also take care of the date in question. That is, the system should check and sum the total only for a particular year and make sure that staff has not been issued more that they are entitled to.