|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Checklists By Richard Rost 
6 years ago
Archive Daily. Keep History. Class Rosters. Safety Procedures. Learn how to create checklists in Microsoft Access. You will see how to set up a checklist table, create an event to set the date and time the checklist item was completed, and then when you're done with the list, archive the data and store it in a history table. Ezra from Seattle WA (a Gold Member) asks: I have a checklist of safety issues that needs to be done every day. I need to store the date and time that each issue was performed. I would like the user to be able to check a box, have the date and time automatically fill in, and then when the entire checklist is done be able to save all of that information for reporting later. How can I do this? MembersI'll show you how to convert your queries over to SQL to avoid cluttering your database. You will also learn how to create a custom sort order in case your checklist needs to be followed in a specific series of steps. We will use conditional formatting to color each line green when an item is finished. Finally, we will set up multiple lists, so you can have several checklists in your database.
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! LinksContinuous Forms: https://599cd.com/continuous
IntroIn this video, I will show you how to create interactive checklists in Microsoft Access that automatically fill in the date and time when an item is marked as completed. We will use event programming instead of default values, set up a history table to archive completed checklists, and add an archive button to your form to save data and reset the checklist for future use. You'll learn how to design the tables, forms, and queries needed for a daily safety or task checklist that can easily track and store completion data for reporting.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In this video, I'm going to show you how to create checklists in Microsoft Access. You'll be able to automatically have the date and time entered when you click on that completed date and time field. We're not going to use default values. I'm going to show you how to use a little event. When you're all done running down the checklists for the day, or however often you do it, when you're finished, click that archive button on the bottom and it will save all that information to a history table and refresh the checklist so it's clean for the next time you want to use it. Today's question comes from Ezra from Seattle, Washington. Ezra asks, I have a checklist of safety issues that needs to be done every day. I need to store the date and time that each issue was performed. I would like the user to be able to check a box, then have the date and time automatically fill in. And then when the entire checklist is done, be able to save all that information for reporting later on. How can I do this? Well, let's get started. Ezra, this is my blank database template. You can download a copy of this for free off my website. I'll put a link in the description down below the video. Pretty much all we need out of this template is this continuous form that I'm going to use in a minute. I have a video on how this is built. If you don't know how to use continuous forms, go watch that video. I'll put a link down below as well. Let's start by creating a table for our checklist. So, create table design. I like an ID for an auto number in every one of my tables. Just put item in here. That'll be short text. That's the checklist item name or item name if you want to. Item name. Then we'll put completed. That'll be a date/time. Now, don't set a default value because we're going to set that value when the item is actually completed. I want it to be blank if it hasn't been done yet. Let's save this as my checklist T. That's my checklist table. In this checklist table, we'll just put a list of items in here. Sweep floor. Take out trash. Do dishes. Send Rick Christmas present. I'm just kidding about that last one. So, this is my basic checklist, and this will get repeated every day. If you want to add items or delete items, you can do that right in here. As I know, you said you wanted a box to check when the item was completed. We're going to do something better. We're just going to make it so all they have to do is click on the completed field and it will fill in. To do that, let's go over to the continuous form here. Save changes to this. Come over here, continuous form. We're going to copy this and paste it, copy and paste. This will be my checklist F, my checklist form. Right-click, design view. This is my basic continuous form template. Let's go up here, open up the properties for this form. We're going to go to All and set the record source equal to checklist T. It's the only table that I have in this database. We're going to come in here and we're going to add existing fields and drop them right here in the detail section. We don't really need the ID. Let's just bring in item name and completed. Drop those like that. I can get rid of these guys now. Delete these. We're going to slide item name right up here like that. Then completed can go right next to it, and you want date and time so let's make it a little bit bigger. Let's go to the properties for this completed. Let's set the format equal to whatever you want. I'm going to go mmddyy hh:nn AM/PM. If you want, if it helps to see the day of the week in here, that's fine too. Go ddd so you get THU for Thursday. Whatever format you want, I cover these formats in a different class. I'll put a link to that down below. We'll just change our labels up here. Checklist item. Over next to this, over here, completed date time. Looks good so far. Slide that up. Slide that closed. Save it. Close this. Close this. Let's open it up now. That's what we got. Looks pretty good. If these have to be in any particular order, which you didn't mention, you could simply throw another field in here to order these, or you can sort them alphabetically, or do whatever you want. I'll put the custom sort order in the extended cut video for the members. Now, here's what I want to happen. When the user clicks on one of these boxes, I want the current date and time to fill in. To do that, we're going to use something called the On Click event. The On Click event runs whenever you click on a field. So right-click, design view. It requires a little bit of programming, but don't be scared. Visual Basic is not hard if you take it step by step. Start up the properties for this box. Go to Events, find the On Click event, which is right on top here. See that On Click? Hit the dot dot dot button. This will bring up the Visual Basic window. You may get a window asking you which builder you want. Pick the Code Builder. Now in here, we're going to say completed = now. That's it. Save it. Come back over here to your database. I'm going to close this and reopen it. Ready? Click. Look at that. It fills in right now. Click. See that? Click. That's the On Click event. It doesn't do anything to these guys, but if I click over here, click, it fills it in. Same. Click. Let me format it to show seconds to you so you can see it actually working. Let's do this. Slide this out like that. Move you over here. Align this guy like that since the date lines up to the right. Open this guy up. Let's put seconds in here in the format. hh:nn:ss. Whatever, so you can see it working better. Ready? Click. See? 12:10:04. Click. 12:10:06. One thing you might want to be careful of: if you click here, that's fine. If you click down here, it's going to keep adding new records. You have a couple of options. You can make it so you can't add new records in here. That's one way to do it. We can just go to Design View, look up the properties for this form, go to Data and set Allow Additions to No. Now you can't add new items on this form. You can only add them at the table or make a different form, so you can't add new items down here. That might be the way you want to do it. Or you can simply come into your code here and say something like: If IsNull(ItemName) Then Exit Sub In other words, if there is no item name, don't put a date/time value in there. It'll exit out of the sub without doing the completed = now. So in order to do that, I'll have to put that Allow Additions back on. Let's do Allow Additions as Yes. Save changes. Now if I open it up, if I click over here and there's nothing in the checklist item name, it doesn't do anything. So there are two ways to handle that. I don't want to confuse you with too much code. If you like the other way better, that's fine. This way at least allows you to add items. Go home, and now you can click here and put that value in there if you want to. Now it's the end of the day. You want to archive this information and save it into a backup table. What we're going to do is we're going to copy this checklist T. We're going to copy and paste. We're going to call this checklist history T. It's our historic data. We only want to copy the structure. I don't want all the data, just the structure. Hit OK. There's my checklist history. It's the same table, but it's empty. Now we're going to need two queries. One query is going to copy the information from checklist T into history T. The second query, an update query, is going to simply erase the dates out of this one, so it's clean and ready to go for tomorrow. First, we'll make an append query. Now, I've got separate videos for append and update queries. Go watch those if you've never done this before. Create query design. We are going to pull records from the checklist T. You can close the Add Tables pane. Bring in the item name and the completed. We don't need the ID in this case. Go to Design. Change this to an append query. We're going to append these records into checklist history T. Hit OK. Save this as checklist append Q. Now we can close it. The checklist history, right now, is empty. If I run the query, double-click on it, nothing appears to happen. You may get a warning message that says you're about to append five rows or whatever into a table. Do you want to do it? Hit OK. I have my warnings turned off. I'm going to show you how to turn those warnings off in your code in just a minute. But now if I open up my history table, there they are. If I run it a second time, double-click, it'll append them again. See that? Now it's the same set of data. What we have to do is clear the data from the checklist next. Let me erase all the stuff in the history table. Goodbye. Now we need an update query to go into the checklist table and make these blank, so it's ready for the next day. Create. Query design. Bring in checklist T. Close it. Change this to an update query. Now we're going to change something. We're going to change completed update to Null. I'm going to set the completed date equal to blank. Nothing. Null. Save this query as checklist update Q. So what we're going to do in our code is we're going to run the append query first. Double-click. That's got that in there. Now we're going to clear the checklist T, so we're ready. Run the update query. Double-click. Now open up the checklist table and those are blank. See what we're doing here? Now if I come into my form again, let's go into the checklist form, put some dates and times in here. We're going to finish these all up. Now if I run my append query again, I should have the new set of dates and times. Yep, all due at 12:19 now. Now I can clear my checklist T. But I don't want the user to have to mess with this stuff. Let's put a button in our form right down here on the bottom that says Archive or Completed or whatever you want to do. Design view. We're going to drop a button right down here. Find Command Buttons. Drop it down here. Cancel the wizard. There are wizard actions you could use to run queries. It's under Miscellaneous, Run Query. You could do it that way. But I don't like doing it that way. I want to teach you a little bit of programming. It's not scary. I want you to learn this with me. Let's put in here Archive. That's the name of the button. Actually, that's the caption on the button. Let's give the button a name. Make Alex happy. If you don't know who Alex is, then you haven't watched enough of my videos. This will be the Archive button, btnArchive. Let's put some code in it. Right-click, Build Event. Build event is what happens when we click on the Archive button. We have to run both of our queries and then probably refresh the screen. So let's do that. DoCmd.OpenQuery "checklist append Q" DoCmd.OpenQuery "checklist update Q" We'll probably need to requery the form in here. Let's try and see what we got first. Save this. Come back out here. Close this. Close this. Now, I have to close down forms after I put major changes in with some VB code. That way they open up fresh. Let's open up our checklist again. Put a couple things in here. Maybe you didn't finish a checklist. Hit the Archive button. As I suspected, these didn't clear off the screen, but I bet you they're clear if we close it and reopen it. Watch. Yeah, we'll need to requery in there. That's just to requery the information in here so it reloads from the table. But let's check our history table. And yep, there they are. See how these items weren't finished? Let's put that requery in the code. Let's go back into our checklist design view. Let's go back to the code window. This little button right there will bring you back to the code window. I put it up here on my quick launch toolbar as well. So right here we need a Me.Requery. Me.Requery says reload the records that are in the current form. Because we've cleared them, you want to see that update in the form. Also, I promised I'd show you how to turn off those warnings, because some of you might be getting warnings when you run your append and your update queries. I have them turned off in Access, but all you have to say here is: DoCmd.SetWarnings False That turns the warnings off. Then when you're done with your queries: DoCmd.SetWarnings True Turn them back on again. If you want them on, then you want to be able to see them again. This just turns them off temporarily for your button. So let's go back to the database. Close it. Open it up again. Put some new values in here and then archive. Boom. Save the data to the table. You can see right down here there's our new records. This one is cleared. Now, one of the things you got to watch out for. If I click on these buttons, when I click here, notice this record is still dirty. The pencil is there. If I hit the archive button right now, sometimes you'll get an error message. I got it through one of the iterations I was doing earlier, because technically when you do this, this record hasn't been saved yet. So one more thing you might want to throw in here at the very top is a Me.Refresh. That says save any record that happened to be currently being edited. So if I do that, if I hit this now, it refreshes that record first, then runs the query. That just makes sure that your data is safe. So there's not a whole lot of programming here to do a whole lot of stuff. So there you go. There's the basics of how to take a checklist, click to fill in these values here. This will work for pretty much anything that involves a checklist, like doing student attendance. I did an attendance database that I put on my website and YouTube a year or so ago. Very similar concepts. All you have to do is put your class roster here, and then if they're in class, just check, check, check. If they're not in class, just don't click on them. Then when you're done for the day, hit the archive button, and you're ready for the next day. To learn more about these checklists, well, in the extended cut for members only, I will show you a couple of different tricks. First, we'll convert those queries over to SQL statements because I don't like cluttering my database with lots of different queries. If I can convert that over to an SQL statement and put that right in my code, I will. Then we'll set up a custom sort order. For example, if in my cleanup list, things have to be done in a certain order, one, two, three, four, five, I'll show you how to set that up. We'll add conditional formatting, so when an item is done, the whole line goes green, not just that particular box, but the whole line, except for the order, of course. The checklist item and the completed date and time will go green. Then I'll show you how to set up multiple lists, so you can have your class roster, your cleanup list, your Star Trek series list, and lots more. Here's the extended cut database. Notice my queries are gone. I converted those over to SQL statements. I can open up any one of these lists by double-clicking on it. Here's my Star Trek list. Which ones have I watched? Well, I finished this one, I finished this one, and then I'm done. I can archive. Seriously though, I've finished them all, so I haven't finished Discovery yet, though. I'm still working on that one. So that's in the extended cut. Silver Members and up get access to all of my extended cut videos. 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 main purpose of the checklist Access database created in this video?A. To allow users to check items and automatically store the completion date and time B. To create shopping lists in Access C. To manage employee payroll D. To track inventory levels Q2. Which event is used to automatically fill in the current date and time for a completed checklist item? A. On Load event B. On Click event C. After Update event D. Before Insert event Q3. Why should you not set a default value for the completed date field in the checklist table? A. Because default values cannot store date and time B. Because you want the field to remain blank until the user completes the checklist item C. Because default values slow down the database D. Because default values are only for numbers Q4. What is the suggested best practice when using auto number IDs in the checklist table? A. Use them for every table as a unique identifier B. Only use them in forms C. Never use them in a checklist database D. Type them in manually Q5. How does the Archive button function in the checklist form? A. It saves completed checklist items to a history table and resets the checklist for reuse B. It prints the checklist C. It e-mails the checklist to the manager D. It deletes the entire checklist Q6. What type of query moves information from the checklist table to the history table? A. Append Query B. Select Query C. Delete Query D. Crosstab Query Q7. After archiving, how is the checklist cleared for the next day? A. An update query sets the completed date field to Null B. A delete query removes all items C. A macro deletes all records D. The user manually clears the fields Q8. What line of VBA code is used to set the completed date field to the current date and time? A. completed = Now B. completed = Date C. completed = Time() D. completed = 0 Q9. Why might you use Me.Requery in the Archive button's code? A. To reload the data and show the updated, cleared checklist in the form B. To backup the database C. To close the form D. To send an email Q10. What does DoCmd.SetWarnings False do in VBA? A. It suppresses warning messages while running queries B. It closes the database C. It turns off your computer D. It deletes warning logs Q11. What programming language is used for event handling in Microsoft Access forms? A. Visual Basic for Applications (VBA) B. JavaScript C. Python D. SQL only Q12. How can you prevent new blank records from being added at the bottom of the checklist form? A. Set Allow Additions property of the form to No B. Set Allow Deletions to No C. Set Locked to Yes D. Change the Record Source Q13. What precaution is mentioned if the current record is still being edited (dirty) when archiving? A. Use Me.Refresh to save any current edits before running queries B. Use Me.Locked = True to freeze changes C. Use Undo to cancel edits D. Set the form to read-only mode Q14. What benefit is mentioned for converting queries to SQL statements in VBA? A. It keeps the database less cluttered with saved query objects B. It increases the auto number increment speed C. It makes forms look prettier D. It disables date and time inputs Q15. Which of the following is an example of a practical application for the checklist database described in the video? A. Student attendance tracking B. Bank account reconciliation C. Project budgeting D. Inventory valuation Answers: 1-A; 2-B; 3-B; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A 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 covers how to create checklists in Microsoft Access, including a way to automatically record the date and time when each checklist item is marked as completed. I will show you how to add this functionality with a simple event procedure, rather than relying on default values. Once you have finished your checklist, you will be able to archive the entries, saving them to a history table, and reset the checklist so it is ready for your next use.The idea came from a question about maintaining a daily checklist of safety tasks, where the user wants each item to capture the specific date and time it was completed, and then later store that information for reporting purposes. The solution I share here works great for that need. We start with my blank database template, which you can download for free. The only part we need from this template right now is a basic continuous form, which is useful if you want to list items in a checklist format. If you are unfamiliar with continuous forms, I have another tutorial explaining them. First, we create a checklist table with an auto number ID, an item name (to describe each checklist item), and a completed field for the date and time. The completed field should not have a default value; we want it to start blank and only be filled in when the user marks that item as done. Once you have entered your basic checklist items, you can edit this table to add or remove tasks as needed. Some people prefer using a checkbox to mark items completed, but to make it more informative, I recommend that clicking the completed field fills in the actual date and time automatically. Next, I show how to set up your form. Base the form on the checklist table and display the item name and completed fields. Format the completed column to show the date and time in your preferred format, even including the day of the week or even seconds if you want a high level of detail. To make the completed field automatically show the current date and time when clicked, we use the On Click event. When the user clicks in the completed column for a checklist item, Access fills in the correct date and time. This requires just a brief VBA procedure, setting the completed field to the current time. You will see the value appear instantly on the form. You should be careful that users do not keep adding new, blank records by mistake. You can prevent new entries from being added by disabling additions in the form properties. Alternatively, you can add a short check in your VBA code to only record the date and time if the checklist item has an item name. At the end of the day or whenever the checklist is done, you will want to archive the data. The process involves making a history table (structurally identical to your checklist table but initially empty) and two queries: an append query to move the completed records into the history table, and an update query to reset the completed field to blank for the next round. This keeps your checklist fresh while preserving completed records for reporting. To streamline the process for users, we add an Archive button to the form. When they click it, VBA runs both queries and refreshes the form to show the updated (now blank) checklist. I will also show you how to temporarily turn off warning messages that might interrupt the process when the append or update queries run. A minor issue to be aware of is that if the user has just entered or changed data in a record but has not moved off the record, Access might not have saved those changes yet, and this could cause errors when the archive process runs. To handle this, the button's VBA should include a line to refresh the current record before running the queries. This ensures all data is safely written to the table. The methods shown here can be adapted to other scenarios such as tracking classroom attendance or routine task lists. The key is that you can create a checklist, record exactly when each item was completed, and easily archive your results for future reporting. In the extended cut for members, I will demonstrate some further enhancements. I will convert the queries into SQL statements within VBA code to avoid cluttering your database with extra queries. I will also show how to set up a custom sort order in case checklist items need to be performed in a specific sequence. On top of that, I will demonstrate conditional formatting, so that completed items can be highlighted (such as turning the whole line green), and show how to manage multiple checklists within one database, such as for different projects or lists. All the improvements are included in the extended cut, which Silver Members and higher can access. If you would like to become a member and get access to extended cut videos as well as live sessions and other perks, just visit my website and join at the appropriate level. However, all of my main TechHelp videos will always be free for everyone to watch. 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 a checklist table in AccessDesigning checklist table structure Adding checklist items to the table Creating a continuous form for the checklist Setting form record source to checklist table Adding fields to the form design Formatting the completed date and time field Using the On Click event to fill in date and time Writing simple VBA to set field value to Now Handling record additions in continuous forms Preventing blank checklist items from being completed Creating a checklist history table for archiving Building an append query to archive checklist data Building an update query to clear completed dates Linking an Archive button to run queries and refresh Disabling query warning messages with VBA Forcing form data to save before archiving Using Me.Refresh and Me.Requery in VBA Testing the checklist and archive workflow |
||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access checklists, archive, history, recurring, tasks, rosters, student attendance, conditional formatting on null value, continuous forms, onclick event, append query, update query, custom date formats, docmd.setwarnings, openquery, requery, all PermaLink Checklists in Microsoft Access |
