Status Box
By Richard Rost
4 years ago
Display Messages on your Forms. Great for Loops.
In this Microsoft Access tutorial, I will teach you how to create a Status Box textbox control along with a VBA function to easily display information for users. You can use it on form startup, or any time a loop is running. Use it so the user doesn't think your database has locked up. I'll show you how to add color to it, rich text formatting, and more.
Pre-Requisites
Recommended Course
Gold Members
More StatusBox Related Videos
Links

Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, MsgBox Function, status, statusbox, MessageBox, message box, status bar, message, user info, dialog, information screen
Subscribe to Status Box
Get notifications when this page is updated
Intro In this video, I will show you how to add a status box to your forms in Microsoft Access to display messages and updates for your users. You'll learn how to build the status box, control it with VBA, and use it to communicate the progress of long-running processes, display calculated values like recent sales totals, and even add color and formatting with rich text. I'll demonstrate practical examples, including tracking progress in loops and colorizing messages based on results. This is a quick way to keep users informed about what's happening in your Access database.Transcript Welcome to another FAST TIPS video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to talk about a status box - using a status box on your forms to display messages in your Microsoft Access databases. If you've been watching my videos for any length of time, you've probably seen this little gray box right here on the main menu. This is what I call the status box.
Now, the status box can be used to display pretty much any kind of message that you want, and here this button gives you an example. See, hello world, that's all it does. It puts hello world in that box. Nice and simple. I use this thing in a lot of different videos to show what's going on while the code is running or while some process is going - whatever. I like it better than using message box and there are some benefits too - we'll talk about those in a minute.
I show how to build this thing in my customer contacts video where I show you how to build the contact manager, but you have to watch like 20 minutes of this one to get to that, and this follows my blank template video where I show you how I build the TechHelp free template from the ground up, and that's like 20-25 minutes long. So, when people ask me about the status box, I have to point them to these videos, and that's like 40 minutes of video they have to watch just to get to the status box.
So I'm putting this video together for two reasons. First, I want a quick video I can say, hey, go watch this FAST TIP video. It'll show you how the status box works. We're going to get to that real quick. The second thing is we're going to have some fun with it after that.
I'm going to show you how it works again real quick, and then we're going to do some embellishments. We're going to add some color. I'm going to show you how to display some really cool information in them. So sit back, relax, and here we go.
If you're new and you haven't watched the other videos, the status box is basically just a text box, and I color it gray to indicate to the user that they can't type in here. I mean, they can, but this data doesn't go anywhere. It's for display purposes only. You can lock it if you want to, but why? I use a little Visual Basic function to control what gets written to this box. So yes, if you're new to VBA, go watch this video first, it'll give you the background you need. I'll put links to all this stuff down below in the description under the video - you can click on it.
What we do is we create a text box and we put a button on the form. In this button, right-click, build event. That brings up my VB code editor right here. I have private sub hello world button and then I say status "hello world" just like message box. With message box, message box pops up a window up in your face with an OK button on it, or it can be OK, Cancel, or Yes, No, or whatever you want. Generally, it's just OK. The problem is in order for the database to continue doing stuff, you have to click OK. So, if you want to put a message up on the screen that the user can see but not stop Access from doing stuff, like if it's on a loop, you don't want to use a message box.
That's why I wrote the status box function. So what does status do? Status is up here: private sub status. It takes in one bit of information as a parameter, as a string - whatever you send to it. It says, okay, status box, which is that text box on the form (this guy over here - that's status box). You set that equal to s, which is whatever string I just sent into it, and then follow that with VB new line, which is basically a new line. It's a new line/carriage return pair, but it basically moves down to the next line, like if you're in a text editor and you press enter, and then tack onto the end whatever status box used to have in it.
The effect is whatever goes in first goes on top. That's just easier to code. See, it comes on top; the next one will come on top. If I put some extra stuff in here, watch, the next one comes on top. So the most recent stuff is always on the top of the box. It's the easiest thing to code. Otherwise, you have to have the box scroll down to the bottom, which I don't like. It causes the screen to flash. Your users just get used to it. The stuff on the top of the box is the most recent.
If you don't know what this stuff is, it's called string concatenation. That's just putting multiple strings of text together, and then the VB New Line and then the status box. If you don't know how that works, go watch my concatenation video.
The next line is DoEvents. What does DoEvents mean? If you're in a loop, if you're doing a bunch of stuff, nothing else will happen except what is programmed in your loop. The screen might not even update. Especially if you try to cancel it, if you process any keyboard clicks, it won't process any mouse clicks until the process is over.
So, if you have a big long loop and you want to status a bunch of stuff as it's running, you need to have DoEvents in there. DoEvents just lets the processor take a break and say, okay, go listen for user input or anything else it might want some attention for right now, and then come back and work on your loop. I have a whole separate video on DoEvents coming up soon. There's a lot more to it than that.
So that in a nutshell is what the status box is. If you're curious as to what this thing is, it's sitting there. That's what it is and that's how you build it. You can easily take this and put it into other forms. All you have to do is copy this button, this text box, and this little function right here. Can you write one that handles all the forms? Yeah, you could. But this way, each form has its own private status sub, so it writes the text on that form. You can have it write text on other forms if you want to. That's a whole different video.
Now that I showed you how the status box works, let's see some examples of where it would be handy. Let's start off with a long series of processes. Let's say you have a bunch of queries that have to run and they take forever. I've had some queries back in my day that would take 15 minutes to run; they're pulling data across the network, it does an append query, it does a delete query - there's a whole bunch of stuff - you're importing data from a client or whatever.
Let's say, to the user, it looks like your database might be locked up if they don't look for that little tiny thing down here in the corner. Maybe give them some status updates as things are running.
So, let's say this hello world instead here is going to run some big long process that's got like 20 steps. You could say, "Beginning import now..." All right, and then something will run here, some long query. How do you do that? You do DoCmd.OpenQuery and it will run that query. I have a whole separate video coming out on this very soon, Should I do multiple queries in a row—imports and stuff, run an append query, run an update query to change things, update your prices, whatever. If you don't want to wait, I do cover that in my Access Expert Level 16 class, but I am making a TechHelp video on this too. Obviously, the course goes into more detail than the TechHelp videos, but it'll give you the basic understanding of it.
What I'm going to do right now to simulate something running is I'm going to put a sleep command here just to pause the application so it looks like something is happening in the background. The goal here is to show you how the status box function works. So I'm going to go grab the sleep timer code from my sleep video. If you don't know how this works, you can watch this video, the free code is right here. I need just this, just this guy. I'm going to go back to my database. I'm going to create a module and I'm going to drop in that one line of code. Now I can use a sleep timer. There's the sleep right there. Save this as my global module.
Let's go back over here. Now I can just, you know, something runs here, and then we'll just say sleep maybe a second. Those are milliseconds. Then, you know, beginning or step two, we'll call it. That's going to take three seconds, let's say, and then step three. The point I'm trying to make here is that while this is sleeping or while this is doing its thing, the user is seeing something happen. They don't think that the computer is locked up. You could do status "done" and then a beep maybe.
Save that. Instead of just then clicking a button and going to get coffee and they don't know what's going on, let me make this bigger real quick so you can see what's going on in here. Make this way bigger. There we go. Save it. Close it. Open it. Ready? Now the user goes to run that big long process - beginning import, step two, step three. It's a little bit too long with the delay, but you get the point. Before, they would have sat there and just watched nothing for 10 seconds and would have wondered if the computer was locked up. At least now, after each query runs or each whatever, you can see some stuff happening.
Maybe if you have a big loop - I know sometimes I used to send giant email batches with like 10,000 emails or even more, and I would want to see a status of how many have gone out yet. Let's do a fake loop here. Loops are common; I use these all the time: for loops.
So we'll change this to "sending email batch." We'll do a for-next loop in here. Again, if you've never done a for-next loop, go watch this video. It'll teach you how to do it. I'm going to show you right now, though.
Let's say you know there's going to be 100 emails. You get the same thing with a while loop if you don't know how many you're going to have ahead of time. But just for class purposes, let's set x as a long integer. I'll say for x = 1 to 100. Small email batch, then next.
Now, normally when you click go, you just won't see anything happen. I mean, your screen might update when each email sends, it all depends on how you're sending the email. And you know, sending in here is where you do your actual sending. We'll fake it like a sleep, maybe 100 milliseconds, maybe one tenth of a second - about how long it takes to send an email. And then we'll do a status: "Sending x of 100" in a loop. At the end, say status "done" and then beep.
So we're going to send 100 emails, fake sent, obviously. Here we go, go, send them all out. You see something happening on the screen. That's the whole point I'm trying to make here. As your emails are going out, your user can see; you could calculate the percentage and put that there.
One thing to note, by the way, is that updating the screen is one of the slowest things you can do. So, if you've got, let's say, 100,000 emails that are going out, you might only want to update that occasionally. Let's say you're not sending emails in the loop, maybe you're exporting records. So let's get rid of the sleep here. By the way, as I mentioned, how do you send that email? Wouldn't you know, I have a video for that too.
But let's say instead of sending stuff, you're just exporting records, so it'll be really fast or you're doing some kind of stuff. So, without a delay in there, let's jack this up to a thousand. Now go. You still see it takes a second or two because updating the screen - that's what takes a lot of time.
So you might want to use your modulus function. Modulus is the remainder after integer division. Remember that. I cover that in my odd or even video - the mod operator. Using that, we can come in here and say if x mod 100 = 0, then do the status. Oh, and I didn't mention my if then video. There's that. Don't forget that if you don't know how to do an if then.
If you don't know this already, by the way, I have to mention the other videos because a lot of people say, what is that, where do you cover that? I want to give everybody the chance to know where that stuff's covered. The benefit of the full course is I've already arranged the stuff in the right order for you and I teach it to you in a nice easy-to-digest manner. So, anyways, what this basically says is only display a status update every hundred records. That's what that means. So now it went by really fast that time, but see, it only displayed every hundredth record instead of all of them.
You can use the status box to display some startup information. For example, if you want to say, what are my sales for the month? Go look that up, and just say I want to see that every time I start the database up. I've shown how to do stuff like that before. In fact, I've got a whole dashboard form video where I show you how to calculate a whole bunch of cool stats and display them on your main menu with a little chart and everything.
Let's say you just want a quick little summary - what are my sales? - put it in the status box.
You can do a little event in here. Open this up, go to the events for the main menu. You can use the on load event or on open event, either one. You come in here and say, dim c as currency. Then say c = (what fields do we need?) We need the order table. We need to sum up the order total field and let's do the past 30 days, past 30 days of order. So order date is greater than 30 days ago. So we have order date, order total.
I can never remember these things. I'm going to slide that down there so I can see it while the code's open. I usually use my notepad editor.
We're going to say give me DSum. Sum up what? Order total from order t where the order date is greater than or equal to today's date minus 30. I like to put the date minus 30 actually in there, so I go that guy because dates have to be inside of those characters, those little hashtag symbols like that. So DSum up the order total from the order table where the order date is greater than or equal to date minus 30, and date minus 30 will actually go into that string between those two characters.
Once we have that value, we'll just say status "30 day orders" like that and the value. Save it. Close it. Open it up. Now when we open up our database, we're given our 30 day order total and it is 750. Let's see if that's right. Yep, that's correct. You can format that as a currency and do all kinds of other stuff with it.
What is this magical DSum function I just used you ask? Well, guess what? I have a video on that too. DSum, DCount, DMax, DMin, lots of different D functions to pull stuff out of tables.
You can even do something where, let's say, your totals were particularly good or bad - you could recolor the status box. If you put some data in there, let's go back to our code, and let's say if c is greater than (let's say) if you did $5,000 in sales for the month, then we're going to say status box, that's the name of the text box, .BackColor equals, what do you want? Green? Or you can use the RGB function to put whatever color you want in there. You can put other status options in there, like if it's less than 20, you can make it red - whatever you want, save it.
Now if I close this and open it back up again, my sales are 750. Let's say we have a really good month, and this is now 50,000. When the database starts up, look at that! The 30 day sales total is 50,000. Party. Invite Rick, because I like parties.
I do this, for example, when I'm importing every morning. I have to import my member list from YouTube for the people who signed up. If I have a new member, I have my status box. It's normally gray, it switches to yellow, but I can say, oh, we got new members, which thankfully happens quite often. Thank you very much. This way, I can at least make sure that if I have that member's info, I can make sure they get their email, so they get their login and all that stuff. Because YouTube doesn't give me anything except your name, your channel name, and your membership level. I don't have your email address. I can't contact you to set up your account. You have to come to me.
Not only can you change the background color, but if we switch this thing to rich text, we can change the foreground color too. What is rich text? I have a whole different video explaining it, but basically, you can use a lot of different formatting options in your text boxes and in your reports and in your forms. If you switch over to rich text, there are some drawbacks. It embeds HTML codes inside your text. You don't want to use it for everything, but for something like a status box, you can add some color to it.
Let me show you. Let's come back into our code, and let's say here, instead of colorizing the whole background, first, let's put a dollar sign here. We'll say, oh, oh, sorry, I missed a step, we have to come and change our box here. That's the first thing. Change your box. Go to the data tab. Now, there's a format tab where it says "format." That's not what you want. You want data and you want "text format." I show this in the rich text video. Change plain text to rich text.
Save it. Close it. Open it back up again. Hit "hello world" and look what happens. It all gets kind of squished together; you don't see everything on its own separate line. Why is that? Because rich text will ignore VB new line. Instead, you have to replace that with the HTML line break character, which is going to be <br>. If you know any HTML or web design, that is line break. You can use a lot of different characters in rich text, not all of them. It's not a complete set of HTML; it's not like a web browser. But there's a big set of them. I cover more of them in detail in my rich text video. Go watch that for some details. I'll just give you some little tidbits down here.
For example, let's say we always want to bold our dollar amount. So I'll put a bold symbol there, and then we'll do a close bold after it. Save it and now open it up, and there you go. You can see that's bold. Let me make it a little bit bigger so you can make it out better on the screen here. Wrong thing, I was clicking around. Then let's go to a point that's nice and big. Nice and big and bold, and there we go. Thirty-day orders. Boom.
Now, if this is over a certain amount, let's make it red. Let's go back to our code, and what I'm going to do now is I'm going to say in here, we're going to make status a string. So dim s as a string. I'm going to say... right here, s = thirty-day orders, and then we're going to bold it either way and stop right there. If c is greater than, let's say, $10,000, then s = s and let's put a font color in here. So it's going to be font color = red. Like I said, it's a simplified version of HTML. Then we'll continue, so s = s and the dollar sign, c, and the bold tag. Actually, before the bold, we're going to use... No, we can leave it like that. We can actually cheat. We can put the closing font here. You're not supposed to have a closing tag without the front tag, but technically, this will work. It shouldn't cause any problems. Ideally, for perfect code, you'd want to do this: you'd stop it here, then you'd do the same thing, like that, you want to put the closing font. If you want to be a stickler, there.
But you can have a closing font with it. S and close your bold. Save it. Now, what did we get? Nothing. Hold on. What did I forget? Oh, did you see it? Did you see my mistakes? I leave stuff like this in the videos and in my classes too. If I goof, chances are you'll goof, you'll make the same goof. So I like to leave the goofs in the videos. Sometimes I get going too fast, and I just don't catch what I'm doing.
In this case, what am I doing here? I'm assigning all this stuff to a variable s. I never sent it to the status function. Status s. All right. Do it again. Close it. Open it. There you go. Text inside your box. Let's change those sales. Sales aren't stellar. That's only a $50 sale, and for those who really pay attention, yeah, I cheated. I added order total here. Because normally the order total in my database is calculated from that, and then you add it all up. I just cheated for class. Yeah, I added an order total field here. I know, I know. That's only 300.
You can use that status box to display a whole bunch of relevant information. I like to use it on my customer forms, for example. I'm not going to go through a full example right now. If you want to see more of this, let me know - post me a message down in the comments below, and I'll do more with status box if you want.
But I have a little status box on the bottom of my customer form. Sometimes you don't normally notice details of all these different fields unless they come together. What I'll do is I'll have it so that if a customer is a gold member and they're in the learning connection, it'll put that in the status box on the bottom so I can very easily see in one spot what all the memberships are.
Let's say hypothetically, you have a special discount for customers in Florida that have at least a family size of four. You might not remember that just looking at these separate fields, but you could have a status box say, "Remember, this is a guy from Florida with a family size of four, he gets a discount." That kind of stuff. That's the kind of stuff that the status box is also good for.
So, it's good for loops. It's good for showing things to the users when stuff's going on in the background, or they're running a long process. It's good for putting messages in their face when they open a form up and it says, "Hey, this guy has an unpaid invoice." That kind of stuff.
So there you go. There's your fast tip for today. Lots of other videos for you to watch. If you didn't know anything that I talked about, many of these other things and pieces and parts, well, there you go.
I hope you learned something from today's video. Hope you had a good time doing it, and I'll see you again real soon.Quiz Q1. What is the main purpose of using a status box on an Access form? A. To display temporary messages to users without interrupting processes B. To replace all message boxes in every scenario C. To force users to acknowledge a popup before continuing D. To display permanent user instructions
Q2. How is the status box implemented in Microsoft Access forms? A. By adding a label and linking it to a macro B. By creating a text box controlled by a VBA function C. By inserting an ActiveX control from the toolbox D. By using a command button with embedded text
Q3. What is a major benefit of using a status box over a message box? A. It continues showing messages while processes run in the background B. It provides a sound alert for every update C. It prevents the user from interacting with the form D. It forces users to click OK before the process continues
Q4. When a new status message is added to the status box, where does it appear? A. At the bottom of the existing messages B. It replaces all previous messages C. At the top of the existing messages D. In a separate popup window
Q5. What coding concept is used to display multiple lines in the status box? A. Array assignments B. String concatenation with line breaks C. Field validation D. Query joins
Q6. What does the DoEvents command do in a VBA code loop? A. Stops the loop and displays a message B. Allows Access to process user input and update the screen C. Cancels all pending queries D. Automatically saves data in the form
Q7. If you want to update the status box only every 100th record in a loop, which VBA operator should you use? A. Division operator (/) B. Modulus operator (Mod) C. Plus operator (+) D. Power operator (^)
Q8. Which function is used in the example to sum up order totals from the last 30 days? A. DLookup B. DSum C. DCount D. DSQLError
Q9. How can you make the status box display text in different colors using formatting? A. By changing the form background color B. By switching the text box to rich text and using HTML tags C. By selecting different fonts in design view only D. By creating multiple text boxes with different properties
Q10. When using rich text in a status box, which tag is used to insert a line break? A. <li> B. <p> C. <br> D. <hr>
Q11. Why might you want to avoid updating the status box on every record in a very large loop? A. It may cause data corruption B. It significantly slows down processing due to screen updates C. It can overwrite data in other forms D. It requires advanced VBA certification
Q12. In what scenario would you want to recolor the status box based on sales totals? A. To visually indicate particularly high or low sales performance B. To set form styles for all users C. To highlight invalid data entries D. To match the company logo colors
Q13. What is a potential drawback of switching a text box to rich text? A. It removes the ability to use VBA functions B. It embeds HTML codes, which may not be ideal for all data types C. It disables all font color options D. It forever locks the text box's background color
Q14. Which event could you use to automatically display information in the status box when a form is loaded? A. After Update event B. On Click event of a button C. On Load or On Open event of the form D. On Unload event
Q15. What is one example use of the status box for summarizing customer information mentioned in the video? A. Displaying a customer's email address for quick copy B. Showing discount eligibility based on state and family size C. Storing confidential notes for admins only D. Launching a video tutorial
Answers: 1-A; 2-B; 3-A; 4-C; 5-B; 6-B; 7-B; 8-B; 9-B; 10-C; 11-B; 12-A; 13-B; 14-C; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on how to use a status box in your Microsoft Access forms to display messages to users. Many of you who have watched my other lessons may already be familiar with this feature. The status box is that small gray area typically found on the main menu that shows updates or information during database processes.
The status box is designed to present virtually any message, such as letting the user know when a process is happening, or just confirming actions. For example, I often use a button that simply displays "hello world" in the box. This provides a quick visual update for the user. I prefer this method over the traditional message box, and there are some important reasons for that which I will discuss.
Originally, I covered how to build the status box in some of my longer tutorials, such as the customer contacts video and the TechHelp blank template walkthrough. However, those videos take some time to get to the relevant section. Because of this, I wanted to create a shorter, dedicated video specifically to cover the status box for those of you who want a quicker answer.
To summarize, the status box is just a text box added to your form, which I usually color gray to indicate to users that it is not meant for input. Essentially, it is read-only and used only for displaying messages. You are free to lock it if you want, but that's optional. I use a simple Visual Basic function (called 'status') to write to this box. If you are new to VBA, I recommend watching my introductory videos on the topic to familiarize yourself with the basics before proceeding.
The setup is straightforward: add a text box to your form and a button that triggers the function to write messages to this box whenever anything noteworthy happens on your form. Instead of using a message box, which interrupts the user by requiring them to click OK before anything else happens, this method lets messages appear while your application continues working. This is particularly useful if you need to show updates during a long-running process, such as loops or multiple queries.
The function itself accepts a string as an argument and places that text at the top of the status box each time it is used, so the newest messages always appear first. This is called string concatenation, combining multiple pieces of text with a new line separator. If you are unfamiliar with these terms, I also have dedicated lessons covering those topics.
A crucial part of the function is the DoEvents command. When Access is busy processing a large task or a loop, it can stop updating the screen and might not respond to user actions. DoEvents allows your database to check for things like user input or screen updates while processes are still running.
The nice thing about the status box is its adaptability. You can copy the components (the text box, the button, and the function) to any Access form where you want this feature. Each form can maintain its own instance of the status feature, ensuring you get targeted updates wherever you need them.
There are many scenarios where this comes in handy. Suppose you have a series of long, sequential queries or processes, which may take a significant amount of time to run, such as importing data or performing batch updates. To keep the user informed and prevent them from thinking the application has frozen, you can display status messages after each major step, such as "Beginning import," followed by updates for each part of the process. A classic case is sending out email batches, where you want to see a running count of progress, like "Sending 4 of 100."
One thing to keep in mind is that frequently updating the screen can slow performance, especially if your process runs through thousands of records. For better performance, consider updating the status box less frequently, for example, every hundredth record using the modulus operator. I have additional videos covering modulus and conditional statements if these topics are new to you.
The status box is also excellent for displaying summary information at application startup. For instance, you might want to calculate sales for the last 30 days by using DSum to sum all order totals over that period and show the result in the status box when the main menu opens. If you want, you can add formatting, like currency format, for better clarity.
Moreover, you can visually highlight key results. If sales exceed a certain threshold, you can change the background color of the status box, or use rich text to modify text color and appearance. Switching the text box to rich text format allows you to apply HTML-like formatting. This can be handy for spotlighting important information, such as highlighting exceptional sales performance in green or red, depending on the result.
The rich text approach uses simple HTML tags for line breaks, colors, and bold. Just keep in mind that while it's not a full-featured HTML editor, you can achieve quite a lot with the available tags. If you want to know more about rich text formatting, I have a separate video covering that in detail.
Beyond displaying progress and statistics, you can customize the status box to show consolidated information about customers or other records. For example, you might want a message to appear when a customer from Florida with a family of four qualifies for a discount or to show at a glance which memberships a customer holds. This pulls together details that might otherwise be overlooked on a busy form.
In summary, the status box serves multiple roles: real-time progress reporting, startup summaries, flagging important details on forms, and enhancing communication between your database and your users without disrupting workflows. If you need more advanced examples or want demonstrations tailored to your specific needs, feel free to let me know. I am always open to expanding on this topic if there's interest.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Creating a status box in Microsoft Access forms Using VBA to update the status box dynamically Difference between status box and message box Concatenating strings to show updates in the status box Ensuring newest status is always at the top Using DoEvents to keep the UI responsive during loops Using status box to display progress during long-running processes Simulating long processes with the Sleep function Displaying progress in loops using the status box Updating status only at intervals with the Mod operator Displaying 30-day sales totals in the status box with DSum Formatting numerical output in the status box Changing status box background color with VBA Switching a text box to rich text format Replacing new line characters with HTML breaks for rich text Formatting values using bold and color in rich text Displaying custom messages for records matching certain criteria Practical uses for status boxes on customer and dashboard forms
|