|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Progress Bar By Richard Rost 
6 years ago
Display a Custom Progress Bar In this Microsoft Access tutorial, I will teach you how to use the Microsoft ActiveX ProgressBar control that comes with Access. I don't like using external controls, however. So, I'll teach you how to create your own custom progress bar using a text box. Melissa from San Diego, CA (A Learning Connection Member) asks: I have a monthly report that I run which requires a series of action queries, imports, and exports that takes forever. Is there any way I can show some kind of a progress bar so I know how far along it is? MembersI'll show you how to create a cool-looking 3D progress bar with shadowing, the percent complete, and an ABORT button to cancel the loop.
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! Links
Update
IntroIn this video, I will show you how to create a progress bar in Microsoft Access, including how to use the built-in Microsoft Progress Bar ActiveX control and the limitations of using ActiveX controls, especially when sharing databases. I will also demonstrate how to build your own simple progress bar using just a text box and some VBA code, which is a practical alternative for most Access applications. You will learn how to update the progress bar for multi-step processes and simulate activity with code, as well as tips for formatting and customizing your progress bar.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am Richard Rost, your instructor.In today's video, I am going to show you how to set up a progress bar in Microsoft Access. Plus, I am going to show you an alternative to the ActiveX Microsoft Progress Bar Control. Today's question comes from Melissa from San Diego, California, one of my Learning Connection members. Melissa says, I have a monthly report that I run which requires a series of action queries, imports and exports that take forever. Is there any way I can show some kind of a progress bar so I know how far along it is? Well, Melissa, Microsoft actually bundles a progress bar control that comes with Access. It is what you call an ActiveX control. It is not built into Access. It is not part of the normal control box. You see your list of tools in there, but it is an external control. Now, there are good parts of that and there are bad parts. The good part is that it does not all clutter into Access. There are tons and tons of add-ons you can use with your Access database, but they are external controls. They are outside files. They are not built into Access. They are not native application controls. The downside comes when you distribute your database. If you give it to someone else and they do not have the same control or do not have the same version of the control that you do, you run into problems. I have even run into problems with ActiveX controls upgrading from one version of Access to another. I upgraded from, I believe, Access 97 to Access 2000 back in the day, and I had a few ActiveX controls in there and they stopped working correctly because Microsoft upgraded Access but they did not upgrade the ActiveX controls that came with it. There were new ones to replace them but they did not behave quite the same way. I have had problems with this in the past. Because of that, I tend to shy away from using ActiveX controls and there are a lot of great ones in there. There is a whole big gigantic toolbox of them and I will show you a few of them in a second. But I try not to use ActiveX controls. Maybe for a database that only you use, go ahead, especially the progress bar control. It is kind of harmless. It is not that bad. But some of the other ones that I have used, I have had lots of problems with, especially when I try to give my database to other people. I stay away from ActiveX controls. But in this video, I am going to show you how to use the progress bar ActiveX control and then I am going to show you how to build your own version of a progress bar control using just a text box, and we will draw Xs across it, which is good enough for most applications. Then, for the members in the extended cut version, I will show you how to make a pretty one that involves a couple of text boxes and some shadowing and some other effects. But let us take a look at this guy first. All right, blank Access database. Let us create a form. All right, it does not have to be this big. Let us shrink it up a little bit. For those of you who know my style, I have to put a little bit of background color in there. Let us go with, let us go to Flight Purple. Now, the point of a progress bar is that you are going to run some process, like Melissa said, and you have got, let us say, 15 action queries that run. You have to do an import, a make table query, an update query, an append query. You have got like 15 steps, and while it is going on, you will see the little progress bar down here move. When you are inside of a query, look, if you have got an action query that is going to run and it is going to update 10,000 records, you will see that little progress bar go across. There is nothing you can do about that. You cannot control what happens while a query is running. That is just a native Access problem. But between those queries running, if you have got 15 or 20 different queries that run a whole bunch of steps, you can increment that progress bar yourself. Now, it is going to involve some programming, unfortunately. There is no way around that. So, you need to use a little bit of VBA, but I am going to show you how to do it. It is pretty easy. For my full developer students, I am going to include this in class two, and we are going to actually use it with a record set, because we are studying record sets right now in developer 18. I am going to show you how the progress bar is a great tool to use when you are running through a very long record set. If it has to update 10,000 records while it is looping, we will have that progress bar go across. We will do that last. So, where do we find this progress bar? Let us go up to our design and open up the toolbox here. Now, it is not one of the built-in native Access tools. You have to come down here to find ActiveX controls. This is a list of ActiveX controls. They are outside controls. ActiveX is a technology that Microsoft used to make basically little plug-ins. You can insert them into Word or Excel or Access or Visual Basic. There is Business Bar, Button Bar, Adobe Controls, and the last third-party stuff in here. All the Microsoft stuff starts off with Microsoft - Microsoft Forms, Image Controls, Outlook stuff. There is all kinds of stuff. Now, the problem with ActiveX controls, and if you do not believe me, Google "Problems with ActiveX Controls." There are a lot of them. I do not like them, especially for a database you are going to be distributing. Even on your own network, you can run into problems. This is the one we are looking for right here, by the way, the Microsoft Progress Bar Control version 6. But with ActiveX controls, you have got to make sure you get the same version of the control on all machines, same version of Access, and the same version of the Progress Bar Control. They can become unregistered. I have seen that happen. When you distribute a database, let us say you put the Progress Bar Control in your database and you send it to someone else. You have to do special tricks to get that Progress Bar control to register on that machine. That is really weird. I will be completely honest. I have not done a ton with ActiveX controls in about 10 years because they were incredibly unreliable 10 years ago. When Office 2007 was out, 2010, I had lots of problems with ActiveX controls, and they are not really secure. These are technically external files and they can be the target of attacks. Sometimes attackers go after ActiveX controls because they can modify the control, which now has access to your database. So, I just do not like them. There are problems with them. But it is simple and it is easy to use, so I will just show you how to use this one. Here is the control when you bring it in. Nothing special. Just drag it across your form like that. That is more of what it is supposed to look like. If you open up the properties, double-click, you get a special form. Usually, ActiveX controls have a special form if you double-click on them. You can set the min value and the max value, so it goes from 0 to 100. You can change that if you want to. I like that because I use it for percentages. We are going to do that with the custom progress bars that we create - we are going to use 0 to 100. You can change the mouse pointer, what happens when you hover over it. You can change the border style, the appearance, all this different stuff. That is OK. Hit OK. I am going to rename this guy. I am going to right-click on it and go to properties. It is progress bar 0. I am just going to call it PB. Let us call it PB1 because we are going to make a couple of different ones. PB1. There is some other custom stuff in here, OLE class, class, verb, all these things. Do not worry about it. We are not going to do a ton with ActiveX controls. I do not like them. Have you gotten that impression that I do not like ActiveX controls? But it is real simple to use. So, I am just going to close this now. So, we have got PB1 right here. In fact, just so I do not forget, I am going to throw a little label next to it right here. This is PB1. That is the name of it. I will make it black. Let us drop a button on here, and I will just show you how you control this thing real quick. Cancel that. Go. Let me resize everything a little bit here. Save this form. We will just call it form1. It is fine, we are not going to use this for anything. Right-click, build event. I will bring up your code builder. In here, I am just going to say PB1 = 50. Save that. Let us open up the form. Click the button. Boom. There, it is at 50. See how that works? All right. PB1 = 80. Boom. Now it is at 80. If you want to have this thing climb upwards - dim X as Integer, for X = 1 to 100 or 0 to 100, whatever you want to do. PB1 = X. And then Next. Save it. Click. It goes by really fast. It is done. But presumably, you would have other stuff in here. Let us simulate other stuff here. I will come up here and I will make a Sub, OtherStuff. What are we going to do in here? We are going to make the computer sleep. Now I am going to open up the Code Vault. I am going to give you guys some source code out of the Code Vault. I am going to make this page for free. Here is the URL for it: 599cd.com/sleep. I will put a link in the description below the video so you can just click on it. That will take you to this page in my Code Vault. I am working on something called the Code Vault. It is not ready for prime time yet, but I am loading this up with all the different source code that I create in all of my classes. I am going to make this available for a small monthly fee eventually, but it is not ready yet. Some of the stuff is free. Some of it will be by subscription, like the more complicated stuff. This is actually something that is really easy. All you really need is one line of code. Depending on what version of Access you have, 32 bit or 64 bit, that is what this is right here. I really hate the fact that they split Access up into 64 bit and 32 bit and the source code does not work between them, which really irritates me. A lot of stuff that I built years and years ago still runs just fine, but only under 32 bit Access. Then Microsoft came out with 64 bit, and none of it is backward compatible. I am like, why would you do that? Literally, all it is in most cases is just putting one line of extra keyword in there: PtrSafe for a lot of stuff. But this will work right here. So, copy all this stuff right here or type it in if you want to. You really only need, if you are running 32 bit, that line. We do not need SleepSec. SleepSec is a Sub that I wrote that you can send it a number of seconds, like SleepSec 5, and it will sleep 5 whole seconds. Because Sleep by itself runs in milliseconds. But just grab this whole thing if you want to - just copy this whole thing. Then come on back over to Access and we are going to create a module - create a module. Just paste that code in that I gave you. If you want to type it in, type it in. Again, you really only need that line of code, the Sleep line. Do this one if you are running 64 bit. Save that. Hit yes. We will just call this MySleepMod, the sleep module. It has to be out here in a public Sub. Now let us go back over to our other code. I just closed the other window. This is a multi-document interface, kind of like Word documents. We can have multiple documents open inside Word, or at least that is how it used to behave. It does not behave that way anymore. But now, for our sleep stuff, our other stuff, we can have it Sleep. Let us have it Sleep 500. That is half a second. 500 milliseconds. A thousand milliseconds are in a second. Now, when I hit Go, you can kind of see the progress bar. Oh, there it goes. See? Now it seems to be going slowly, and notice how I have got this Not Responding up here because we do not have any kind of DoEvents in here. That is why it is taking its good old time. This brings up something interesting. One of the problems without including DoEvents in your loops is that after three to five seconds or so, Access thinks that it is not responding and it kind of freezes up. So, this progress bar is actually moving back here, but you really cannot see it because Access thinks it is blocked up. But I guarantee you, eventually it is going to hit 100 and stop, and Access will regain control. Yeah, it is even going white now. OK, there it came back. Let us clean this up a little bit. So, in my OtherStuff, I am going to put a DoEvents. In any loop, you should always have DoEvents. Otherwise, it will seem like it is frozen when it is not. That is just good to do. I have it inside the loop here, or inside the OtherStuff, whatever you want. Now, the reason why it appeared to crawl is because this is not necessarily a hundred of these blocks across. There are maybe 30 blocks across. So, let us increase that step. Let us go 1 to 100, Step 10. That means it is going to count up by tens. Go. See? It is moving across faster. There is your basic progress bar. Now, at the very end, it might have stopped at 91. At the very end of your loop, you might want to do something to make it, like, say, PB1 = 100 at the very end here, because this loop, if it is stepping up by 10, might not stop exactly at 100. It is going to go 11, 21, 31, see what I am saying? Now, by the time it is done, it will hit at the end eventually. Now we are at 100 and we are done. Maybe pop a "done" message box up here or something. Want to make it seem even more realistic? Check this out. Instead of Sleep 500, let us make it random. int(RND * 100). In other words, it could be a number from 1 to 100 milliseconds. So, anywhere from 1 to 100. Go. That is a little bit fast, but you can see how it kind of staggers a little bit. Let us increase that a bit. Let us make that 500. Go. See? That is more like it. That is how a progress bar behaves. Access will do a little bit and then chug and then do a little bit. Now, you are not going to obviously have this running inside of a loop like this. You are going to have other stuff going on. So, if you have, let us say, a bunch of imports and queries running in the background, you are going to have yours being like this. Forget this loop for a minute. I am just going to REM this stuff out because I want to use this for the next couple examples. You have got real stuff happening. You might have, here, query one is going to run. Then, after query one runs, let us throw in other stuff in there. Assume that is running. So, OtherStuff, query one runs. Then you are going to say right here, maybe PB1, if you have five queries that are going to run, you know there are five of them. So, every time a query is done, set the progress bar - set it at 20, 20 percent finished. PB1 = 0 to start off. Now, query two runs, then PB1 = 40, then query three is going to run. Each one of these lines is where you will do your stuff, by the way. I am just putting OtherStuff in here to simulate a query running. Let us say you know query three takes a long time to run. So, OtherStuff, and then OtherStuff again, and now PB1 = 80 percent done, and so on. You get it. One more query for OtherStuff, and then PB1 = 100. So, I am simulating my different queries running, and you just set these wherever you want to put the progress bar at. When I hit Go now, same. That is all you need to do, unless it is an actual loop with a number of records, which I will cover for developer students later. This is basically how you have your progress bar run for any number of known events. Again, if you have got a big long query and it is going to have that query thing happening down here, there is nothing you can do to get around that. That is part of Access. But at least if you have multiple events running, you can simulate something like this. Let us get rid of this stuff. I just wanted to show you how you would logically and realistically do this. Let us turn this stuff back on. I have also seen progress bars be used on a form to indicate that you filled out this many fields on the form. You have got a big form with a lot of questions in it and you want to visually cue the user, "Oh, you finished this many questions on the form or on your test. You are this far done with the application." You can use the progress bar that way, too, just after Update events in your different fields. Now, I showed you how to use the Progress Bar Control that comes with Microsoft Access, the Progress Bar Control. It is ActiveX. I do not like it. Let me show you how you can build your own using just a text box. Here is a text box. Let us call this one PB2. OK. Resize that label. There we go. Place it right there. Let us make it black, and I am going to size this guy out about the same. Set whatever colors you want. If you want to make this guy gray, bold, whatever - however you want to pretty it up. Now, what we are going to do basically is fill this up with a number of Xs or whatever character you want, stars, or whatever. I tried finding a fun ASCII character to use in there. Xs and stars work better. Let us name this guy PB2. The first thing you have to do is figure out how many characters across this guy is going to be. It will change based on how you format this. I am going to use Xs. So, I am going to count them up: 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 30 1 2 3 4 5 6 7 8 9 40 1 2 3 4 5 6 7 8 9 50 1 2 3 4 5 6 7 8 All right. So, 58 across. So, that is my number. Remember 58. Let us go into our code now. Build event. Now, right here, I am going to create a Sub. I am going to call this PBText and I am going to send to it X as Integer, so I can call PBText just like I was setting PB1. I am going to send PBText what number I want to be in there. Now, we know it is 58 across. So, Const MaxSize = 58. I am setting a constant. Not "constant," Const. That is not a variable. It is a constant. It will never change. I am going to Dim a temporary variable Y as Integer. You will see why in a second. Get it? Ha ha. You will see why in a second. Y = Now I want to figure out what percentage. I am going to send it a number from 1 to 100 just to be easy. That 58 might change if I redesign the form a little bit. Make the text bigger or change the character. So, I want Y to represent what percentage I am done. When I call this with X, I want X to be the percentage. In fact, if you want to make this easier, PercentComplete. I like to, even though that makes things longer up here, I like to make this represent what I am sending it. Because if you are in a different Sub and I say PBText, as soon as I do that I get the helper. Now that tells, oh, X does not do me much good. So, we will make that say PercentComplete. If you want to make it shorter for inside your code, that is fine too, but that is not a big deal. So, Y = Int(PercentComplete / 100 * MaxSize). If MaxSize was 100 and I sent it 100, it would be 100 / 100 * 100, which is 100. If I send it zero, it is zero / 100 * 100 = 0. So, this just calculates this into a percentage. If we are half done, 50, then 50 / 100 is one half times MaxSize. That is basically converting that into a percentage. Now I know the percentage. Int will chop that fractional part off, so now I have just got it. If it comes up to 49.642, it will just be 49. Now that I have the integer percentage that I am complete I can say: PB2 = String(Y, "X") String is a function that says give me X number of whatever character I want. So, if I say 50 and then X, I get 50 Xs in a row. That is what the String function does. Let us see if it works. Sure. I am pretty confident it will work. Not 100 percent positive. I can have percentage, get it? All right, enough of my puns for today. Go. And nothing. Oh, I did not call it. I wrote the Sub, but I did not call it. So, down here in my loop, I am going to say PBText X. Now I can see it is PercentComplete. I am not setting it equal to the progress bar, because the progress bar is an actual control that has a value. This is a Sub that I am calling, so it is slightly different. So, I got my code in there now in the loop. Ready? Go. Beautiful. There. Now, it stopped just shy. I wonder why. Let us see here. Did I count this out right? Oh, we have the same problem - our loop stopped at 91. So, down here, when this loop is done, because this loop is not perfect, we can say PBText 100 when you are finished. Save that, and now let us run it. There, it is finished. Maybe put a little percentage at the end over here, what percentage we are at. Let us do this. Up here, when we draw the string, after that, put a space, then X, the number, then a percentage sign, like that. Let us see what that does. It is probably going to shrink up our MaxSize. I want to guess maybe 55. Let us try it now. Go. Oh, variable not defined. What did I not define? Oh, X. I did not make it X, I made it PercentComplete. I had X on my notes. OK. Go. Do that. Beautiful. A little bit too far at the end, maybe let us go 53... Go. There is your little progress bar. It shows you as it is going across. If you do not like Xs, change the character. Maybe do a dash. Of course, you might have to now change how wide it is. The X character is much wider, or go with a font that is unispaced, like Courier. Format, like Courier... where is it? There it is. Courier New, down here. These are unispaced fonts, also called monospaced fonts. So whatever character you use, it is the same width. In a proportional font a W is wider than an I. With monospaced fonts, every character has the same width. I use those for coding all the time. So if I run it now, of course, I need to adjust it. We are way overboard. Let us change this down to 40. It is just trial and error sometimes. You just guess. You can count it out, but a little bit bigger. Once you get it, though, you will have it for all the characters. That is not bad. 46 should be perfect. Now, if I change it to an asterisk, it should still give me the same width. Oh, the asterisk looks nice. I like that. So, there is your progress bar control and your text progress bar. Want to learn more about progress bars? In the extended cut for members, I cover making this fancy looking 3D text box bar, which is really cool looking. Much nicer than this one. This one is OK, but this one looks a lot better. I will show you how to make an abort button, so if you are, let us say, 81 percent through with a long process and you realize something is wrong, you can abort out of it without having a Control Break or exiting out of your code that way, and you can gracefully exit the loop. I also cover a lot more with progress bars in my full Access Developer Level 18 class, where I will also show you how to use this progress bar with a record set, which is something that we are studying in our developer classes. 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 will see a list of all the different membership levels that are available, each with its own special perks. But do not worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free. If you enjoyed this video, please give me a thumbs up, and feel free to share it wherever you think it might help people who are interested in Access. Make sure you subscribe to my channel, which is completely free, and click on the bell icon to select "All" to be notified every time I post a new video. YouTube no longer sends out email notifications when new videos are posted. If you would like to get an email every time I post a new video, click on the link below to join my mailing list. Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access. It is over three hours long and you can find it on my website or on my YouTube channel. I will include a link below you can click on. If you like level one, level two is just one dollar, and that is free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. If you have a specific problem you need help with, or you would like to discuss having a database built for your needs, I do offer one-on-one consulting. Be sure to follow my blog, and find me on Facebook, Twitter, and of course YouTube. Once again, my name is Richard Rost. Thank you for watching this TechHelp video brought to you by AccessLearningZone.com. I hope you enjoyed this video and you learned something today. I will see you again soon. Bye. QuizQ1. What is one major disadvantage of using the ActiveX Progress Bar Control in Microsoft Access?A. It is difficult to program B. It is only available in Access 97 C. It may not work when distributing the database to users who do not have the same control or version installed D. It is slower than other controls Q2. Why does Richard prefer to avoid using ActiveX controls in distributed Access databases? A. They make the database files very large B. They can be unreliable and incompatible across different versions and machines C. They require expensive licenses to use D. They are only compatible with Mac OS Q3. If you have a process with multiple action queries, imports, and exports, how can a progress bar be useful? A. It automatically optimizes your queries B. It shows you how far along the process is C. It increases processing speed D. It prevents errors during execution Q4. What programming language must be used to update a progress bar during processing in Access? A. JavaScript B. HTML C. VBA (Visual Basic for Applications) D. SQL Q5. What is a safer, alternative way to create a progress bar in Access without using ActiveX? A. Using only a Label control B. Using a Text Box and filling it with repeated characters like Xs or stars C. Using a ComboBox control D. Installing third-party add-ons Q6. How can you make sure the progress bar does not appear frozen during a long-running loop? A. Add a MsgBox inside the loop B. Use the DoEvents statement inside the loop C. Decrease the number of records processed D. Avoid using sleep functions Q7. When using the ActiveX progress bar, what must you do after distributing the database to other users? A. Nothing; it works automatically B. Make sure the control is registered and the same version is installed on their machines C. Upgrade all client computers to Windows 11 D. Convert the database to SQL Server first Q8. For the text-based progress bar method, what should you consider when determining how many characters wide it should be? A. The version of Access B. The type of data being processed C. The width of the text box and choice of font (monospaced vs proportional) D. The number of database tables Q9. What Microsoft Access property should you set to determine the percentage of progress shown in a text-based progress bar? A. The "Caption" property of a label B. The number of Xs or characters to fill, based on percentage complete C. The font size only D. The tab order of controls Q10. What function is used in VBA to generate a string of repeating characters (like Xs) for the text-based progress bar? A. Repeat() B. String() C. Fill() D. Concat() Q11. What could happen if you do not include DoEvents in a loop while updating a progress bar? A. The progress bar will update too quickly B. Access may become unresponsive and appear frozen C. The database will automatically close D. The text-based progress bar will not be displayed Q12. What is an advantage of using a monospaced (fixed-width) font for your text-based progress bar? A. It increases database speed B. All characters have the same width, making the bar visually consistent C. It protects against security vulnerabilities D. It adds color automatically Q13. If you have five queries in your process, how should you update the percent complete for the progress bar after each query runs? A. Set the value to 5 percent each time B. Set the value to 100 percent after all queries C. Increment the value by 20 percent after each query D. Use random values Q14. What is a possible risk of using ActiveX controls in databases, as mentioned in the video? A. They improve security too much B. They can be a target for malicious attacks C. They cannot be deleted from a form D. They require no configuration Q15. What does the Sleep function demonstrated in the video do within the progress bar code? A. Pauses execution for a specified amount of milliseconds B. Increases CPU speed C. Clears the progress bar D. Deletes temporary files Q16. Why might the ActiveX progress bar control not fully display 100 percent when stepping through a loop? A. The loop steps may not end exactly at 100, so a manual update after the loop is necessary B. It is a bug in Access C. The control is not resizable D. Only works with Text fields Q17. In the text-based progress bar example, why did Richard adjust the MaxSize constant? A. To match the number of queries in the process B. To fit the width of the text box and accommodate additional text like the percentage C. To make the bar change color D. To sync with the ActiveX bar Q18. What is one reason Richard prefers the text-based progress bar over the ActiveX control? A. It is visually more impressive B. It is more compatible for distributing databases without external dependencies C. It requires .NET framework D. It only works with SQL Server Q19. Besides showing progress during queries, what is another use for a progress bar Richard mentions? A. Displaying current user name B. Indicating completion status of fields on a large form C. Highlighting required fields D. Listing all tables in the database Q20. What is the main benefit of using DoEvents within a loop updating a progress bar in Access? A. It speeds up the loop B. Allows Access to process other events and keeps the UI responsive C. Decreases database file size D. Prevents Access from saving records Answers: 1-C; 2-B; 3-B; 4-C; 5-B; 6-B; 7-B; 8-C; 9-B; 10-B; 11-B; 12-B; 13-C; 14-B; 15-A; 16-A; 17-B; 18-B; 19-B; 20-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 covers how to set up a progress bar in Microsoft Access, as well as an alternative method to the standard ActiveX Microsoft Progress Bar Control.A commonly asked question is how to display a progress indicator for processes like lengthy monthly reports that involve multiple action queries, imports, and exports. This would help users track how far along the operation is as it runs. Microsoft Access includes a progress bar option, but it is provided as an ActiveX control. This means it is not part of Access's built-in tools, but rather an external add-on. There are advantages and disadvantages to using ActiveX controls. The benefit is that Access does not become overburdened with rarely used tools, but the controls themselves are not native to Access. They exist as external files and are included separately. The main drawback emerges when you share your database with others. If recipients do not have the same ActiveX control version, or if there is a version mismatch, functionality problems can occur. For example, upgrading from an older Access version like 97 to something newer, ActiveX controls may not function properly if the new version does not support the previous ones. This kind of compatibility concern is frequent and can disrupt the distribution of your databases. Because of these issues, I prefer to avoid ActiveX controls unless the database is solely for my use. They can work fine in those cases, such as with the progress bar, but for any project intended for distribution, the potential problems grow. I have experienced many headaches with ActiveX controls in shared environments, so I recommend caution. In this tutorial, I first explain how to implement the Microsoft ActiveX Progress Bar Control and then demonstrate how to create a custom, simplified progress indicator using just a text box that visually represents progress by drawing Xs across its width. This approach is often sufficient for most users' needs. Also, for members, today's Extended Cut will go a step further and walk through creating a more visually appealing progress bar using multiple text boxes and various effects for a polished look. Let me start with setting up a form in a blank Access database. For clarity and a little visual interest, I add a light purple background. A progress bar is useful in situations where you are executing a series of steps, such as several action queries or imports that run sequentially. For example, if you have 15 steps to complete, a progress bar provides a visual cue as each step completes. It is important to note, however, that Access does not allow you to control the native progress bar displayed during a long-running single query. That progress bar cannot be customized. However, between separate queries or processes, you can manually increment a custom progress indicator as each part completes. Implementing a progress bar means you will need to use a little bit of VBA coding. There is no way around that, but the process is not particularly complex. For those already enrolled in my full developer courses, I show how to use a progress bar with recordsets, which is especially helpful when looping through thousands of records. This technique is covered in Access Developer Level 18. The first method involves using the ActiveX progress bar control. To add one, you access the list of ActiveX controls in the toolbox rather than using the native Access tools. ActiveX controls were designed by Microsoft to be plug-ins that can be used not only in Access but also in other Office apps and Visual Basic. The available controls include business bars, button bars, and many Microsoft or third-party options. Problems with ActiveX controls are well-documented. Issues range from compatibility and registration failures to potential security risks, since attackers might target them. I have personally experienced reliability issues, especially when moving between Access versions or different computers. Despite these concerns, I briefly demonstrate how to add the ActiveX progress bar to a form. Once you bring it into your form, you can adjust its properties, such as the minimum and maximum values (typically 0 to 100, which works well for a percent-complete display). You can tweak colors, border styles, and other appearance options. I recommend renaming the control to something recognizable, such as PB1, since you may have more than one. To actually use the control, you set its value property in code. For instance, setting PB1 to 50 will fill the bar halfway, and setting it to 80 will move it further. To demonstrate a running progress bar, I show how to use a simple loop in VBA that increments the value, simulating progress. If you add a time delay in the loop, you can visually see the bar move. For delays, there is a handy bit of code that pauses execution for a given number of milliseconds. For convenience, I've made available a free code snippet that handles this pause, supporting both 32 bit and 64 bit versions of Access. Just add this code to a module in your project. Placing this pause in the loop lets you see the progress bar move from start to finish. However, you have to be aware that, without including a DoEvents call in the loop, Access may go into a "Not Responding" state (even though it is still processing). Inserting DoEvents into your loop prevents Access from freezing and keeps the interface responsive. You can also experiment with different time delays or random values to make the progress bar appear less mechanical, similar to what you would see during real-world operations. In many practical use cases, you will not have simple loops. Instead, you will be executing a series of action queries or processes. In these scenarios, you can set the bar to indicate rough percentage completion after each distinct step. For example, if you have five steps, set the progress bar to 20 percent after the first, 40 percent after the second, and so on. If a particular query is especially slow, you can add additional delay or visual feedback as needed. Progress bars can also be used to track form completion, guiding users as they fill out multiple fields. After each relevant field is updated, simply increment the bar, giving users a clear view of how much is left. The next approach is to build your own progress bar entirely within Access, using just a standard text box. This avoids all the compatibility headaches of ActiveX. The idea is to fill the text box with as many repeated characters (Xs, asterisks, or any desired symbol) as needed to represent progress. Start by sizing the text box and counting how many characters fit across it at your chosen font and size; this gives the "full" width. In the code, create a subroutine that calculates the portion of the bar to fill based on a passed-in percentage. For clarity, use a descriptive parameter name like PercentComplete. Use the String function to generate the correct number of repeated characters. For example, if your box holds 58 characters and you are 50 percent complete, fill 29 characters. Optionally, you can also display the numeric percentage right next to the bar for even more feedback. It may take a little trial and error to line up the characters, depending on the font size and style chosen. Using a monospaced font like Courier New ensures each character occupies the same width, keeping the bar visually neat. If you prefer a different look, simply change the fill character or adjust the font. This custom text box approach is easily tailored to your application's needs. In the Extended Cut for members, I explore creating a more sophisticated progress bar effect that looks three-dimensional, using text boxes with shading and additional effects. I also cover how to add an abort button that gracefully exits a long process if needed, and ways to use these techniques with recordsets, as covered in Access Developer Level 18. To watch the complete tutorial, including all steps and detailed code explanations, visit my website at the link below. Live long and prosper, my friends. Topic ListExplanation of ActiveX progress bar controls in AccessHow to insert an ActiveX progress bar into a form Setting ActiveX progress bar properties (min, max values, appearance) Naming and referencing the progress bar control in VBA Controlling the ActiveX progress bar value with VBA Looping to increment the progress bar value Simulating time delays using Sleep API in VBA Adding Sleep API code for both 32 bit and 64 bit Access Using DoEvents in VBA loops for UI responsiveness Simulating progress for multiple sequential tasks Updating the progress bar after each query or step Building a custom progress bar using a text box Calculating progress bar fill based on percentage Using the String function to display progress in a text box Adjusting text box width and font for consistent progress display Displaying percentage complete alongside the text progress bar Choosing and formatting monospaced fonts for text progress bars |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access progress bar, status bar, progress meter, percent complete PermaLink Progress Bar in Microsoft Access |
