|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Progress Bar Form Completion By Richard Rost 
6 years ago
Progress Bar 2: Show Completion Progress of Form Learn how to use a Progress Bar to show the completion progress of a form with a large number of fields on it. David from Virginia Beach, VA (a Gold Member) asks: I really enjoyed your video on progress bars. I have a form with about 70 text boxes that require information to be entered over several weeks time, not all at once. Is there any way to create a progress meter indicating how much of the form has been completed to date? I would like it to appear at the top of the form and show how each record's profess stands as it is changed. Thanks, and live long and prosper! MembersI'll show you how to use a For Each command to loop through the controls on the form and count them automatically using the Tag property. We will also display the percent complete for each of the records in a continuous form and make them look pretty with some conditional formatting.
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! LinksOriginal Progress Bar Video: https://599cd.com/ProgressBar
IntroIn this video, we will talk about how to create a progress bar on a Microsoft Access form that visually indicates how much of the form has been completed based on the fields filled in. You'll see how to set up native Access controls for the progress bar, add simple VBA code to update it, and trigger automatic updates when fields change or as you move between records. This tutorial is based on a viewer question about tracking form completion when users enter data over time, and I'll show you step-by-step how to implement this feature for your own databases.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am Richard Rost, your instructor. In today's video, we're going to talk about progress bars a little more. I did a video a few months ago about progress bars. I've had a couple of people email me since then who want to know how they can use a progress bar on a single form to show how much of that form is completed.If you have a big form with a whole bunch of controls on it, you want to know that you're, for example, 65 percent completed. In this video, I'll show you how to do exactly that. Today's question comes from David in Virginia Beach, one of my Gold members. David says, "I really enjoyed your video on progress bars. I have a form with about 70 text boxes that require information to be entered over several weeks' time, not all at once. Is there any way to create a progress meter indicating how much of the form has been completed to date? I would like it to appear at the top of the form and show how each record's progress stands as it's changed. Thanks and live long and prosper." David was replying to my email signature at the bottom. I have "live long and prosper" on each of my emails if you've ever emailed me before. But yes, of course, you can definitely use the progress bar to indicate which of the fields on your form have been filled in. Let me show you how to do it. If you have not yet watched my original progress bar video, go watch it right now. It's on my website. It's also on my YouTube channel. I'll put a link down below that you can click on, but there's the page. Go watch that because I'm going to use that in this video. Here's a copy of my blank customer template. You can also find a copy of this database on my website. It's a free download. It basically has some forms and a simple customer table so I don't have to keep reinventing the wheel for each video. It's got a basic customer form. It looks like that and, of course, a customer list. The customer table is right there. We don't need all of these fields for this example, so I'm going to cut this down just a little bit to make the video a little easier. Let's get rid of all of this stuff and keep like five fields. One, two, three, four, five. Let's chop all this stuff off to make it easy for just the purposes of class. So there we go. I'm going to move this stuff down a little bit so I can put my progress bar on the top. Now I'm going to go grab the progress bar from my other database that I built in the previous video. Gold members, you can find this in the download folder. The rest of you will have to type stuff in. Now I'm feeling a little generous today. I'm actually going to use the progress bar that I created in the members-only extended cuts. I'm going to show that to everybody right now. It's a bonus. This is just for you guys. In this video, I showed you how to make a progress bar that runs whenever you do an automated batch. You've got a bunch of queries that have to run, or for my members, I showed how to do it with a recordset in my developer class. If you click on this button, it says, "Are you sure?" Then the progress bar, you can see it running up there. It's basically just faking a process in the background. The goal is to show you how the progress bar works. I'm going to take this PBarF, that's my progress bar form, and just bring it over to my other database real quick. Then I'll show you how to copy the controls. Here's PBarF. Click and drag it. Boom. Drop it in there. Close that database. What is inside PBarF? If I go to Design View, here's the progress bar. It's really simple to build. I show this in the extended cut. There are two text boxes here. There's a gray one in the background that's just to provide the backdrop. I call that one PBBack. There's one in the foreground that's called PBFront. That's the one that I change the size of to make it slide across the other one. How do I do that exactly? With a little bit of code. It's in the extended cut of the other video. If you want to do that, you'll have to go watch that one. I'm going to give you the code that you need to make it work. You just lay these over the top of each other and they form a nice little progress bar, just like that. I'm going to copy these two objects over to the other form. Copy, go back to my customer form, Design View, and then paste them in. Paste. There we go. There's my progress bar. Now, it doesn't do anything without the source code. Let's go over here and grab the code. Up on the Design tab, go to this little guy right here. It says "View Code" - that opens up your Visual Basic window. All you need is this right here. It's my PBar subroutine. That's how you control the progress of the progress bar. In a nutshell, you send it a value, PercentComplete, which is a number from zero to 100. You set the front progress bar Visible to True. You set the width of PBFront equal to the width of PBBack times the percentage. If you send it 50, it's going to make it 50 percent the width of the back text box. That works. Then it just changes the label - not really a label, but the text in it. It says, "Percent Complete: 50 percent." It's very simple. I do this in the extended cut, but we need to put this in the other form now. Copy that. Close this form now. Save changes. In this form, go to the code. Put that in anywhere up top here. Paste. There you go. Now we can call my PBar anywhere we want. Want to see how that works? Just drop a button in here to test it. Cancel the wizard. I'll put Test in here. Right-click, build event. In here, I'll just say MyPBar 50. Give me 50 percent complete. Save it. Come back over here. Close this guy. Open it back up again. I'll test it. Boom. See? 50 percent. Very simple to control the progress bar. Two text boxes and a little tiny bit of code. The benefits of Visual Basic programming: you only need four lines of code to make this really cool thing. I'm not using the external control bar or the external progress bar. It's one of the things I talk about in that other TechHelp lesson. I don't like using the external controls if I can avoid it: the progress bar, even the tree view control, things like that. I don't like using them because if they update them or they update Access, sometimes they stop working, especially if you distribute this stuff to other people. I like to come up with solutions whenever I can that are 100 percent based on the Access native controls: text boxes, list boxes, and so on. And by the way, if you're new to Visual Basic programming, if you've never done any programming before, watch my Intro to VBA video. It's on my website and YouTube. It's free before you do any of this stuff. Again, I'll put a link down below for you. All right, so now we've got the progress bar working. Now what I want to do is look at these fields in here and say, "Hey, I only want to have this progress bar indicate the number of fields on here where the values are not null," where there's something in each of these boxes. So let's make another subroutine. Instead of only clicking Test, putting 50 in there, let's add these up and do some math. In here, let's call this sub CalcPBar - calculate the progress bar. That'll be a custom sub, which I'm going to put right down here: Private Sub CalcPBar. That'll go down inside of this stuff down here. Let me rearrange my windows a little bit so you can see better. There we go. The button, which is Command26_Click, is going to run CalcPBar. I didn't give the button a good name. I don't want Alex to yell at me. If you don't know who Alex is, you haven't watched enough of my videos. We're not going to keep this button around. We're going to get rid of it later. This is just to run this code right now. Later on, we'll run the code automatically, but for now, it's just a temporary button. What are we going to do in here? Basically, we're going to do a little math. I know how many fields there are: five. I want to count up the ones that aren't null. We're going to need two variables in here: Dim TotalFields As Long, and we're going to need another one. Let's just call it Finished As Long. Set TotalFields = 5, and Finished = 0. Now I'm going to say If Not IsNull(FirstName) as the first field, then Finished = Finished + 1. I'm going to do the same thing for each of those controls: LastName, Email, Phone, and Address. Add them all up. Next is LastName, then Email, Phone, and notice how they're capitalizing. I typed them all in lowercase, and then when I move off of them, they capitalize. That's how I know they're matching up to the fields that are in here properly. Then Address. We've added them all up, and now if they're all done, this should be five, and of course, five divided by five is one, or one hundred. Now I'm going to say MyPBar (that's my function), PercentComplete, is going to be Finished divided by TotalFields times 100, because I want that one to turn into 100 because the PBar is taking a number from zero to one hundred. This here would be a maximum of one. Looks good. Let's save this. I'm back out here. Now if I push this button, boom, it goes up to 100. Let's say they didn't put the last name in there. Test it. Boom, 80 percent. Let's say they didn't have the phone number too. Boom. So the progress bar is working now based on what's in here. Now, I don't necessarily want to have to click a button. I want these to update automatically, so we have to tie an event to each one of these boxes: AfterUpdate events. The AfterUpdate event fires whenever you change the data in here and then tab off it, or move off it in some way. Open this up. Events: AfterUpdate... is the builder button. You might be asked what kind of builder you want; pick the code builder. I've got videos on that too. I have mine set to always use the code builder. I show you how in my code builder video. I'll put a link down below, and I've got videos for the AfterUpdate event. In here, all we need is one line of code: CalcPBar. That's it. That's why I wrote this as its own subroutine, because everyone now can call that. I have to put this in the AfterUpdate event for each one of these text boxes. There's no way around that, unfortunately, but it's easy to do and you only have to do it once. Any other changes, you just do right inside the CalcPBar. So, LastName: AfterUpdate... CalcPBar. I'm going to put in some little tabbing in here. What I like to do is highlight this, including that line in front of it, copy, then go to the next one: Email, AfterUpdate... then paste, and it comes in with the proper indentation. Keep your code nice and neat. I get people sending me samples of their databases to look at, and I can't read it because there's no indenting or spacing. If you want me to look at your database, you have to properly indent everything. AfterUpdate... paste. Form with proper formatting is important. It makes it easier to work with a database from a programmer standpoint if it's properly commented, documented, and indented. Get rid of these buttons. I'm going to delete this so it's not in here. The Command24_Click is the "open up other forms" that we don't have in this template. I'm missing something I like to have up top: Option Explicit. I deleted it from my older databases and I should put that back in the template. That just says you have to explicitly declare your variables. I like that because if you type something in, like you type a function name wrong or a subroutine wrong or a variable name incorrectly or a field name incorrectly, Access will actually alert you instead of just assuming that's some new variable. You have to explicitly declare everything. Now I can get rid of Command26_Click because I no longer need CalcPBar in the button. We can actually delete the button off the form. Let's get rid of that. The nice thing about my progress bar is that because the sizes are based on the size of the back text box, I can resize this without losing any functionality. That's why I designed it that way. The front bar is changed as a fraction of the back bar no matter what its width is. Save it, close it, open it back up again. Notice I've got nothing in there. There's a way - we'll fix that in just a second. For now, let's just put something in here. "Rost." Tab. Boom. 80 percent. Delete the email. Boom. Back down to 60 percent. Phone number. Boom. So the progress bar is working in the AfterUpdate event for these fields. Notice, however, if I move from record to record, it doesn't work. Or when I open up the form the first time, it didn't work. We have to fire that event also when the form opens and when you move from record to record. One event handles both of those. It's called the OnCurrent event. Again, I've got lessons on how that works. The form properties are right here. Open that up. Find OnCurrent under events. Come in here. And guess what? CalcPBar. Close it, save it, open it up. Boom. It fires as soon as you open it. When I move from record to record, now you can see everything works. Get rid of a phone number here. Let's get rid of a name there. You can see how it works as I move from record to record. There you go. Want to learn more? In the extended cut for members, I'll show you how to use a loop inside your VB code so you can loop through the different controls on the form without having to add them all up individually. You can say, "For each text box on my form that I want to add up, add its value to the total number of text boxes." We'll count the fields automatically instead of individually. That way, every time you add a new text box or even delete one, you don't have to adjust your counts. We'll use the Tag property to mark which text boxes we want to include in our count. Then I'll show you how to display that percentage on your list form, as you can see right there. You'll add a little conditional formatting to make it look pretty. The records that are finished will be green, then yellow, then orange, and so on. That's all 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. 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 progress bar discussed in the video?A. To visually show how much of a form has been completed by the user B. To speed up form loading and reduce processing time C. To display error messages during form submission D. To validate required fields Q2. How is the progress bar implemented on the form? A. By using two text boxes overlaid on each other with code to adjust their sizes B. By importing an external progress bar ActiveX control from Microsoft C. By creating a new table to record user progress D. By using a macro attached to each form control Q3. What data type and value does the progress bar subroutine (PBar) expect as an argument? A. A string value representing the field name B. A numeric value from 0 to 100 indicating percent complete C. A Boolean indicating completion (True or False) D. An array of fields Q4. Which VBA event is triggered to update the progress bar when a user modifies a field? A. BeforeUpdate B. OnOpen C. AfterUpdate D. OnClick Q5. How does the code determine whether a field is considered 'complete'? A. It checks if the field contains a valid email address B. It tests if the field value is not null C. It checks if all fields are longer than three characters D. It verifies if the field has been clicked Q6. Why does the instructor prefer not to use external ActiveX controls for things like progress bars? A. They are expensive to license B. They are not visually appealing C. They may stop working when Access is updated or distributed to others D. They require internet access Q7. What is the benefit of referencing the back text box's width when resizing the progress bar front? A. It prevents calculation errors with percentages B. It ensures the progress bar remains proportional regardless of its size C. It limits the bar to a maximum width of 200 pixels D. It creates a colorful gradient effect Q8. What additional feature is implemented so that the progress bar updates when navigating between records or opening the form? A. Attach the update code to the OnCurrent form event B. Use a timer to refresh the bar every second C. Place a hidden button on the form to refresh manually D. Require the user to press F5 to update Q9. What programming practice does the instructor recommend for variable declaration in VBA? A. Never declare variables explicitly B. Always use Option Explicit to require explicit variable declaration C. Use only global variables D. Declare variables only inside loops Q10. What does the instructor suggest for maintaining clean and readable code? A. Avoid commenting the code to save time B. Rely on automatically generated code structures C. Properly indent and comment your code D. Only write code in uppercase Q11. In the video, what is the suggested method for automating inclusion of controls in the progress calculation, as mentioned for extended cuts? A. Use the Tag property to identify which text boxes to include in the loop B. Manually count the controls every time C. Store field names in an external file and reference it D. Only include the first five controls always Q12. What is the main benefit of using native Access controls for the progress bar instead of non-native ones? A. Ability to use more colors B. Greater compatibility and reliability in different Access versions and deployments C. Faster form submission speed D. Easier to export forms to Excel Answers: 1-A; 2-A; 3-B; 4-C; 5-B; 6-C; 7-B; 8-A; 9-B; 10-C; 11-A; 12-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 will focus once again on progress bars in Microsoft Access. I previously made a video on the topic, and since then, several viewers have asked how to create a progress bar that shows the completion status of a single form. The goal is to see, for example, that your form is 65 percent filled out as you move through your data entry process.The question this time comes from a viewer, David, who works with a form containing about 70 fields that users fill out across several weeks. He wants a way to indicate, at the top of the form, how much of the data has been entered for each record. This visual cue would update as the user works with the record. First, if you have not seen my original progress bar tutorial, I recommend checking that out. You'll find it both on my website and my YouTube channel. The background information from that lesson will give you important context for what we're doing here. To demonstrate how to set up a progress bar that reflects form completion, I used my blank customer template database. This template is available as a free download from my website. It comes with a simple customer table and some forms so that you do not have to build everything from scratch each time. For this demonstration, I narrowed the form down to just five fields to make things easier to follow. Once the form was simplified, I made room at the top to add the progress bar. To bring in the progress bar itself, I reused the progress bar I built in the previous lesson. Members will find this object in their downloads folder, but even if you are not a member, you should be able to recreate it using my instructions. As a bonus for this video, I decided to show everyone the progress bar that I originally demonstrated for my extended cut members. This progress bar is designed to display progress during automated operations, such as running a sequence of queries. When triggered, the bar visually moves to reflect a percentage of completion. The progress bar consists of a simple form with two text boxes. The background is a gray text box, and on top sits a second text box that changes size to visualize completion. This part is explained in more detail in my extended cut for members, but I will also walk you through the basics here. After importing the progress bar form into my current database, I switched to Design View to examine its components. The "front" text box slides across the background as the completion percentage increases. The code controlling it is straightforward: you send it a percentage value from zero to one hundred, and it adjusts the front bar's width appropriately. It also displays the current percent complete as text. Once the progress bar objects and code were placed in the new form, I added a button to help test the progress bar. When clicked, it simply displayed a static 50 percent completion. This allowed me to verify that the bar was functioning by adjusting the width and updating the label correctly. One important advantage of building your own progress bar with native Access controls is that it avoids reliance on external ActiveX controls. I prefer using features like text boxes and labels since they tend to be more stable and will not suddenly stop working after an Office update. Native controls are much more reliable, especially if you plan on sharing your database with others. If you are new to Visual Basic for Applications, I highly recommend checking out my Intro to VBA video available on my website and YouTube channel. It will give you a solid foundation to build on before you go further. With the progress bar working, the next task was to calculate completion based on how many fields have been filled in. The approach was to count how many of the five key fields in the form were not null and calculate a corresponding percentage. I created a custom subroutine for this calculation, which adds up each field if it contains a value, then divides by the total number of fields to generate a completion percentage to feed into the progress bar. Initially, I linked this calculation to a button so I could test the math. For example, if only four of the five fields were completed, the progress bar would show 80 percent. Filling all fields resulted in a full 100 percent. Of course, manually clicking a button every time a value changes is not ideal. What you want is for the progress bar to update automatically as the user moves from one field to the next. To accomplish this, I used the AfterUpdate event for each data entry field. Whenever the user changes the data in any of these boxes and leaves the field, the CalcPBar subroutine recalculates and updates the progress bar automatically. Setting up these events is straightforward, though each field does need its own AfterUpdate event calling CalcPBar. Fortunately, this only has to be done once for each field, and after that, all further logic is handled inside CalcPBar. Proper formatting and indentation in your code make it much easier to manage and debug your database. When reviewing submissions from others, unreadable code tends to be poorly maintained. Therefore, taking care to keep your code clear and organized will save you time in the long run. After the progress bar logic was working, I removed the temporary test button from the form since it was no longer necessary. I also added "Option Explicit" at the top of my code module to require explicit declaration of all variables, minimizing mistakes from typos or misnamed variables. One important detail is to ensure the progress bar updates correctly when you open the form or move between records. To do this, the CalcPBar routine should also be triggered by the form's OnCurrent event. That way, whenever a new record is loaded, the progress bar immediately displays the current completion percentage for that record. With this setup, as you change records or edit fields, the progress bar updates live. Deleting a value reduces the percentage, and entering data increases it in real time. For those interested in advanced techniques, in today's Extended Cut, I show how to automate the tallying of fields using a loop in VBA. Instead of manually specifying each control to include in the progress calculation, you can loop through controls marked a certain way—such as by using the Tag property—to dynamically calculate completion. This makes it much easier to manage your form as fields are added or removed, since you do not have to manually update your calculations every time. Additionally, I demonstrate how to display the completion percentage directly in your list form, add some conditional formatting features, and visually highlight completion status with colors based on progress. If you want access to the extended cut and all my other member-exclusive content, you can become a Silver member or higher. Membership details are available on my website. Members gain access to all extended cut videos, live sessions, and more. Remember, the basic TechHelp videos will always remain free as long as you keep watching. For complete, step-by-step instructions on everything covered here, visit my website at the link below. Live long and prosper, my friends. Topic ListAdding a progress bar to an Access formCopying progress bar controls between forms Understanding how the progress bar works with text boxes Implementing the progress bar with Visual Basic code Calculating form completion percentage based on non-null fields Creating a CalcPBar subroutine for calculation Using TotalFields and Finished variables to track input Updating the progress bar automatically with AfterUpdate events Adding AfterUpdate events to multiple controls Applying Option Explicit in your VBA code Using the Form's OnCurrent event for progress updates Ensuring the progress bar reflects record navigation and form load Resizing progress bar controls without breaking functionality |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access progress bar, large form, percent complete, progress meter, tag property, for each, loop through controls PermaLink Progress Bar Form Completion in Microsoft Access |
