I'm totally new to access, but looking to learn it as much as possible.
Basically the reason I've taken interest in Access is because I'm trying to create a leave planner for employees that they could enter and book. I've already done this in excel and google sheets, but the issue is that with a lot of staff the spreadsheets crash on the Local Area Network.
So I'm thinking to create a SHARED database:-
1. Which any member of staff can load and book their leave.
2. Every user would have their own password to avoid anyone else altering the leave without permission.
Is the above possible and if so, is it a very difficult task? Does it involve any vb scripts?
To SHARE a database you must split it. (look this up) Once the database is built and all forms and queries are made, run the db split.
All forms and queries are on the front end. (give a copy to each user, NO SHARING)
and all the tables are on the backend. (only 1 copy exists on the server for all to use)
No need to create passwords for everyone since windows already has.
In a form , put a userID box, and password text box
when the user click Login button (or whatever you name it), it checks the windows login authentication. If they pass, then you know their login ID and can filter data to it.
In the form you must add the following code to do this in the Login button CLICK event:
select the login button, in the properties, events,put cursor in the On Click event,
click the little ellipsis button that appears at the end of the text box. This will throw you in the code section.
Paste the following code:
Code:
SUB btnLogin_Click()
Dim sUser As String, sPass As String, sDom As String
sUser = txtUser
sPass = txtPass
sDom = "CoDomain"
If WindowsLogin(sUser, sPass, sDom) Then
mbSafe = True
DoCmd.OpenForm "frmMainMenu"
DoCmd.OpenForm "frmLogin"
DoCmd.Close
Else
MsgBox "LOGIN INCORRECT", vbCritical, "Bad userid or password"
End If
end sub
'-----------------
Public Function WindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean
'-----------------
'Authenticates user and password entered with Active Directory.
On Error GoTo IncorrectPassword
Dim oADsObject, oADsNamespace As Object
Dim strADsPath As String
strADsPath = "WinNT://" & strDomain
Set oADsObject = GetObject(strADsPath)
Set oADsNamespace = GetObject("WinNT:")
Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & "\" & strUserName, strpassword, 0)
WindowsLogin = True 'ACCESS GRANTED
ExitSub:
Exit Function
IncorrectPassword:
WindowsLogin = False 'ACCESS DENIED
Resume ExitSub
End Function
Thanks Ranman256. Unfortunately I'm not sure if this is a feasible option as splitting a database and give chunks to each employee may go against their security policies and also windows is locked down by IT department, so not sure if it would pop up with username/password request and also I'm not allowed to have anyone's username/passwords.
Before you go re-inventing a wheel, do you have the ability to do Outlook to a shared .PST file on your network? Because if you do, you can make a shared calendar and allow folks to update that calendar. It depends on whether you trust folks to not dink around with that shared copy, but it becomes easy for everyone to enter various "appointments" and block out their time that way.
If you actually have an Exchange server in that mix, you can ALSO have the appointment become an automatic notification engine because you can invite everyone as an "Optional" attendee, at which point you can even arrange for their acceptance of the "Appointment" become an e-mail to you. Besides which, if you use the "Month" view of the calendar, you can see at a glance who will be present and who won't on a given day.
Obviously, if you cannot use Outlook, then the answer to your question is still YES, you can share a back-end database of leave requests. Be sure to implement it as a FE/BE case. Talk to your IT folks about making the required folder having permissions needed for Access. In essence, if you have a domain environment, create a group. Then add users of your database to the group and grant "Modify" level access to that one folder and its contents. (You would need "Read" access or at least "Passthru" access to all parents of the folder.)
As to giving a front-end to each user, just remember to do things to lock it down first. This forum has lots of suggestions on how to rein in an Access FE file to keep it from being a nightmare. Further, if you use the Group-oriented security suggestion, it is not like the database file is anything but a file anyway.
If your IT folks are NOT familiar with Access, send 'em our way. If they are worried about using Access in a relatively tight environment, show them this response: I ran an Access split database with each user having a private copy of the FE (for purposes of eliminating a lot of lock contention) in a U.S. Navy environment dealing with a bunch of sensitive data (Sensitive But Unclassified). Also called "For Official Use Only" (FOUO). We had live-system IP addresses and true server names in it and nobody balked twice.
If the U.S. Navy with all of its security requirements could allow that in a hosting center as a way to track security compliance, I think something so simple as ordinary personal leave should be fine. It's not like you are going to keep Personal Identifying Information (PII), are you?
I'm not sure about outlook, but we all do have a shared folder where we can place files/folders for others to view/edit.
Definitely not sure about Exchange Server. I appreciate and thank you for your offer of willing to address any issues to directly with our IT department, but being honest, they won't allow any changes to their systems, so I have to make best of what we have.
I've already done a leave sheet in Google sheets & converted it to Excel, but because there are more than 100 colleagues, sharing it, this causes the spreadsheet to grind to a halt and crash.
What I want is a database of staff names, team numbers and then a front end which looks similar to this:-
I'm not sure about outlook, but we all do have a shared folder where we can place files/folders for others to view/edit.
Definitely not sure about Exchange Server. I appreciate and thank you for your offer of willing to address any issues to directly with our IT department, but being honest, they won't allow any changes to their systems, so I have to make best of what we have.
I've already done a leave sheet in Google sheets & converted it to Excel, but because there are more than 100 colleagues, sharing it, this causes the spreadsheet to grind to a halt and crash.
What I want is a database of staff names, team numbers and then a front end which looks similar to this:-
I'm not sure how exactly it is meant to work?
I'd probably have a tab for each month and the workdays as you have them as column headers, with formulae not fixed values.
Not sure how you could stop someone taking out someone else's leave though?
I think that you could have something like this, but controlled by relevant dept, and each users submits their leave request. That is checked against your sheet, if OK, then add and confirm to user. If not, state what is available back to user?
Absolutely. I can see you are using COUNTA, SUM & COUNTIF at least.
It is not the formulae that would be the problem, just the implementation, and others on here would be best placed for that advice.
Just in my mind I would be thinking of
A read only planner, showing what is being used/available. There are sample calendar databases in the Sample Databases forum. Perhaps you could utilise something along those lines?
A form to submit leave request and dates (perhaps automatically emailing the HR dept to alert to the request).
Facility to add to the planner, and inform user of acceptance or rejection.
Each request could be added to a table to see who requested same dates first, in case of delay in processing first request. Possible timing issue there?
So each user would have a small front end database and all use the common back end.
I think I need to explain how our leave planner system works.
Basically we have two leave systems. First one is like a "WITHIN DEPARTMENT" version, so it's called a "LOCAL LEAVE PLANNER" and second one is like the type you mentioned that goes to HR, which is respectivaly called "HR LEAVE PLANNER"
The reason we have two is because we are all on flexible contract & hours and part of the PERKS & BENEFITS of our job is that staff can put in leave within the LOCAL LEAVE PLANNER at a short notice and take time off as they wish, but also have the option can cancel leave at a short notice. The HR LEAVE PLANNER is ONLY used if staff have ACTUALLY taken the leave, so in other words the LOCAL LEAVE PLANNER is just to help managers see how many staff they expect to have the next day or week.
I'll give you an example:-
Example 1
Mr A works on this flexible contract and wants Fri 23rd Sept off as Leave due to hospital appointment. He checks the number of available spaces and books his leave a Thur (a day before his hospital appointment. But on Fri 23rd Sept, his hospital calls at 8am to tell him his appointment has been cancelled due to unforeseen circumstances. Mr A calls works (has to be before 9am) and informs them he's coming in and once he get's there he removes the Leave booking.
Example 2
Mr A works on this flexible contract and wants Fri 23rd Sept off as Leave due to hospital appointment. He checks the number of available spaces and books his leave a Thur (a day before his hospital appointment. On Fri 23rd Sept, he attends his hospital appointment. The following monday he comes to work and since he took a day off, he now needs to put this through his HR LEAVE PLANNER.
I hope these examples make sense. This is why staff have to do their own leave and can't ask anyone else as that requires man-hours which they are not willing to spare.
So the best option would be to have a front-end that looks similar to the spreadsheet, but all the info is stored in a database shared on the Local Area Network.
Well if you store the userid of the user, you could limit them to only being able to create/amend/delete their records.
A lot of thought needs to go into how it will all work. What if in option 2 he does not submit it to the HR planner? Best to have that done by Access in some way? Perhaps start as a status L, then become status H if taken.
All in all, quite a large project I would have thought, especially for a novice, which you state you are, as am I.
I would break it down into small steps, and then assemble them together.
The hardest part is probably the structure to hold the information for it to work the way you want it.
Might even be worth googling to see if anything similar is available.
I had to create a small timesheet system recently, and is still getting tweaked and I must admit whist there were some out there, it seemed easier to create mine from scratch, the way I wanted it to work, than try and understand someone else's logic and how that worked.
Currently there are 2 separate systems, so the LOCAL LEAVE PLANNER is done on a spreadsheet and the HR LEAVE PLANNER is on a specialist website dedicated to putting your leave through, but let me be clear that neither have anything to do with each other, so you don't need to put anything in the LOCAL LEAVE PLANNER that will message or contact the HR department.
My main goal is to create a LOCAL LEAVE PLANNER which pulls staff information in a database, but has the looks similar to the one in google sheets.
So for example, staff access an interface which asks for their name or staff id and once entered, it shows them something similar to the one on google sheets. They then alter their Leave and that then updates the interface.
To be honest, the database is there just to hold their names/id's and team numbers and nothing else.
Currently there are 2 separate systems, so the LOCAL LEAVE PLANNER is done on a spreadsheet and the HR LEAVE PLANNER is on a specialist website dedicated to putting your leave through, but let me be clear that neither have anything to do with each other, so you don't need to put anything in the LOCAL LEAVE PLANNER that will message or contact the HR department.
Well you stated that if the leave was taken in local, then it went to the HR planner. Why repeat the process? I had to do that in some Excel sheets and it takes some work to avoid that. When I worked for a large bank in the UK, as a user we were putting the *same* information in 4 places. Whenever that happens, you know the structure is wrong and open to errors.
Just because it is done that way now, does not mean it still has to be done that way.
I joined a small firm back in November as an admin person, but due to my limited computer knowledge I have since changed the way they handle certain processes. Sometimes to make it more efficient and sometimes to automate. They are more than happy to do less work. That timesheet project is one such example. It took me a while to create and will be enhanced, but for now it streamlines the process that used to happen.
My main goal is to create a LOCAL LEAVE PLANNER which pulls staff information in a database, but has the looks similar to the one in google sheets.
Again, you possible have the ability to make it *better* than that whilst making it look similar. It is often mentioned on here that Excel users try and use Access in the same way, always to their detriment.
So for example, staff access an interface which asks for their name or staff id and once entered, it shows them something similar to the one on google sheets. They then alter their Leave and that then updates the interface.
Again, you possible have the ability to make it *better* than that whilst making it look similar. It is often mentioned on here that Excel users try and use Access in the same way, always to their detriment.
I think you have the opportunity to do more than that. EG. I do not know how you could stop someone deleting someone else's entries in s ahred sheet Google or EXcel. You might be able to see who did it after the event, but not stop someone do that without a lot of effort.
With Access you could let users see all the leave requested/authorised, but not be able to modify it, only their entries.
I wish you well with it, it sounds an interesting project to take on, especially not being an Access expert.
Think of the LOCAL LEAVE PLANNER as an unofficial diary. It's simply there to keep a track of how many staff are in and how many on leave. Whilst the HR LEAVE PLANNER is the official diary and is ONLY used if the person has actually already taken a day off.
The structure of a LOCAL LEAVE PLANNER is there due to the flexibility of the job contract allowing people to take off days at a short notice and cancel leave at a short notice, whilst allowing management to keep a track of staff to make sure that a certain minimum amount of staff working. This process won't be changed as that would mean taking away the special benefits & perks of staff.
You can stop people. For example the way google sheets stops the person from altering another person's entries is by using the PROTECT RANGE option and setting permissions to each individual's email.
I don't have a clue where to start, so I was really hoping someone could help me get start and support me along my journey
You can stop people. For example the way google sheets stops the person from altering another person's entries is by using the PROTECT RANGE option and setting permissions to each individual's email.
I've only had a quick look, but that appears very cumbersome when in your situation, you would have to protect each individual row in your example. What happens when someone leaves and another takes their place.? You would need to go in and change the row of the person that has left to the new name and amend the email address?
I've only had a quick look, but that appears very cumbersome when in your situation, you would have to protect each individual row in your example. What happens when someone leaves and another takes their place.? You would need to go in and change the row of the person that has left to the new name and amend the email address?
This is exactly why I want to move away from Google sheets as it would be very time-consuming to protect a range from each individual. I feel MS Access is the real answer.
I appreciate that your still learning. I'm very surprised that no one else more experienced has stepped into this debate to see if they can help me resolve this issue.
Before you go too deep off the deep end here, the technically correct way to do this is not to have a monthly table or daily columns. This is a non-normalized design that invites proliferation of tables. It would be to have a monthly DISPLAY based on a single leave table based on having date entries (or no entries) based on particular dates.
A couple of questions come to mind about your implied leave rules, like whether you could make one table do two jobs by giving different reports to anyone who is asking. Does the supervisor need to approve? Do you need to do anything special with H/R before or after the leave is actually taken? There might be some maintenance steps, but this IS doable.
To do what I might do, you will need to learn a little about VBA, but you would have to do so anyway eventually. So let's think about this possible design.
In your tables you need a user ID that goes with a user record including actual name, a department ID, other information as needed. Since it is clear that you are in a domain-based environment, you can use the Environ("Username") function as needed to determine who logged in on the machine. If your User table includes the result of that function (which will be a string based on how the IT guys assigned usernames. In our shop it was firstname.lastname.modifier (where contractors were all ".ctr" as a modifier), but any consistent scheme will do. You just store THAT name as a single string in the user record. Then you can at some point use
(That includes both apostrophe characters and double quote characters, watch out since that part of the VBA might be confusing if you are not familiar with it.)
Create a table that shows requested leave periods as UserID (LONG), DeptID (LONG), LeaveDate (DATE), maybe a SupvApprove (Y/N), definitely a TookLeave (Y/N) for one day at a time. The UserID and DeptID fields only need the ID key from the user table and from a department table.
If the supervisor has to approve, then an entry in the Department table would name the current supervisor of the department. That way you could test whether the person who is logged in has permission to approve or see someone else's information. That would also imply that the user's current department ID is in the user table. The supervisor's record would have to show a Yes/No field to say that the person IS a supervisor. You would have to talk to the person wanting this design about whether a supervisor can approve his/her own leave.
The key to this is the days-off form. Build an unbound form that will be tedious to build but you will only build the actual form once. Have a grid of check-boxes 7 per row with labels, and 10 rows. Name these as Dayn and Checkn where n is the digit strings for 0 through 69. Put a date picker at the top of the form to pick the start date for that grid. By default you pick today's date for the start of the grid and the current user for the display to build. However, you can have a combo box to list other users for the case that the current user is a supervisor.
The key is in the event code. You will need some VBA routines for this. Here are facts to understand...
1. Dates are based on a "time-line" or "Offset" method. A Date field is an OFFSET from the system reference date and it is the date FORMATTING routines, not the value in the date variable, that assigns month, day, and year in the date string displayed for a date.
2. Labels don't have values, but they DO have .Caption properties that are just as mutable as most other control properties.
3. A checkbox DOES have a value and you can test or set it at will on a form.
So... hold onto your hat for this one.
In the form's Form_Load event routine, put a default date of Now() as the value of the text box that contains your date picker, call the text box [WeekOf]. compute the weekday of today as TodayWkDay = DatePart( [WeekOf], "w" ) - and I am omitting the option arguments that would follow the "w" because the defaults work for what I am suggesting. Use that to compute the date that should start your display. That date would be DsplyStart = Now() - TodayWkDay + 1 because (I think) in the earlier DatePart call, Sundays come back as day 1 of the week and Saturdays come back as day 7.
Also look up who is using the form as described earlier, then also do a DLookup to get the person's Department ID knowing the UserId from the UserTable.
Now write a loop in VBA for I = 0 to 69 in which inside the loop, do a TestDate = DateAdd( "d", I, DsplyStart) to compute the day that goes with index I. For each step of that loop, look up ReqCount = DCount( "[UserID]", "LeaveTable", "[LeaveDate]=#" & FormatDateTime( vbShortDate, TestDate ) & "# AND [DeptID] = " & Cstr(DeptID) )
Now you will do a few more things on that display. Remember the names I suggested for the fields on the form?
Code:
BoxName = "Check" & Cstr(I)
LabelName = "Day" & Cstr(I)
GridDate = Format( TestDate, "mm/dd" )
Me!Controls(LabelName).Caption = GridDate
If ReqCount > 0 Then
Me!Controls(Boxname) = True
Me!Controls(Labelname).BackColor = vbYellow
Else
Me!Controls(Boxname) = False
Me!Controls(Labelname).BackColor = vbWhite (or you could pick vbGreen, your choice)
End If
Before I do much more, you have to decide whether this approach is totally beyond you at your current level. This much lets you see how to think about the problem using Access methodology rather than Excel methodology. In Excel, you can build a grid and put what you want in it. For Access, you want to think about normalized tables. A table of months or days is still Excel thinking.
If it is an approach you want tot ry, then here are a couple of other thoughts.
If you implement a form as suggested, make the form painter a SUBROUTINE in the Form's class module because you will call that at least twice: Once on Form_Load and once after someone picks a new date.
You would have to decide on whether the act of checking a box automatically reserves the date or whether you wait until someone clicks a [Save] button.
In the case of "checking the box saves the date" you would have Boxn event routines to do a DoCmd.RunSQL for an "INSERT INTO" to add the current date for this user to the leave table if the box is now checked, or to do a "DELETE" to remove the current date for this user from the leave table. (Note that if the box is checked for another person, doing a DELETE that is specific to this person will not remove anything.)
In the case of "wait for a [Save] button" then you have ANOTHER loop that goes through to test the boxes to see if the person actually reserved that date and generate the INSERT INTO or DELETE calls in the loop.
In either case, you decide on the rules to be applied if the person tries to check a box that was already checked by another person. You would implement THAT in any of several ways, but the simplest might be that in the subroutine that paints the box, do one more DLookup for the UserID from the leave table showing the person and department for which that check applies, and in the case that the current user and the user requesting the same date were in the same department but not the same user, lock the check box so that it cannot be unchecked.
I know this is complex and you would have to think about it for a while. But here we DO try to tell you the Access way of doing things, not the Excel way - unless you are in the Excel threads, of course.
Oddly enough, my manager asked me today about a holiday planner. She wants to get away from the paper based version.
For now I will use a spreadsheet as there are only 7 employees in my team. It might be presented to the other teams to see if they want to use it (at least until the end of the year). In the meantime I will think on how to do it and watch this thread closely.
