|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Employee Time Clock By Richard Rost 
6 years ago
Create a Time Clock Form. Clock In and Out using Buttons. In this Microsoft Access tutorial, I will teach you how to create a basic time clock for employees to clock in and out for work, lunch breaks, etc. They will not be able to manually enter or edit records, or go back through previous days. Austin from Portland OR (a Silver Member) asks: I want to create a simple time clock form so my users can clock in and out for the day, clock out for lunch breaks, and so on. I'd like to use buttons for clocking in and out because I don't want them to be able to edit their times manually. Also, I don't want them to be able to go back to previous days and change their info. How can I do this? MembersI'll show you how to create a Main Menu with a simple employee log on with a password. They will then be able to open the time clock form for ONLY their data. They can clock in and close the form if they want. When they go to clock out, the time clock will show the previous record they clocked in under. You will also learn how to disable the clock in button, and close the form when they clock out.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksCalculate Hours Worked: https://599cd.com/TimeSpent
IntroIn this video, I will show you how to create a simple employee time clock in Microsoft Access. We will build the necessary tables and a form to allow users to clock in and out using command buttons, preventing manual editing of time entries. You will learn how to lock fields, use the Data Entry property to restrict editing of past records, set up a combo box to select employees, and add VBA code to automatically capture the current time. This tutorial covers the basics needed to set up a functional time clock database for your employees.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am Richard Rost, instructor.In today's lesson, I'm going to show you how to build a simple time clock to clock employees in and out. Today's question comes from Austin from Portland, Oregon, a silver member. Austin said, I want to create a simple time clock form so my users can clock in and out for the day, clock out for lunch breaks, and so on. I'd like to use buttons for clocking in and out because I don't want them to be able to edit their times manually. Also, I don't want them to be able to go back to previous days and change their info. How can I do this? Well Austin, there are some form control properties you can use, such as Locked or Enabled, to lock those fields, and you can use one line of VB code to set that value with a button. I'll show you how in a second. I'll also show you how to use the Data Entry property of the form so it opens up to a blank new record and they can't go back through and edit the previous ones. Let's begin by creating an employee table to store our employee information. We'll just keep it simple. We have an employee ID. That'll be our AutoNumber and then we'll put in first name. Of course, you'd have all kinds of other information in here about your employees - their address, their phone number, all that stuff. But for training purposes, we don't need to go into all that. I'm going to save this as my employeeT. That's my employee table. Primary key will be our AutoNumber. I'll just put a few simple records in here. We've got me, we've got Sue, we've got James, and we've got Kirk. Save that, close it up. Now we're going to build a time clock table to store their actual clock in and clock out times. We're going to create table design. We're going to have a time clock ID. That's our AutoNumber. Yes, every table should have an AutoNumber. Then we'll have the employee ID, which is a number of type Long Integer that's related to the employee table. That's our foreign key. If you're not sure about what that means, go watch my relationships video. I'll put a link in the description below. Go watch that now and then come back to this one if you've never related two tables together before. Now we're going to track their time in and their time out. So, time in and that'll be a date, and their time out. Do we need to store any other calculations in here like the minutes or hours worked? No, we don't have to because we can calculate that in a query. There's no need to store that in the table. The only exception would be if you've got lots and lots of employees and tens of thousands of clock in, clock out records, and you want to make reporting and searching and stuff faster. Yes, then you could calculate it and store that value. But for most instances, you don't need to store it. You'll calculate that data in a query on the fly when you generate your reports. Let's save this as my time clock T for table. All right. If you're not sure about my naming conventions, I use T for tables. Now let's create the time clock form. Create form design. We don't need to be this big, so let's make it a little bit smaller. Bring those edges in. Something like that'll do. I'm going to give it a splash of color. Let's go with yellow for our time clock. We're going to bind this to our time clock table by setting the Record Source property. Then we're going to add some fields here. So, go to Design, Add Existing Fields. I'm going to bring in the - we don't need the time clock ID, we don't need to see the AutoNumber on this form, it's kind of irrelevant. The employee ID, we're going to make a combo box to pick the employee, so we're not going to bring that in just yet. But I am going to bring in time in and time out. So let's bring those over here and drop them on the form. Now you can close the field list. Arrange these however you want them. I'm going to slide these like this. Make the labels look a little more pretty. Like so. Time in. Time out. Just like that. Maybe make these black so they're easier to see. I cover all this basic formatting in my Access Beginner Level 1 class, by the way, if it seems like I'm glazing past stuff. Let's save this so far as my time clockF. Let's take a peek at what we got. I'm going to close it and reopen it. Looks good so far. Let's make a combo box for the employee. And again, if you've never made a relational combo box where you look up the value from a different table, then I've got a free video for that. Look down below for the link for the relational combo box. A lot of my lessons build on knowledge from other lessons. I'm not trying to push it somewhere else. I want to make sure you understand it before we continue. So if you don't know what a relational combo box is, go watch that free video and then come back to this one. So, find combo boxes up top here. We're going to make a combo box to pick the employee and save it in the time clock table. I want the combo box to get the values from another table or query. What are we looking up the values from? The employee table. Bring over both fields because we're storing the employee ID, but we're displaying the first name. That's the value of a combo box. How do you want to sort it? First name is fine. Next. That's what it's going to look like. Next. Store that value in the employee ID field in my time clock table. So I'm picking a person's name, I'm picking Rick, and that will store employee ID one in the time clock table. That's how relationships work. The label can be employee. Finish. Then we'll slide this guy right above the other ones so it looks nice and pretty. Make that black also. That's a little bit too wide, so let's go like that. And we'll slide these ones over just a little bit, like so, line everything up nice on top of each other. It looks good. Let's save it, close it, open it up. I'm on a new record, one of one. Pick an employee. Pick Rick. Time in. Now you can put a date in here just by typing, or you can use the keys on the keyboard. The shortcut keys, control semicolon, put the date in there, and then hit the space bar, and then control shift semicolon, which is basically control colon, gives you the time. But we don't want our end users being able to type those things in or edit them. So, I'm going to hit escape a few times here. Hit escape. That'll go back to blank record. I want to make little buttons over here that put the time in both of those fields. I'm going to lock these. So, let's go to design mode. Another thing I want to do is turn this little guy off here because we don't want - this is the date time picker. We don't want to see that either. Let's go in here, design view. Let's open up the properties for both of these. I'm going to turn Show Date Picker off to Never. Let's find the Locked property. Where's Locked? Right down here. Turn Locked to Yes. Now for that, I'm going to make these guys just a little bit gray. That kind of signifies to the user, hey, you can't type something in there. Those are locked. That's a bit too gray. Let's go a little bit less gray. That's good right there. Now here come our buttons to clock in and clock out with. Ready for a little tiny bit of programming? Here we go. Go to the control box and find the button tool. Technically it's called a command button. Click on that, drop it right down here. Now the wizard starts up. We're going to cancel the wizard, because we want to do this without the wizard. Now I'm going to drag this over here. First, let's go over to the property. Let's give this button a good name. Right now it's called Command5. We're going to call this clockInBTN, clock in button. The caption is going to be Clock In, just like that. It's the caption that appears on the button. Right click, Build Event. Now you might get a little window up that says what kind of builder do you want? Pick the Code Builder. I have it turned off on my system. That'll bring you to the Visual Basic for Applications, the VBA window. Now right in here, we need one line of code. It's just simply going to be timeIn = Now and then open close parentheses. That's it, one line of code. That's setting the value of the time in field equal to right now. Even though this field is locked, we can still set the value in VB code. It just locks it from the user being able to type values in. So, let's come back over here, save this, close it, open this up, and hit the Clock In button. Boom. There you go. It puts the date and time to the minute right in there, to the second actually. If I click in here and try to change it, I can't. Let's do another one. The same thing for clock out. I'm just going to copy this button. Watch this. Copy, paste. That's all I got to do. Change this to Clock Out and change the name right up here to clockOutBTN, clock out button. Right click, build event, timeOut = Now. That's all. Save it. Let's close it and reopen it. There's my first record. Click out. Boom. That's it. Of course, you have to pick a name. Close it. Now open up your time clock table and there's the record. Employee for. Let's widen these out a little bit so you can see all the data. There it is. Time for the next record. Open it up. Now you might not want to have the user have to go to a blank new record each time. So, let's open this up in Data Entry mode. Come over here, find the properties for the form, which is double click right here, where the ruler bars meet, that little square there. Go to the Data tab and find Data Entry. Change that to Yes. Close it. Now when they open it up, it goes to a blank record. They cannot go back through the other records. This is a plus. You don't want them going back through and going to last Tuesday being - let me change this a little bit so I didn't show I clocked out late or whatever. This way they're forced to go to a blank new record. If they mess up, they're going to have to go to their manager. The manager, if you have your database properly secured, can go directly into the table and make changes. I cover how to lock your database down in my Security Seminar. I'll put a link down below. You can prevent the users from seeing any of this stuff and being able to go into your code or into your tables. It involves some programming, of course. You can't properly lock down your database so the users can't play with the data in the tables. But now when the users open up the time clock, they can clock in. James, clock in. Now they're working. They're going to have to leave this form open while they're working. That's a good thing. I like leaving the time clock window open or at least minimized. What you might also want to do is throw a refresh in your button. See how this is a little pencil there? It indicates this record is still dirty. It hasn't been saved to the table yet. Let's go back into our code real quick. Click on any of these buttons, go to build event. After each one of these I want you to put in Me.Refresh. Me.Refresh simply tells the form: save the data to the table underneath but keep it open. Don't go anywhere. This ensures - save changes, yes. This ensures that when you add a record, all right, Kirk, and then I hit clock in, save the data. See how now we're back to the little arrow there? It saved that data to the table. In case something happens where the user's computer shuts down or whatever - let's say there's a problem with the machine, everything shuts down - at least this way, if Kirk comes back in now, Kirk can at least clock out properly. Then he goes to his manager and says, my computer locked up. So, the manager can at least go into the time clock table. Now, this is two records - you're separated. We can then just adjust this and then close that. You can see now the manager can now see in the table that, wait a minute, you clocked in this day, where's your clock out time? What did you do? There's all kinds of things you can do, but basically, as far as your end user is concerned, you put a button on your main menu that opens up the time clock. Then you tell them to clock in, leave that form open while you do your work, and then when you're done for the day or you want to break, clock out. That's the simplest time clock. Yeah, there's lots more you can do with this, and I will cover some more in the extended cut for the members. But this is a basic functioning time clock, and that's really all you need. Now, if you want to learn more about how to calculate the hours worked that each employee worked to create a time sheet, I do have another video. It's calculate total time spent on a job. The same thing works for employee time clocks as well. You got a time in, a time out, and I'll show you how to calculate the total minutes they worked. From there, you can figure out the total hours, minutes left, display time, all that stuff. That's a free video. It's on my website and on my YouTube channel. I also cover making a more fully functional work log in my Access Expert 11 class. I'll put links to both of these videos in the description below. Want to learn more about building a time clock? In the 20 minute extended cut, I show you lots more with this time clock database. We'll build a main menu. I'll create a simple login with a password so when they pick their name from the combo box list, before they can do anything, they have to enter a password. I'll show you a real simple way for putting in a password. This way, each user has to log in as themselves. Then we'll open up the time clock form for just that user. Notice how it's locked on the time clock form now also. As long as the user is logged in, it picks it up off of the main menu form. Then we'll set it up so they can clock in and then close the time clock form if they want to. The next time they click the time clock button, we'll open up the last record wherever they were clocked in, but not clocked out yet. We'll have the clock in button locked, as you can see it there in the picture, and then when they hit the clock out button, we'll close the time clock form. All that's covered in the extended cut video for members only. How do you become a member? Click on the Join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free. QuizQ1. What is the primary purpose of the time clock form demonstrated in the video?A. To allow users to manually enter and edit their work times B. To let users clock in and out using buttons, preventing manual edits C. To calculate payroll for employees D. To update employee addresses Q2. Which property should be used on the form fields to prevent users from typing in time values? A. Visible B. Enabled C. Locked D. Tab Stop Q3. What is the purpose of the Data Entry property being set to Yes on the time clock form? A. It allows users to delete old records B. It opens the form directly to a blank new record, preventing access to past records C. It enables sorting of records by date D. It unlocks form fields for editing Q4. What code is used in the clock in button to set the time in field? A. timeIn = Date() B. timeIn = Time() C. timeIn = CurrentTime D. timeIn = Now() Q5. Why should you not store calculated fields such as total minutes worked directly in the time clock table? A. Storing calculated fields increases data accuracy B. Calculations should be performed in queries to avoid redundant data storage C. Access tables do not support storing calculated values D. It is required for all Access databases Q6. What is the advantage of using a combo box for the employee ID on the time clock form? A. It sorts employees by address B. It allows picking an employee name and saves the corresponding employee ID C. It lets users type any value they want D. It automatically calculates hours worked Q7. What should you do after setting the time in or out value using VBA to ensure data is saved? A. Close the form immediately B. Click save in the ribbon C. Use Me.Refresh in the code D. Restart Access Q8. If a user's computer shuts down after clocking in but before clocking out, what is the suggested recovery step? A. Ignore the missing clock out time B. Let the manager manually update the time clock table C. Delete the incomplete record D. The user can edit their own time Q9. Why is it recommended to lock down your database and restrict table access for regular users? A. To improve database speed B. To prevent users from making unauthorized data changes C. To make the interface look cleaner D. To allow multiple people to share the same record Q10. In the extended cut of the lesson, what additional feature is demonstrated? A. Exporting data to Excel automatically B. Adding a password-based login system for employees C. Calculating monthly payroll within the form D. Sending email alerts for late clock outs Answers: 1-B; 2-C; 3-B; 4-D; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on how to build a simple employee time clock system in Microsoft Access using forms and VBA code. I am Richard Rost, your instructor, and in this lesson, I will guide you through the process of creating a secure and efficient method for employees to clock in and out.The idea comes from a student who wanted a form that allows users to clock in and out for the day, including lunch breaks, solely using buttons to prevent manual editing. He also requested that employees should not be able to go back and change their times for previous days. There are a few methods in Access that make this possible, such as using the Locked or Enabled properties for form controls, and applying the Data Entry property to the form so it always opens to a new record. To start, you will create an employee table. This should include an AutoNumber employee ID as the primary key and a first name field. For demonstration purposes, you only need minimal information now, but in practice, you would track details like the employee's address or phone number. Next, you will set up a time clock table. This should have its own AutoNumber primary key, a foreign key field that links to the employee table, and fields for time in and time out, both of which are Date/Time data types. Calculated values such as hours worked are not stored in the table, as these can be determined dynamically in queries. The only exception would be exceptionally large databases where stored values might help with reporting performance. With the tables created, you will then design the time clock form. Set the record source of the form to the time clock table. For the user interface, remove the need for users to see or manually input time clock IDs or employee IDs. Instead, use a combo box to pick the employee based on their name. This combo box saves the employee ID in the time clock table but displays the user's name for clarity. If you are not familiar with creating relational combo boxes, I recommend watching the related tutorial on my website. Next, add the time in and time out fields to the form. After arranging and formatting the form for readability, the important step is to restrict user edits. Set the Locked property for both time in and time out fields, which will prevent users from typing in those fields. You may also want to adjust the background color to indicate that these fields are not editable. To allow users to clock in or out, add two buttons to the form. Name one clockInBTN with the caption "Clock In" and the other clockOutBTN with the caption "Clock Out". For each, you will add a brief VBA routine that sets the time in or time out field equal to the current date and time using the Now function. Even though the fields are locked, this code can still write to them, which means users cannot alter the values by hand but can still record their times through the button. It is also helpful to include a line of code that refreshes the form after each time entry. This ensures that the data is immediately saved to the table, and the visual cues in the interface change to reflect the saved state. To further protect past records, set the Data Entry property of the form to Yes. This ensures that when users open the form, they only see a blank record and cannot navigate to previous ones to make changes. If errors do occur, such as a missed clock-out, a manager with higher security privileges can access and adjust the entries directly in the table. More comprehensive security options and database lockdowns are covered in depth in my Security Seminar. With this setup, employees can easily clock in at the beginning of the day, leave the form open or minimized while working, and clock out when finished. It is a straightforward and functional time clock system. For those interested in calculating total hours worked and generating timesheets, there are additional tutorials on my website that explain how to build queries to perform these calculations. For those who are members, today's extended cut covers several enhancements. I demonstrate how to set up a main menu, implement a simple password-based login, and ensure that the time clock form opens specifically for the logged-in user. I also show how to restrict clock-in and clock-out actions based on their current status, carry forward unsaved data, and manage session flow so that users cannot mistakenly clock in twice. If you wish to explore these advanced features, you can become a member and gain access to this and many other extended cut lessons. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListCreating the employee tableDefining primary key and basic fields in employee table Creating the time clock table Establishing foreign key relationship between tables Adding fields for time in and time out Discussion on calculated fields and performance considerations Creating the time clock form Binding the form to the time clock table Adding existing fields to the form Formatting form labels and controls Creating a relational combo box for employee selection Configuring the combo box to display employee names Locking form fields to prevent manual editing Disabling the date picker in date fields Adding command buttons for clock in and clock out Naming and labeling command buttons Writing VBA code to set time in using Now() Writing VBA code to set time out using Now() Copying and editing button properties and code Saving records with Me.Refresh in VBA Setting Data Entry property to restrict record navigation Testing the time clock workflow from the user perspective |
||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access timeclock, microsoft access time clock templates, time tracking, time tracker, employee time clock, time card, time hour tracking, time sheet, timesheet, track employee hours clock in clock out PermaLink Employee Time Clock in Microsoft Access |
