Attendance 5
By Richard Rost
6 months ago
Tracking Attendance for Students and Employees Part 5 In this Microsoft Access tutorial, I will show you how to sort and group your attendance report by student name and class date, and then add filtering to your attendance form using VBA so you can easily view records for a specific day. You will also learn how to use VBA to add students for a given date directly from the form and refresh the attendance records automatically. This is part 5. MembersIn the extended cut, we will add code so that first, the button checks if you already have attendance data for the selected date and confirms if you want to add students again, and second, checks if you missed taking attendance for any previous days so it can notify you to catch up. I will show you how to prompt users before duplicating records and how to flag dates with missing attendance, including options to skip weekends and customize the date range checked. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, attendance report sorting, group and sort reports, report design view, sort by student name, sort by class date time, group by customer id, filter property, filteron property, VBA filter attendance, openquery VBA, doCmd.openquery, me.requery, attendance append query, add students button, attendance default date, attendance filter by date, attendance append VBA, requery attendance form, check attendance duplicate, attendance missed dates check, skip weekends attendance, attendance developer tips, barcode reader for attendance, leave of absence attendance, multiple classes attendance
Subscribe to Attendance 5
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. Today is part five of my attendance series. If you have not watched parts one through four yet, what are you doing here? Go watch those and then come back.
All right, so our attendance report is looking good, but one little problem remains: our sorting. These are not sorted in any particular order. They are actually probably sorted by customer ID, but it could be anything.
These are not sorted. You want the dates sorted inside each group, and the thing with reports is reports are going to do whatever they want to do. You could put a sort in your query and reports are just going to ignore it, so you have to set up your own sorting inside the report itself.
Now, right now we only have the customer ID header. We do not have any sorting and grouping in here aside from that customer header.
So, let's add a sort for student name because I want to sort by student name. Let's go to Report Design and then Group and Sort, and then we are going to add a sort. We are going to sort by the student name.
All right, save it, print preview.
Wait a minute. It is still not sorted. Why is that? Well, okay, let's take a look and see here. It is going to be group on, oh, and then sort by student. Oh, okay. We want the sort by student name to be above the group by customer ID. So, come over here where this little arrow is and click up. That promotes that so it is above the customer ID. So, the first thing it is going to do is sort based on the student name.
Save that and now let's print preview it. Okay, that is much better. Now it is sorting by first name, but that is okay. That is all I have is a student name by first name and I think for most class situations you kind of want it sorted by first name. Is Jimmy here? Is Alex here?
Now William and Wesley are at the bottom and James is up top. Perfect.
Now, what about those dates inside here? Let's see, I want those sorted normally by the date, with the lowest ones on top.
All right, design view. Now let's add a sort. We are going to sort by class date time.
Save it and print preview. That looks good. We can get away with that just fine because that sort is after the customer ID, so it will sort those just fine. You could reverse those, by the way. You could have the date appear first and then have all your students inside of it. In fact, in my Access, I think it is Access Expert 15 class, I show you how to do both. We do both reports. One will be by student, the other one will be by date, so you can look at each date and see who is absent and stuff.
In fact, while I have got your attention, before we get on to some more cool stuff, I just want to point out in my Access Expert Level 15 class, I spent a lot more time working on this attendance system. Some of you have said in the comments it would be nice to have multiple classes. Well, this one does. We set up multiple classes, you can have students enrolled in each class, their enrollment date, whether they paid for it or not if you are charging for classes like I used to do.
Very similar on the setup: you pick the class date, you add the students. We will check attendance. We have all kinds of different options in here for how you want to display the attendance, whether it is by student, whether it is by date.
Again, this is an expert level class, so all of this is done with no programming. There is no VBA required. If you want a more robust attendance system, check this out. I will put a link down below.
Advertisement over.
All right, now we have got just about as far today as we are going to go without VBA. So now it is time. I am going to get rid of this little guy there and I am going to paste that one in. There we go, make it a little bit smaller. Let's see if it slides over there. Yes, this is PowerPoint.
Now we are going to get into a little bit of developer stuff. If you are not a developer yet, do not worry. Do not be scared.
Go watch this video first. It is Intro to VBA. It is about 20 minutes long. It will teach you everything you need to know to get started programming in VBA.
We had this conversation in my forums on the website this morning. You are not going to learn all of VBA in 20 minutes. That is why I say it will teach you everything you need to know to get started. In other words, I do not want you to be afraid of VBA. It is not hard. It is not rocket surgery or brain science. It is something that you can become very good at very quickly, but it will take you a lifetime to master.
I have been doing this and programming in VBA for over 30 years and I still do not know everything there is to know about it. But just learning a little bit of VBA will make your databases a lot more powerful, as I am going to show you right now.
For example, when we click on our attendance form button, it just brings up the form and shows you all the records. If you have a whole semester of data in here, you have to scroll down. There is lots of stuff. So, would not it be nice if this just defaulted to opening it to this date? If you want to see all the rest of these you can just turn the filter off. That would be pretty cool.
So check this out. Let's go to design view now. I already showed you how to make this button in part two using VBA. I gave you a little sneak peek. How do you say it, sneak peek? I was going to say sneak preview.
But what we could do here is open it up and then say
Forms!AttendanceF.Filter =
And I want to see everything from, assuming this is a day, I want to see everything from this day at midnight to the next day at midnight, but not including that midnight. So the filter is going to be, what is the name of the field? It is class date time, is greater than or equal to.
Now I want to put today's, or the attendance date field, but it has to be inside of those guys. It has to be inside pound symbols because dates have to be represented inside pound symbols.
We are going to put AttendanceDate in there. Spelling is super important in VBA. I see it all the time. People post problems in the forums or questions and it turns out to be a spelling error. I am guilty too. I am horrible at spelling sometimes.
Now we are going to continue the line down to the next line here. We are going to say: and class date time is less than AttendanceDate plus one.
Now what is this string going to end up looking like? It is going to look like this. It is going to say class date time is greater than or equal to whatever that date is, 2025 06 01, right? And class date time is less than 2025 06 02. When this string gets put together after all the concatenation is done, that is what it is going to look like.
Dates have to be between pound signs. You have your greater than or equal to and your less than.
Now we have set the filter property, so now we just have to turn the filter on. So it is going to be Forms!AttendanceF.FilterOn = True.
I have a whole separate video on doing this. In fact, I have a whole bunch of videos on filtering and using the FilterOn property. I will put a link down below.
All right, save it, debug compile once in a while, come back out here. We are going to close it, open it, click the button, and look at that. It filters it just for this day's data and if you want to see everybody just turn the filter on.
Or, if you really wanted to, you could specify the start and end date down here and then just use this data to open up that form between those two dates. That is certainly another way. There are a million ways you can do this stuff, folks. I am just showing you all the different Lego pieces - you put them together however you want.
Now, earlier I said if you open this up and you hit Add Students for Date, then you have to close this form and reopen it. Well, would not it be nice if instead of this button being out here, we put it in the form itself and it would do the adding the records and then just refresh the records? That would be pretty cool.
So I am going to take this button.
Actually, let's take a look at what is in here. This button has an embedded macro because we used the command button to create it. All it is doing is opening up this query, AttendanceAppendQ. Well, we can do that same thing with some VBA, so I am going to delete that button. Goodbye.
We can slide all this stuff up if you want to. Let me get our box a little bit bigger. Now close this, save changes, yes. Now we will put that button in here.
I am going to grab a button, drop it down here, cancel the wizard, and then we are going to go with Add Students and it will add it for whatever date is on that main menu because that is what our query is programmed to do.
Now how do we do that? Right-click, Build Event. Oh, Command64. I forgot to name my button. That is one of my pet peeves with buttons. It does not give you an option to name the button.
Open it up. Even though we canceled the wizard, the wizard still does not ask you to name the button. We are going to call this AddStudentsBtn.
We have to run this AttendanceAppendQ. Right-click, Build Event. It is going to be DoCmd.OpenQuery "AttendanceAppendQ". Now after we append the records, we have to requery the form so it reloads the records. It is going to be just Me.Requery. Me means the active object, in this case, the active form that you are on.
Save it, debug compile once in a while, close it, close it. Let's go back to the main menu.
Let's move ahead to tomorrow. Open the attendance form. Oh, there is nobody in here for tomorrow. That is the new default record. Let's add students. Click. Boom. Is that not nice?
Now you just go click click click click click - all done.
Open your attendance form again, turn off the filter. There is everybody.
That is pretty neat.
Now, what if you do this and you hit Add Students again and you already have students on that date? You are going to add a second set of them. Right-click. Now you have everybody twice.
So, it would be nice if you could program something in that looked at the data and said, hey, you already got students on this date. Are you sure you want to add them again? That would be nice to do.
Now, we are going to cover that in the extended cut for the members. We are going to do two things. We are going to have it so that first, it checks to see if you have already got attendance data on that date and if so, it is going to ask you, are you sure you want to add them? Because you might want to add them again if you added a couple new students, for example. Not a problem. Yes or no.
We are also going to have it check to see if you forgot to take attendance. Let's say it is Monday and you forgot to do attendance on Friday. When you open the database it is going to look at the data and say, hey, wait a minute, you forgot to do it. You forgot to check on Friday. You are going to get in trouble. You are missing stuff. Go do it now before you forget.
I will have it so you can loop back as far as you want. If you want to go two weeks back or a month back, that is fine.We'll skip Saturdays and Sundays. You can add other dates if you want to, but that will be covered in the extended cut for the members. Silver members and up get access to all my extended cut videos.
Now wait, I'm not done. I know a lot of you leave after you see the extended cut mentioned. I'm not done yet.
I have gotten, since I started this last week with part one, a lot of feedback from people who want to see other things. So if you have features you'd like to see added, let me know. If enough people are interested in a particular feature, I'll consider making another TechHelp video, or I might make a template or a seminar video out of it.
I know this could be a whole full-fledged system. Again, in the TechHelp videos, I just show you how to get started. This is not meant to be something that you're going to run your school on, but if you're an Access developer and you're looking to learn how to do this stuff, this will get you up and running.
A few comments I've gotten so far: people want to see how to use a barcode reader to scan your students in. That's one option. Someone else asked about doing a leave of absence. A few people have asked about how to do multiple classes, which I do already cover in Access Expert 15 like I mentioned earlier.
If enough people are interested, I'll keep working on the series for you. If not, I've got other options available, so check those out.
That's going to do it for today. That's going to be your TechHelp video. Thanks for watching part five. I hope you learned something. Live long and prosper, my friends.
I'll see you next time, and members, I'll see you in the extended cut.
TOPICS:
Sorting groups in an Access report
Adding sorting by student name in a report
Adjusting sort order priority in report grouping
Sorting report data by class date
Setting report sorting and grouping order
Applying filters to Access forms using VBA
Creating a date filter for Access attendance forms
Using VBA to set and activate form filters
Adding command buttons using VBA
Naming command buttons in Access forms
Running append queries with VBA code
Refreshing form records with Me.Requery
Handling potential duplicate appended records
COMMERCIAL:
In today's video, we're continuing with part five of the attendance tracking series. You'll learn how to properly sort and group your attendance reports by student name and class date within Access, and why setting up sorting in your queries is not enough when working with reports. I will show you how to fine-tune your sorting and grouping directly inside your report, how to apply VBA code to automatically filter attendance records by date, and how to requery your forms after appending new students. Plus, I'll show you how to automate the process of adding students for a selected date right inside your forms, and explain some tips for avoiding duplicate entries. In the extended cut for members, we will also cover checking for existing attendance before adding new records and alerts for missing attendance dates. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. Why is it important to set up sorting within a report in Access, instead of relying solely on sorting in the query?
A. Because reports will ignore query sorting and use their own
B. Because query sorting is more complex than report sorting
C. Because reports can only sort numeric fields from queries
D. Because sorting in queries is not possible in Access
Q2. What must be done to ensure that a report is sorted first by student name and then by customer ID?
A. Place the sort by customer ID above the sort by student name
B. Place the sort by student name above the sort by customer ID
C. Only sort by one field at a time
D. Remove the group by customer ID option
Q3. When sorting attendance records within each group in the report, how should the dates be sorted?
A. In random order
B. With the oldest dates at the top (ascending)
C. By student name first, then date in descending order
D. By class name first
Q4. According to the video, if you want to display the report showing each date with the students absent for that date, how should you adjust the sorting?
A. Sort first by date, then by student
B. Sort only by student, then by class
C. Sort by class first, then by date
D. Only use one group at a time
Q5. What is the main advantage mentioned for learning basic VBA in Access?
A. It is required to use filters in reports
B. It allows you to make your databases more powerful
C. It eliminates the need for queries
D. It is the only way to create tables
Q6. In VBA, how do you represent a date literal in a filter string for Access?
A. By surrounding the date with asterisks
B. By surrounding the date with pound symbols
C. By surrounding the date with double quotes
D. By surrounding the date with parentheses
Q7. What property must be set to True to apply the filter in Access using VBA?
A. FilterApply
B. FilterOn
C. ApplyFilter
D. DoFilter
Q8. What is the purpose of the AttendanceAppendQ query mentioned in the lesson?
A. To delete old attendance records
B. To append/add new student attendance records for a specific date
C. To filter the attendance records
D. To generate a report of absent students
Q9. Why is it important to requery the form after running the append query to add new students?
A. To save changes to the database
B. To refresh the form and show the updated records
C. To reset all filters
D. To close the form and reopen it
Q10. What potential issue arises if you run the "Add Students" process for a date where students have already been added, and how does the video suggest handling it in the extended cut?
A. No issue, students can be added as many times as needed
B. Attendance records will be duplicated; code should check if records already exist and confirm before appending again
C. Students will be removed; data must be restored from backup
D. There is no way to resolve this
Q11. What is a suggested way in the video to handle situations where you may have forgotten to take attendance on previous dates?
A. Ignore those dates completely
B. Manually check calendars for missed days
C. Write code to detect missing attendance records and prompt the user
D. Re-enter all data for the semester
Q12. Why did the instructor recommend naming buttons such as AddStudentsBtn in forms?
A. Because unnamed buttons are not recognized by VBA
B. Because it is easier to reference and maintain in code
C. Because Access does not allow unnamed controls
D. Because unnamed buttons cause errors in queries
Q13. What was one of the advanced options or features suggested by users for future attendance system videos?
A. Using a barcode reader to scan students in
B. Sorting by phone number
C. Automatically sending emails for absences
D. Printing ID cards for all students
Q14. According to the instructor, what is the purpose of the TechHelp video series with respect to Attendance, as opposed to the Access Expert templates?
A. To provide a fully built, production-ready attendance system
B. To help beginners get started and learn concepts
C. To address only theoretical uses of Access
D. To focus only on coding and programming techniques
Q15. What does the instructor say about learning VBA for Access?
A. You will master it in twenty minutes
B. It is easy to get started, but it takes a long time to master
C. Only experts should attempt to learn it
D. It is not necessary for creating efficient Access databases
Answers: 1-A; 2-B; 3-B; 4-A; 5-B; 6-B; 7-B; 8-B; 9-B; 10-B; 11-C; 12-B; 13-A; 14-B; 15-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.
Summary
Today's TechHelp tutorial from Access Learning Zone continues our series on building an attendance tracking system in Microsoft Access. This is part five of the series, so if you have not already watched the previous four lessons, I recommend going through those first to get caught up on the project so far.
Up to this point, our attendance report is coming along nicely, but we still have an issue with sorting. The records are not organized in any meaningful way. They might be sorted by customer ID, but that is not very helpful for reading or analyzing the data.
To improve this, we need to sort the report so that entries are organized by both student name and date. In Access, simply sorting your query will not impact the order in your report. Instead, you have to set up sorting and grouping directly within the report design.
At this stage, our report only groups by customer ID. To fix this, I want to add sorting by student name first. In the report design view, there is an option to manage grouping and sorting. By adding a sort for student name here and promoting it to the top of the list above the customer ID grouping, we ensure records are sorted by the student's name first.
After applying the sort and taking a look at the print preview, we now see everything sorted correctly by first name. For most classroom settings, sorting by first name can be helpful, as it lets you quickly check if particular students, like Jimmy or Alex, are present.
Now, we need to tackle sorting the attendance dates within each group. I want those dates sorted with the earliest dates at the top. Returning to design view, I add another sort, this time on the class date field. When I check the print preview, everything looks great - student names are sorted, and within each student, class dates are in order. If you prefer, you can reverse this and sort primarily by date, then by student, depending on your reporting needs. In my Access Expert 15 course, for example, I demonstrate both methods.
For those interested in a more advanced attendance system, in that same Access Expert Level 15 class I cover more extensive features. There, you can set up multiple classes, enroll students in individual classes, track enrollment dates, payment status, and more. The structure is very similar, just with added flexibility and features - and all of that is handled without any programming, so you do not need to know VBA for that particular class. If that is of interest to you, there is a link available for more details.
Returning to our current lesson, we have now pushed the system about as far as we can without any programming. It is time to start introducing some simple VBA. If you are new to programming, do not be intimidated. I recommend starting with my "Intro to VBA" video. It is a quick overview that will give you enough foundation to get comfortable. Nobody learns VBA in 20 minutes, but you can get the basics down and see how much more power you can add to your databases.
One improvement we can make with VBA involves filtering the attendance form when it opens. Right now, opening the attendance form shows all records, which can be overwhelming if you are dealing with a whole semester's worth of data. It would be much more useful if the form opened filtered to today's date by default, letting you see attendance just for that day. If you want to see other dates, you can always remove the filter.
This can be accomplished by using VBA to set the form's filter property. The filter needs to display only records with a class date on the selected day. You do this by creating a filter expression that only includes records greater than or equal to the starting date, but less than the following day. It is important to remember that date values have to be surrounded by pound signs in Access. Be careful with spelling as well, since mistakes in field names trip up even experienced developers.
Once the filter string is composed, you assign it to the form's filter property and activate it. There is a whole video on how to use the FilterOn property if you need a deeper explanation.
With that set up, launching the form now shows just today's attendance. You can always turn off the filter to see all the data. As an alternative, you could add controls to let the user specify start and end dates, filtering the form according to those selections.
Another helpful update handles the process of adding students to a given attendance date. Previously, the main menu had a separate button that required users to close and reopen forms to refresh data. It would be much smoother if this button was moved inside the attendance form itself, allowing you to append students and then refresh the view automatically.
To implement this, I removed the old button and created a new one on the form, labeled "Add Students." When this button is pressed, VBA runs a query to append students to that attendance date and then refreshes the form with the new records.
Testing this, you can move to any date with no students, click "Add Students," and see everyone added instantly. The process is straightforward and efficient.
There is a downside, though. If you click "Add Students" again for the same date, you will end up duplicating students. Ideally, the system should check if records already exist for that date and warn you before adding again. That way, you do not accidentally add duplicates. Also, it would be helpful if the system could alert you when you have missed taking attendance on certain past dates, so you can keep your records up to date.
Both of those improvements - checking for duplicate entries and prompting when you have not taken attendance on previous dates - will be covered in the Extended Cut for members. We will set up the logic to detect existing records, prompt users with a yes-or-no confirmation, and show reminders for missing dates. We will even build the logic to skip weekends and handle custom timeframes. Silver members and above have access to all of my extended cut materials.
Before wrapping up, let me mention that I always welcome suggestions for additional features. Since starting this attendance series, I have received plenty of feedback. Popular feature requests include using barcode scanners to check students in, handling leave of absence situations, and managing multiple classes. If enough people ask for a feature, I am likely to create another video or even build a dedicated seminar or template.
Keep in mind, these TechHelp videos are meant to get you started. They are learning tools for developers looking to expand their skills, not production-ready systems for running a whole school. If you are interested in more advanced access databases, be sure to look at my expert courses and templates for more robust solutions.
That concludes today's lesson. 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 List
Sorting groups in an Access report
Adding sorting by student name in a report
Adjusting sort order priority in report grouping
Sorting report data by class date
Setting report sorting and grouping order
Applying filters to Access forms using VBA
Creating a date filter for Access attendance forms
Using VBA to set and activate form filters
Adding command buttons using VBA
Naming command buttons in Access forms
Running append queries with VBA code
Refreshing form records with Me.Requery
Handling potential duplicate appended records
|
