Employee Leave Requests with Access and/or SharePoint

[HydraofManyHats]

I'm coming here for help because in my frantic Google searching for a solution, I ran into a calendar built in Access that almost does what I needed. It was posted a long time ago here:

MS Access Calendar Open Form Day Block

The issue I ran into is that it was designed for one-day events where a leave request is likely to be multiple days. I'd like to save the supervisors/managers from having to enter multiple records for a multi-day leave, but I'd still like it to display the name of the person out on each day between the start and end date.

I have minimal experience with code - just enough to understand what is happening when I read it and make minor changes to existing code - but this kind of alteration is well beyond what I can do myself.

Access might not be the best tool for what I'm trying to do, so I'm definitely open to suggestions.

Here's what I have to work with:

• Windows 10
• SharePoint (Office365)
• Also Office 365 - general Office Suite (just not Access)
• Access 2016 (not 365) but needs to be compatible with 2010
• The button sync a SharePoint list to Access is disabled. I haven't tested the other direction to use SharePoint as the data source for Access. At this point, I don't know if I care if the public calendar and the private absenteeism tracking is actually linked.
• No budget
• No tech support
• No plan to technically maintain this if I get hit by a bus or win the lottery.
• Users who are not tech savvy and will accidentally break anything that is not locked or nailed down.

Here's what they want the solution to do:

• Employees need to be able to enter leave requests. They should only be able to view their own requests (not the rest of the team).
• Requests need to be approved by a Supervisor.
• Supervisors need to be able to view all requests, set an approval status and have the option to make notes.
• Eventually, we need to be able to report on the number of absences for each employee based on the approval status. We also have the factor of "absence occurrences" (consecutive days off are 1 occurrence from the disciplinary/policy standpoint).
• Supervisors also wanted a place to track coaching with employees, so I was trying to combine the absence tracking and coaching tracking in one place. Not necessary, but easier to deal with.
• I've also had requests for a publicly visible calendar to show the employees which days are still available for leave (or blacked out).

I have exhausted by Google-fu skills. I have a bunch of half-pieces of things that I've pulled from templates and other projects, but nothing that will work smoothly together.

Any ideas?

[twinnyfo]

HydraofManyHats,

First, welcome to Bytes!

Sooooooooo.........

I can see you have a lot to do. What it sounds like you are asking for (and I don't think you are asking for this) is a ready-made solution that will suit your needs. I wish I had one. Unfortunately, the folks on this forum typically, don't (won't/can't) create an entire project like the one you are looking for. We are glad to assist with helping you troubleshoot problematic code or address design/structure issues; we can't develop projects for others in our spare time.

But, here are a few thoughts:

I know it is possible for Access to use SharePoint lists for data sources. I, personally, have not had much success with them, simply because of the inherent limitations with such a configuration. However, there are others in my office who do use SP Lists with relative success.

Speaking of SharePoint, SP does have its own calendaring functions. Your long pole in that tent is that if someone puts something on the calendar, anyone can see it (not just the supervisor who needs to approve it). If you require the user to set permissions to each item they add, then you run into the problem of someone 1) deleting all permissions, 2) assigning incorrect permissions or 3) if the permissions are set correctly, then only the person and the supervisor can see the item, which defeats the purpose of a publicly viewable calendar displaying free dates.

I am certain that MS Access could be used as a Calendar, which would display several weeks at a time (similar to ADezii's solution) which would allow users to submit Leave Requests, have supervisors approve them and then, once approved, these requests would be displayed on the Calendar (with or without identification of the person taking leave). However, that is a major project, which would require significant amounts of time and detailed communication back and forth to develop this solution.

I am hoping that one of the other Users on this forum has a ready-made solution, because the concept sounds very useful, from a management perspective.

A possible starting point would be to develop a database which is user-specific, so that whoever logs in has specific rights and privileges. Then, build the calendar around that concept. I can probably help with that initial aspect.

Please know that I am not "blowing you off". This sounds like a very interesting project. I just wish I had time to develop such a monster at this time.

[HydraofManyHats]

Thanks so much for the suggestions. And yes, it was a good assumption that I really needed suggestions and didn't expect anyone to actually "build" this monstrosity. This project is a fun thing to do in the spare time created by waiting for managers to answer my emails.

I added a login to the Access database with different user levels, but I haven't updated the code yet to vary the security access. It's just the supervisors/managers who will be accessing it, so I'm just working on a navigation page. If I leave it open, they might get confused about which items to click on to get where they need to go.

I think I'm going to have to take a step back and put this together in a simpler way - with more manual work required on the part of the users. Right now they're using email and a paper calendar with the leave written in pencil, so just getting it into a format where more than one person can see it will be an improvement.

Thanks again!

[NeoPa]

Many developers seem to want to create security systems within their projects using tables and forms and a whole structure of who can do what. This is almost always far more complicated than needed when you consider you already have the security ID of whoever has logged on. Not only is it more complex, it's also much less secure.

Consider using the information already available to you. That way you don't need to store passwords at all; you don't need to risk your system being easily hacked and important users' security information getting out into the wild. This latter can easily turn into a need to look for a new job. Quite a serious issue for something you've tried to do to help.

The Function to Return UserName (NT Login) of Current User can help you build a far safer system without the need ever to prompt the user to worry about passwords etc.