Attendance 2
By Richard Rost
11 months ago
Tracking Attendance for Students and Employees Part 2 In this Microsoft Access tutorial I will show you how to improve your attendance tracking database by setting default attendance dates, using a main menu to control form defaults, creating append queries to quickly add students to the attendance table, and adding buttons to automate these tasks with and without VBA. This is part 2. MembersThere is no extended cut, but here is the file download: 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 CoursesUp Next
Keywords TechHelp Access, attendance form design, attendance default date, unbound text box default value, =Date() usage, Forms!MainMenuF!AttendanceDate, append query attendance, button to run append query, add students automatically, VBA DoCmd.OpenForm, StudentQ as data source, AttendanceAppendQ, auto-populate attendance records
Subscribe to Attendance 2
Get notifications when this page is updated
Intro In this video, we continue with part two of the attendance series in Microsoft Access. I will show you how to set a default date using an unbound text box, update default values with the =Date() function, and standardize naming for fields and controls. We will cover techniques for referencing values from another form, create buttons to open forms using both the command button wizard and VBA, and build an append query to add student attendance records for a selected date. You will also learn how to set up a button to run that query directly from the main menu. This is part 2.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor Richard Rost and today is part two of our attendance series. If you have not watched part one yet, what are you doing here? Go watch part one. Go on. Go do it and then come on back.
All right, so yesterday we built our little attendance form here and it has our list of students, the date, and whether they are present or not. I am going to slide this over here. Save it there.
Now, what I want to do next is instead of setting this default date in the table, I want to grab the default date from that guy right there. You can change this, and then when you add new records, it will just use whatever date is there. This will default to whatever today's date is. So when you open this guy up, it will add whatever today's date is in it.
That is getting a value from a field, which is one of the prerequisites from before. So design the main menu. Let's go in here. I am going to change this to say attendance date.
Now, this is currently called current date. I want to change this to attendance.
Oops. My mouse has been doing something really weird. It just randomly clicks wherever I leave the pointer sitting and it will just be here. I have gone through several mice and it is not the mouse, it is the system.
Anyways, the name attendance date is now the name of the field. Now we cannot have equals date in the control source because the control source says this will always be your value. If it is bound to a table, it gets the field that is in the table. If it is a function like that, it always will have that value.
So I do not want to put anything in there. It is going to be an unbound text box. But I still want it to start off with today's date. So go to data and default value. We are going to put =Date() just like that. This way it starts off as today's date, but you can change it if you want to. You are going to do like two or three days in a row of attendance.
Now save this and close it. Let's see what we have there. That is good. That is today's date. Let's go to the attendance table now and get this out of here. Get that data out of there.
Why have you put it there in the first place? Just because we were doing the tables first and I did not want to type it in every time. Now we are going to put it in the attendance form down here.
Design view. This guy is going to have its default value set to =Forms!MainMenuF!AttendanceDate. There it is right there. I will zoom in so you can see that. Since I am a good little boy and do not put spaces in my field names or my form names or my object names or any of that stuff, I do not really need these square brackets. But Access puts them on there for me because it is trying to be nice.
If you were not a good little boy and you had like that, and I see this all the time, if you do not know any better, if you did not take my beginner lessons, then you probably have these in your database. It makes it so much easier if you do not have spaces in your field names. Trust me.
I can always tell when someone joins as a member and then they post a question in the forums and they have spaces everywhere and weird characters everywhere, and I am like, yeah, you did not learn from me. We will help you fix it, but this is why you are having a problem.
All right, so save it. Close it. Close it.
Now we need to change this button so that it opens up the attendance form. I am sick of doing that. I am having to come down here and do it. Instead of a hello world, this will be our attendance button.
Now I promised this was going to be an expert level class, and I am going to show you how to do it without any coding. But then I am going to show you real quickly how easy it would be if you know one line of code.
We are going to delete this button. You would not have to delete it if we were using VBA. We are going to delete it anyways.
Form design. Grab a button. Drop the button here. Form operations. Open a form. AttendanceF. Yeah, show all the records. Sorry, I blanked. I was looking for the next step, which is this one. Text and then attendance form. Next, give a good name. AttendanceButton. Then finish.
See how many steps that was? And it works. There you go.
Watch how fast this would be if we did not use the wizard. If you know a little bit of VBA, you are ready. Form design. Grab a button. Drop it here. Cancel the wizard. Change the caption. Attendance form. Give the button a name if you want to, you do not have to. If you do not, Alex will yell at you. If you do not know who Alex is, go to my website. AttendanceButton, whatever you want to call it.
Then watch this. Right click. Build event. Brings up the code builder. A lot of this stuff normally is not on. Let me turn off all the optional stuff. There you go. Normally it is this simple.
Ready? You are in the sub here. You go in here. You do DoCmd.OpenForm "AttendanceF". That is it. That is all you need to know. That is a whole lot simpler than running that wizard. I just like to show you because I want you to not be scared of VBA. It is super easy to use.
If you want to get your feet wet with VBA, go watch this. It will get you started. It is about 20 minutes long. It teaches you everything you need to know.
We will be doing a little bit of VBA for the advanced students a little bit later on.
Close this. Open it back up again.
Now, what it is time to do tomorrow: Let's say 5/31. You put the date in there first. Hit attendance form. Now notice that your default value for the next record down there is going to be for whatever date is over there. So if you want to do these by hand, now it makes it easier. Put Jim Kirk in there. Put Mr. Spock in there. He is in class. You put Reginald Barkley in there. He is in class, and so on.
But I do not want to have to insert these by myself by hand. I mean, you can if you want to. You have like a special study group or something and you want to just note who was at it. But I want to make a button that I can click that just adds them automatically.
Whenever I hear adding records to a table, adding means, oh, that is append. So I am going to create an append query to add those records for me. So close that form.
Let's create an append query. If you did not watch the prerequisite append query video that I mentioned at the beginning of this series, go watch it now.
Create. Where are you? Design. We are going to add in as our source of data. Where are we getting our data from? That is our student query. You can find it over here. I almost do not like this little pane over here. I almost always use this guy. StudentQ. Drop it right there. That is where we are getting our data from.
Now, what do I need to insert into the attendance table? I need to insert the CustomerID. Do not need that. Do not need that. So I just need to insert CustomerID.
Let's change this to an append query. So query design. Pick Append Query over here from query types.
Where are we appending this data to? That is going to go into the Attendance table. Hit OK. Now, notice down here, Append To: CustomerID was set automatically because the field names are the same.
Let's save this real quick. I am going to save this as my AttendanceAppendQ because we are going to make a different attendance query later too.
Let's just see what happens if we run this guy now. You can either close it and reopen it, or I put a Run button for my queries up here on my quick access toolbar. I am going to run this.
I have warnings turned off. I believe I teach you how to do that in the append query video. Normally, you would see something like, you are about to append six records. Are you sure? I turn all that off. I do not like that.
But let's take a look now at the attendance table. Oh, look at that. There are six new records in there with all of my student IDs. That is pretty cool.
I am missing the class date times though. Why? Because we did not tell it what to put in that field.
Let's go back over here. Delete these records that we just inserted. Now, we have to insert a date and time in here. How do we do that? Well, the date and time is going to be this date and time right there. It is one of the reasons why I want to put that on the form so you can grab it.
How do we get it into the destination table? Watch this. There is no field for it up here, but append to the next column over here. Drop that down. Look at that. We can specify any of these fields that we want to.
Well, we do not need to specify that. That is an autonumber. We already have that. There is our class date time. What do I want to put in class date time? I am going to put that value. What is it going to be equal to? Forms!MainMenuF!AttendanceDate. You do not get Intellisense for this, unfortunately. Now, when you hit tab, it is going to throw [Expr1] in front of it. That is OK. You can leave that if you want to. This really does not matter. That is just what is called an alias. You can call it "Date" or whatever you want to call it: "A", "D", I do not care. Give it any name you want. It does not really matter. That is what you normally see in a select query when you see the column headers across the top. For an append query, it really does not matter.
It is just going to say something. Leave it at that.
Now, let's run it again.
Now let's take a look at our attendance table. Oh, look at that. There are our dates. You can set present if you want to, to be all on, or whatever. How you want to handle that, that is up to you.
Now, again, let's delete these because I do not want to have to run that manually by opening up the query. I want to have a button that does it for me.
Now, you can put a button out here to run that query without using VBA if you want to. I am going to show you how to do it.
Grab the button. Drop it there. Go to miscellaneous. Then run query. Next. Which one? The Attendance Append. Next. Put text in here. Add students for date. Next. Give it a button name: StudentButton. And finish.
There is your button. Add students for date. I have a weird thing where my buttons have to be all the same width. And height, too. I do not like the buttons not being the same height.
Looks good. So now, save it, close it, open it. Let's put in here the third. What do we not have? Let's see. Okay. We need the 31st in here. So let's put in here the 31st.
Hit the button. That runs the query. Again, nothing happens. Nothing appears to happen. But when you open this up now, there is all your 31st. That ran the query.
Now, you have a fully functional and working attendance system right here as it is.
Next up in part three on Monday, we are going to build an attendance report. That is going to look like this. We are going to have each student in here, whether they are present or not. We are going to count up their absences and presences and such.
Then after that, we are going to come back to this form, and we are going to put an Add Students button right here. This is going to involve a tiny bit of VBA.But it's going to be super cool. All right. Because let's say you're doing the 31st. Show attendance. You can say add students right here. Boom. There they go.
And we're going to have a little bit, like two lines of code. Also, I'll show you that after we do the report. And then we have an extended cut coming with some more extra cool stuff in it too.
For example, what happens if you try to do that again? Oh, you already have students on this date. Do you want to have them again? No, not really. And we're going to do it when you open up the database. It's going to say, oh, you forgot to take attendance on the 26th. Go back and look a week or two and say, oh, so we're going to add all these kinds of cool things in.
A lot of it's going to be in the regular versions, a little bit in the extended cut. So we have lots more coming up.
We'll continue with part three on Monday, June 2nd, 2025. And if you're watching this way in the future, don't worry about it. It's going to be online anyway. So you're just good.
So today is Friday when this video is releasing. Tune in on Monday. Ignore that. Tune in on Monday. Same bat time, same bat channel. Soon.
I don't have it recorded yet, but I'm probably going to record it over the weekend or tonight. I don't know. We'll see. We'll see how strong the coffee is within me.
But that's going to do it. Folks, that is your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
We might have a Quick Queries tomorrow, Saturday. If not, I'll see you on Monday for part three.
TOPICS:
Setting default date in an unbound text box
Using =Date() for default value
Renaming fields and controls for consistency
Getting values from another form field
Setting default value using Forms!FormName!Control syntax
Creating a button to open a form using the command button wizard
Creating a button to open a form using VBA
Adding records to a table with an append query
Setting an append query's target table and fields
Referencing a form field in an append query
Adding a date value from a form to appended records
Creating a button to run an append query
Naming and organizing command buttons for consistency
Testing automated addition of student attendance records
COMMERCIAL:
In today's video, we're continuing with part two of our attendance series in Microsoft Access. You're going to learn how to let users pick a date on your main menu and have that date automatically fill in on your attendance form. We'll change default values, fix up form and field names, and see two different ways to build buttons that open forms – with and without VBA. You'll see how to create an append query that adds all your students to the attendance table for the selected date, and then set up a button to run that query right from your menu. Next time, we'll build an attendance report and add some more tricks, including a little bit of VBA and more features in the extended cut. 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. What is the main improvement made to the attendance form in this video tutorial?
A. Automatically generating student IDs
B. Grabbing the default attendance date from a form field
C. Importing student data from Excel
D. Adding barcode scanning for attendance
Q2. Why is it recommended to avoid spaces in field, form, and object names in Access?
A. It allows for better formatting in reports
B. It makes databases look neater
C. It prevents issues when referencing fields in queries and code
D. It helps with faster data entry
Q3. How is today's date set as the default value in the attendance date field on the main form?
A. By typing it manually each time
B. By using =Now() in the control source
C. By setting the Default Value property to =Date()
D. By binding it to the date field in the attendance table
Q4. What does making the attendance date text box unbound allow you to do?
A. Prevent data from being stored at all
B. Always force attendance to be for today's date
C. Change the attendance date and use it for new records
D. Link it directly to the student list
Q5. What is the purpose of referencing Forms!MainMenuF!AttendanceDate in the attendance form's default value?
A. To display a message when the date is changed
B. To copy the date from the main menu form into new attendance records
C. To update all previous attendance records with the new date
D. To create a calculated field in the table
Q6. What is the advantage of using an append query in this context?
A. It calculates total attendance for each student
B. It automatically adds records for all students on the selected date
C. It deletes outdated attendance records
D. It exports attendance data to another database
Q7. Why was the class date time field missing an entry at first when running the append query?
A. The query was set to select instead of append
B. The studentID field was empty
C. The value for class date time was not specified in the query
D. The attendance table was locked
Q8. What does adding a button to run the append query on the main form accomplish?
A. It validates student attendance
B. It allows users to quickly add attendance records for all students for a specified date
C. It automatically calculates student grades
D. It refreshes the student list
Q9. What method was shown to open the attendance form using a button?
A. Embedding a macro inside the button
B. Typing the code DoCmd.OpenForm "AttendanceF" in the button's event handler
C. Linking to a website
D. Exporting data to Excel
Q10. Why might a user decide to utilize VBA for form and query actions instead of wizards?
A. Wizards cannot be used to open forms
B. VBA provides more flexibility and is quicker for experienced users
C. Wizards cannot create queries
D. VBA is required for basic database operations
Q11. What is the general purpose of an append query in Microsoft Access?
A. Selects and displays records in a datasheet view
B. Deletes specific records from a table
C. Copies records from one table or query and adds them to another table
D. Updates field values for existing records
Q12. When setting up an append query to populate the attendance table, from where is the data being sourced?
A. The attendance table itself
B. The main form only
C. The StudentQ query containing the list of students
D. An external text file
Q13. What happens if you try to append students for a date that already has attendance records for those students?
A. Access creates duplicate student records automatically
B. Access overwrites the existing records
C. Nothing unless additional logic is implemented to check for duplicates
D. Access displays an error and stops working
Q14. Why did the instructor emphasize designing buttons to have a consistent width and height?
A. For improved database performance
B. For security reasons
C. For visual consistency and usability
D. For better exportability to Excel
Q15. What did the instructor say about using the query wizard versus writing VBA code for opening forms?
A. The wizard method is always better than VBA
B. Both methods can work, but VBA is simpler and more flexible for advanced users
C. VBA does not allow opening forms
D. The wizard does not allow setting button captions
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-C; 8-B; 9-B; 10-B; 11-C; 12-C; 13-C; 14-C; 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 is part two of our attendance series. If you have not already watched part one, I highly recommend you start there so everything makes sense.
In the previous lesson, we set up an attendance form displaying a list of students, the date, and whether each student was present. Today, we're going to refine how the default attendance date is set and talk about ways to make entering attendance records for a particular date much easier.
Initially, the attendance form used a default date set directly in the table, but I want to improve this so the form pulls its default date from a specific field on the main menu form. That way, you can change the date on the main form, and new attendance records will inherit that chosen date. By default, this field will load today's date, but you can enter any date you need if you're taking attendance for another day.
To set this up, I edited the main menu form and renamed the date field to AttendanceDate for clarity. I made it an unbound text box, so it is not linked directly to a table. For its default value, I used =Date(), ensuring that it will prefill with today's date when the form loads, but still gives you the flexibility to change it.
After saving those changes, I moved to the attendance table and removed the previous default date. The form's date field will now supply that information instead.
On the attendance form itself, I set the default value for the date field to pull from Forms!MainMenuF!AttendanceDate. If you name your fields and forms without spaces, Access will let you reference them easily without needing to wrap everything in brackets. I've learned over the years that those who follow best practices for naming rarely run into trouble.
After updating and saving everything, I replaced the button on the main menu so it would open the attendance form directly. While you can do this using the command button wizard in Access without code, I also demonstrated how much easier and cleaner it can be with just one line of VBA if you want to go that route. If you're nervous about VBA, don't be. A simple DoCmd.OpenForm will take care of it, and I have a full beginner's video on my website introducing VBA if you're curious.
With these improvements in place, you can now pick a date on the main menu, open the attendance form, and any records you add will default to that chosen date. This makes it much faster if you want to do several days of attendance all at once.
However, having to manually enter each student for each day is still tedious, so next I wanted to automate the process of adding attendance records for all students on a chosen date. To accomplish this, I used an append query, which is a standard Access tool for adding records to tables.
I set up an append query pulling data from my student query as the source. You only need to add the student ID to the attendance table, and then set the class date/time field to the chosen date from the main menu form using Forms!MainMenuF!AttendanceDate. Access allows you to specify where each value goes in the destination table. The alias Access uses in the query, like [Expr1], can be ignored in this context since it only serves as a label.
Running the append query instantly adds attendance records for every student, all associated with the correct date. This automation speeds up data entry significantly, but I wanted to make it even more user-friendly by adding a button to the main menu that runs the append query with one click. Again, you can do this without VBA by using the button wizard to create a button that runs the query directly.
After everything is set, you just pick your desired date, click the new button, and all the attendance records for your students are created for that date.
Looking ahead, in part three, I'll show you how to build an attendance report to summarize who was present or absent and count attendance statistics for each student. Later, I'll enhance the attendance form with an Add Students button that uses a little bit of VBA for even more control. Some of the more advanced features, like checking for duplicate entries or reminding you if you missed a day of attendance, will appear in future lessons and in the extended cut, so stay tuned.
Part three will be available on June 2nd, 2025, but if you're viewing this later, you'll find it online along with all previous installments.
If you're looking for a complete video tutorial with step-by-step guidance on everything covered here, you can find it on my website at the link below.
Live long and prosper, my friends.Topic List Setting default date in an unbound text box
Using =Date() for default value
Renaming fields and controls for consistency
Getting values from another form field
Setting default value using Forms!FormName!Control syntax
Creating a button to open a form using the command button wizard
Creating a button to open a form using VBA
Adding records to a table with an append query
Setting an append query's target table and fields
Referencing a form field in an append query
Adding a date value from a form to appended records
Creating a button to run an append query
Naming and organizing command buttons for consistency
Testing automated addition of student attendance records |
