Reverse StatusBox
By Richard Rost
14 months ago
Flip Status Box in Access: New Messages at Bottom In this Microsoft Access tutorial, I will show you how to rearrange the status box functionality so new messages appear at the bottom rather than the top. We will cover using VBA concepts such as SetFocus, SelStart, and SelLength to adjust message order, helping your users read messages more intuitively. Caleb from Lancaster, California (a Platinum Member) asks: I have been using your status box function for a while now, but my users say it is a little confusing because new messages come in on the top, which is not how you normally read things. They want to know if it is possible to flip it so that new messages appear on the bottom instead. How can I do that? MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, flip status box, reverse message order, VBA status box, SelStart, SelLength, SetFocus, Microsoft Access global module, invert message display, developer-level VBA, text box focus
Intro In this video, I will show you how to reverse the order of messages in the status box in Microsoft Access so that new entries appear at the bottom instead of the top. You'll learn how to update the VBA code using With, SetFocus, SelStart, and SelLength to control message display and cursor placement. I will also show you how to add timestamps, format the time, manage message history by truncating long lists, and handle potential issues like focus flicker when working with different forms. This tutorial is aimed at users with developer-level VBA knowledge.Transcript Today I'm going to teach you how to flip my status box upside down. In other words, we're going to make it so new messages come in on the bottom. Today's question comes from Caleb in Lang Caster, California, one of my platinum members. Caleb says, "I've been using your status box function for a while now, but my users say it's a little confusing because new messages come in on the top, which is not how you normally read things. They want to know if it's possible to flip it so new messages appear at the bottom instead. How can I do that?"
Well, it's a little tricky. I'm going to show you how to do it. But the reason why I have it the way that it is now is because it's much easier to code and it won't confuse beginner programmers. It's very easy to put something on the top of the box instead of having to scroll down to the bottom.
For those of you who don't know what my status box is, it's basically just a big box that I put on the main menu so that I can put messages in there that happen in the program and we don't have to pop up a message box like in front of the user's face, which stops the execution of code or anything like that. It's very unobtrusive. You can see it, though. I can put messages on here.
Now notice that new stuff is hard to see because it just says hello world. If I put some extra text in here, you can see that new stuff comes in on the top. And that's because as you're adding stuff, it's going down below the bottom of the box. In order to read that, if it was new stuff at the bottom, you then have to scroll down to the bottom, which you can do. But it adds a little bit of complexity, and I like to keep things simple, especially for beginners. But since we're getting a little more advanced, now I will show you how to do this.
But first, some prerequisites. First off, this is, of course, a developer-level video. What does that mean? Well, that means if you've never done any programming in VBA and you want to learn how to, go watch this video first. And if you don't know what the status box is, go watch this video where I explain how to build it. And then in this video, I teach you how to make the status function global so any form in your database can use it, and it just displays the message on the main menu.
Now, in addition to those, make sure you understand how to use "With" in VBA. I'm going to use that today. You also need to understand SetFocus. And to move the cursor down to the end of the box, we're going to use SelStart and SelLength. So go watch any of these videos if you're not certain how this stuff works. In case you don't know any of these things, go watch those videos. Then come on back.
Alright, so here I am back in my TechHelp free template. This is a free database. You can grab a copy from my website. And if you've watched all those other videos, you know that by now. So let's go take a look at our function, which is in the global module. There we go. It's right down here. Now the first thing I'm going to do is I'm going to throw the time in here, right there. I'm going to say now and then a colon and then the actual message. This is where we can see what's coming in and when.
And if I come back over here and hit the button, you just see I just added the time index. Every time I click the button, hello world, it puts a time on there. Makes it easier to see. In fact, you might not want the whole date and time in there. Let's format that. Let's come in here and say instead of the whole thing, let's say format now. And I want to make it look like just the hours and minutes. Remember minutes are n because m is the month.
And if you don't know how to use the format function, go watch this video. There's always more stuff to learn. In fact, on this page, I got right here a nice list of the date format codes. I'll bring this up and it teaches you all the different ones here. N is minute.
Alright. So now, what do we have? It just looks like, oh, that's perfect. Actually, for the purposes of class, I am going to add seconds on there because I'll be doing a bunch of clicking at once. But normally, I don't bother with that. But for the class, I'm going to put seconds on there too. Just so we can see. So you can see now that new stuff comes in on top. And like Caleb said, this stuff can be confusing. If your users are looking at this, they're going, well, okay, this happened after that. Sometimes when I'm doing debugging, I have to kind of think to myself too, it's going backward. This happened first, then that happened, then that happened.
Alright. So basically, what I want to do is I want to put the status box first, then a new line, then what's coming in next. So we're going to take this. We're going to move that to the beginning, then the vbNewLine, and then the format blah, blah, blah, blah, and then s at the end.
Okay, that's how it's going to look now. So put what was there first, then a new line, then the time, then what text is coming in. So it goes at the bottom of the box.
Alright, so we got now. Click, click. Oh wait, no, we can't see it. See, that's the problem. It's down past the end of the box. Let's delete some of the stuff that's in here so we can see what's happening. We delete all of that. Even more. Come on, delete. There's a whole bunch of stuff in here. There we go. Alright, ready? Click. Click. Okay, it's working. The problem is as we get more text in here, it goes down past the bottom of the box.
So what we have to do is we basically have to tell Access, hey, go to the end of that box. But you have to first put the focus on it because you can't move the cursor around using SelStart until you have focus on it. So we got to set the focus and then move the cursor to the end using SelStart.
Okay, so how do we do that? Well, let's come back in here. Now this is a global module. And I don't want to have to keep referring to Forms, Main Menu, FStatusBox the whole time. So we're going to use a "With". I'm going to say, I might just copy this. Copy this thing. And say, with that, and then do some stuff in here and then end with. So now I'm going to have to refer to Forms, Main Menu, FStatusBox all the time.
Now, how do I get its value if I'm dealing with this with? Well, it's just .Value. Okay, so its value equals whatever its value currently is and the rest of that stuff. Okay, let's test that. Make sure. Save it. Always throw in a Debug Compile once in a while. Come back out here and get rid of some stuff. Alright. And then we'll test it. Okay, so it's still working. We didn't break it. Always make sure you don't break it first. If it ain't broke, don't fix it.
Okay, now we have to set focus, .SetFocus, which means with that status box on the main menu form, .SetFocus, .SelStart equals, how do we know where to put it? Well, we want the length of that entire string. Okay, go to the end of it. So it's going to be the length of .Value. So if it's 20 characters long, go to position 20. If it's 500 characters long, go to position 500. And just to make sure no text is selected, which there shouldn't be, SelLength can be zero.
Alright, save it. Debug Compile. Getting to the habit of doing this, I do it a lot. Alright, now we'll click. Click. See, look at that. The cursor's at the bottom. Click, click, see. Now as I go past the bottom of the window, look what happens. See?
Now, you will notice a little bit of a flicker if you're on the main menu because what's happening is it's got to steal the focus on the button, which means that the text box goes back to the top. But then as soon as you let go, it goes where it's supposed to go. See that? But if you're on a different form, let's say you're on the customer form. Alright, let's put this guy over here and close this up. So you can see them both.
Alright, if you've got a button on here, let's copy this button. We'll just make this one say hi. Right-click, build event. We'll say status. Hi. As long as you're on a different form, you won't notice that flickering. Watch. See? Because it just stays there. This form, this guy has the focus. So as long as you don't click on something else on the main menu, see, it'll steal focus and then the cursor goes back to the top of this one. There's no way around that, that I know of. But as long as you're on other forms, you're good. See?
What you could do on these buttons here, if all you have are buttons on here, if you don't want it to steal focus like that, you could put a status in that button too. Like this. You could put in here right after you open that form status, you know, open customer form or whatever. Or even just put the code in here. If you don't want to do a full status, you could just put the code in here that does this part of it. You can make a second function that just does the positioning stuff without changing the value. That's up to you. My job is to teach you how the different Legos work. You can put them together however you want.
And the last thing that I might do is I might have the value of the box get truncated because if you start getting 10, 20, 30,000 characters in that box, you might notice a delay and a bit more of a flicker. And you probably really don't want to go back that far in the history anyways. So I would say that this is going to be the right of that value. Maybe let's go 5,000 characters. Obviously you got to find a value that works for you. But this will chop it off. So if it's more than 5,000 characters, the oldest stuff gets just truncated.
And if you do that and you click and you do this and you do that and you click the button, it all works. This button is going to flicker because this button is on this form will flicker, but it will work. And again, I don't know if you know, well, we could try turning Echo off, but that's dangerous. I don't like doing that. And I don't even think that'll fix the problem with Echo because you can turn off Echo, but it's still going to lose focus and then go back to it. So I don't know. Here's the Echo command. Echo turns off all screen writes. So you got to be careful because if anything in here causes an error, you won't see it in your database lock up. So I use this extremely sparingly. But in this case, it doesn't fix the problem. It's still the same over here. And in fact, over here, I think the flicker is even worse. See? So I'm going to, no, I'm not going to do Echo.
So goodbye. I got a whole separate video on Echo. I'll put a link to that down below as well if you want to learn more about that. And if you want to learn more about my courses, I got tons of them. Lots of developer lessons. And I teach things in the order they should be learned in. We don't jump around like we do in these TechHelp videos. So it's learned this, then learn that, then learn this, and I hold your hand. I walk you through stuff the way you should learn it. Become a good little programmer like me. No, I had to learn this stuff. I've been learning this stuff over like 30 years now. And it's just, yeah, I learned bits and pieces here and there. So it took me a while to piece things together in the right order for you guys. So yeah, that's what I do.
But that's going to do it, folks. That's your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Flip status box so new messages appear at bottom
Prerequisites for developer-level VBA tutorial
Understanding With in VBA for optimization
Using SetFocus to control cursor placement
Utilizing SelStart and SelLength for message box
Adding timestamps to messages in status box
Formatting time display with hours and minutes
Implementing new line order for incoming messages
Managing focus to prevent flickering in forms
Truncating message history for performance
Testing changes for status box display
Handling focus between main menu and other forms
COMMERCIAL:
In today's video, we're discussing how to flip the status box in Microsoft Access so that new messages appear at the bottom rather than the top—a feature many users find more intuitive. We'll guide you through handling VBA components like SetFocus, With, and SelStart to achieve this. You'll also learn how to enhance your messages with timestamps and truncate older entries for performance. Whether you're a beginner or have some experience, this step-by-step tutorial will help you get it right. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What was Caleb's main concern about the status box in the tutorial?
A. It wasn't visually appealing.
B. New messages appear on the top, which is not how they normally read things.
C. It was too difficult for beginners to use.
D. It required too much coding knowledge to modify.
Q2. Why does the original status box add new messages at the top?
A. To maintain simplicity for new programmers.
B. To make it easier to search through messages.
C. Because it mimics the behavior of most chat applications.
D. Because the tutorial demands it.
Q3. What is one of the prerequisites mentioned for watching the video?
A. Basic understanding of VBA.
B. Advanced knowledge of SQL.
C. Proficiency in Python.
D. Experience with JavaScript.
Q4. Which VBA method is used to move the cursor to the end of the status box?
A. SetLength
B. SelectEnd
C. SelStart
D. GetFocus
Q5. Why is it necessary to use SetFocus in the code modification?
A. To change the font size within the box.
B. To enable scrolling.
C. So that SelStart can function correctly and position the cursor.
D. To change the color of the text box.
Q6. How does the "With" statement help in the given context?
A. It compacts the code by allowing repeated reference to an object without fully qualifying it each time.
B. It increases the complexity of the code.
C. It makes the code less readable.
D. It helps in debugging the application.
Q7. What problem arises when dealing with longer text and how is it suggested to handle it?
A. The text becomes unreadable; suggested solution is to reduce font size.
B. The application crashes; suggested solution is to restart the application.
C. Text may cause performance issues and flicker; suggested solution is to truncate to a manageable length like 5000 characters.
D. Text stops getting displayed; suggested solution is to increase memory allocation.
Q8. What was recommended regarding the use of the Echo command?
A. It should be used often for better visual control.
B. It should be used sparingly as it can cause the database to lock up.
C. It should be avoided at all costs.
D. It is the only solution to prevent screen flickering.
Q9. According to the tutorial, what can be a downside to implementing the described changes?
A. Increased complexity in understanding the code.
B. The need for additional permissions to alter the code.
C. Flickering when switching focus between buttons on the same form.
D. Incompatibility with newer versions of Access.
Q10. Which action would NOT resolve the flicker issue during form focus?
A. Using a separate form to keep the main form from being impacted.
B. Implementing a custom function without altering the value.
C. Turning off Echo for all screen writes.
D. Adding status calls to track form events without opening them.
Answers: 1-B; 2-A; 3-A; 4-C; 5-C; 6-A; 7-C; 8-B; 9-C; 10-C.
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from the Access Learning Zone covers how to modify the status box so that new messages appear at the bottom rather than at the top. This change can enhance readability for users who are more accustomed to reading from the top down.
One of our platinum members, Caleb from Lang Caster, California, suggested this adjustment because his users found it confusing that new messages appeared at the top. While having messages appear at the top is simpler to code and benefits beginner programmers, more advanced processes allow us to customize the display.
The status box is an unobtrusive way to show messages within a program, bypassing the need for pop-up message boxes that can halt code execution. However, as new entries currently appear at the top, users must scroll down to see new additions if the messages appear at the bottom. To implement the new format where messages appear at the bottom, you must have some familiarity with VBA.
Before attempting this change, make sure you're acquainted with the status box function, how to make it globally accessible in the database, and have an understanding of the "With" statement, SetFocus, and the SelStart and SelLength properties. These skills will be crucial in managing where the message content appears.
To implement this change in the status box, start by modifying the status function in the global module. Attach a timestamp to each message using the Format function to customize the display of time. Then set it up so that the previous content is at the top, followed by a new line and the time-stamped message at the bottom.
Once this is configured, clicking the button will append new messages at the bottom. However, you may encounter an issue where text goes beyond the visible limit, requiring users to scroll. To remedy this, you must direct Access to move to the end of the text box using SetFocus and SelStart.
By using a "With" block in the code, you can avoid repeatedly referencing the form and status box. After setting the focus on the text box, use SelStart to move the cursor to the end and SelLength to ensure no text is selected.
While this will result in a flicker when messages are added due to the form stealing focus, this is mostly noticeable on the main menu form. When operating from a different form, the flicker is minimized.
To manage the length of the messages and prevent performance issues with the status box, you can truncate the stored text to a specific character limit, such as 5,000 characters. This approach retains the most recent messages while discarding the oldest ones.
Overall, this tutorial covers how to implement and refine this functionality, ensuring users have a clearer view of message sequences. For those who encounter issues with screen flickering while implementing these adjustments, options like the Echo command exist, but they come with their own trade-offs.
For comprehensive, step-by-step guidance on all the instructions discussed here, you can find a complete video tutorial on my website at the link below. Live long and prosper, my friends.Topic List Flip status box so new messages appear at bottom
Prerequisites for developer-level VBA tutorial
Understanding With in VBA for optimization
Using SetFocus to control cursor placement
Utilizing SelStart and SelLength for message box
Adding timestamps to messages in status box
Formatting time display with hours and minutes
Implementing new line order for incoming messages
Managing focus to prevent flickering in forms
Truncating message history for performance
Testing changes for status box display
Handling focus between main menu and other forms |
