Account Balances 9
By Richard Rost
3 years ago
Track Account Balances & Transactions, Part 9
This is part 9 of my Microsoft Access Account Balances tutorial series. In this series we will build a database to track your account balances and daily transactions.
Today, we are going to learn how to select all of the text in a field automatically whenever the user clicks on it. This way, you don't have to manually select the text inside the field to change the whole value. It will work similar to how Excel behaves when you click on a cell.
Members
Members will be learning how to manage transactions more effectively by marking them as cleared instead of deleting them, a feature which ensures these transactions are archived for future reference. This extends to features such as closing child forms, wherein closing the Account List form would also lead to the automatic closure of the Transactions form. Members will also become familiar with a system that produces a warning message if a negative value is expected and a positive one is entered. Further, they will learn how to expand their use of conditional formatting to include warning and critical balance levels, moving beyond a simple zero balance.
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!
Prerequisites
Suggested Template
Links
Recommended Courses
Next Videos
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, select text on click
Intro In this video, you will learn how to automatically select all text in a field when clicking on it in Microsoft Access, making it easier to quickly update values just like you can in Excel. I will show how to use the OnClick event, explain why OnGotFocus is not sufficient, and walk through writing reusable VBA code to handle this for multiple fields and forms in your database. You will also see some tips for locking fields and experience common adjustments to the form's design and layout. This is Part 9 of the Account Balances series.Transcript Today is Part 9 of my Account Balances series. If you haven't watched Parts 1 through 8, go watch those first. You'll find links down below you can click on.
Today we're going to learn how to select all of the text in a field automatically whenever the user clicks on that field. This way you don't have to manually select the text inside the field if you want to change it.
You'll see how Excel behaves when you click on Excel. This is one of my pet peeves. Whenever I want to update my balance, the balance of my savings account, if you click here, it puts the cursor in the middle. Then you've got to click again and do that or you've got to click in front of it and hit tab.
What I want to happen is, here's my other copy. What I want to happen is when I click on a field, it just selects that whole field, just like Excel, and I can just type over it. That's how I want it to behave. That's what we're going to do today.
First off, a program note: I did a little resizing again. I know in the last video I did some resizing and I resized again. It's one of those things where whenever I'm using a database myself, off camera, I sometimes make some little tweaks. I always try to mention them to you though.
I needed to make this just a little bit bigger. I made this one a little bit bigger. My database window still snaps to the spot where I record my screen. I had to make this just a little bit bigger for my purposes. I did a little bit of label resizing and formatting and justifying and all. Just some prettying-up stuff.
One thing I do want to do is I do want to lock a few fields here, like this and this, because I've accidentally done that a couple times while I'm working. I don't want the user to change the account or the type from this screen. If you want to change it, open up this thing and you can change it in here if you want to. I'm going to lock those fields here. We can't set Allow Edits to No because then you can't edit anything on this form.
I'm going to just select that. Select that. Go to Data. Go to Locked and set it to Yes. That way the user cannot change those fields. Click here. Nothing happens. If you click here, nothing happens. If you want to change this intentionally, open the account, change it in here, and then close it. Again, just a minor little thing.
Now, going back to Excel for a minute, whenever I'm updating my balances in Excel, it's really easy to come in here and just click on a cell and type in 2000. That's great. That's how I want my database to behave. But Access doesn't behave that way. If you click here and you want to type in it, not here in front of it, you have to click and drag like that in order to change it. Or, like I said, you can click in front of it and hit tab. It's just annoying. I want to be able to click there and just type a value.
It's going to involve just a couple of lines of code, but you have to know where to put them. What we're essentially going to do is we're going to use the OnClick event. You might be thinking to yourself, use the OnGotFocus event. OnGotFocus happens when you move into a (let's call it) cell or field. Do not think cell. But unfortunately, OnGotFocus technically happens before the click itself is registered.
I've tried this in the OnGotFocus event, and if you click, it does select all the text, but then it puts the cursor back where you clicked. We have to use the OnClick event, which happens after you click.
I'm not worried about the OnGotFocus event because if you tab into that field, it selects all the text automatically anyways. What I really want is the Click event, not the GotFocus event.
I looked this up online. As I always do, I always do a little Google search before I show you guys something in case I pick up a tip or two that I didn't know. I saw it on two or three message forums why people can't understand why the OnGotFocus event isn't working. Well, that's why. OnGotFocus fires before the Click event. You get your OnGotFocus, it selects the text, then the click happens and it puts the cursor back where it wasn't supposed to be. We read it and that's what you want.
Here's how we do it.
Let's focus on the Credit field first and we'll do the other ones. Design View, Credit, Event, On Click is right there. Let me slide this over. Come in here. The first thing I want to do is check to see if that field is null.
It should never happen because we have another event that fires that says if they blank that, if they make it null, they'll put a zero in there. But just in case, we don't want our database throwing errors.
If IsNull(Credit) then Exit Sub. Easy enough.
Now, we're going to use the SelStart and SelLength properties to set how much of the text is selected. So Credit.SelStart = 0 puts the start of the cursor before the first character. Now we want to use the length of what's in that field now to set the SelLength. So Credit.SelLength = Len(Credit). So if there's one character in there, you get one. If there's five characters in there, you get five.
Save it. Right over here, let's close it. Open it up again and let's click here. Boom, look at that. Click here. Boom, look at that. It started at zero and selected three. Click.
Now, what happens if we click down here? It doesn't work. And I selected four characters. Why? Well, because the actual value of that field is 1800. So it's not including the formatting. It's not including that comma.
Now, how do we do that? Well, there's the value of the field and then there's the text in the field. What we really want is the length of the text in that field, and that will include the formatting.
So go back to your code and make this Credit.Text. I want the text that's actually in that text box.
All right, save it and now click out here. Click, click, click, look at that. It selects everything in there. Click. All right, see how that works? Nice and easy.
What do we got? Three lines of code. Really, two. This is just to check to see if it's null, which it should never be. That's two lines of code.
Now you see what I mean when I say, you'll just learn a little teeny tiny bit of programming and you can make your databases so much better.
Now, we've got to do this for every field we want that to happen in. Credit, Balance, Pending, maybe Date, maybe this guy over here.
Do I want to copy and repeat this code everywhere? No. No, I don't want to do that. I want to make a function to do that or a global subroutine, somewhere I can just say SelectAllText.
Furthermore, I want to be able to use this on any form with any field. So we're going to make it global, put it in a global module.
This is the kind of thing that I normally show in my extended cuts for the members. So I'm giving you a bonus today to try to entice you to become a member. See the cool stuff that I put in those videos and in my full courses.
Usually in the free TechHelp video, I give you just enough to get the job done. But for the members, you get a lot more. And don't worry, members, you're getting more today too and an actual extended cut.
Let's get rid of this. Let's cut this out.
We'll start by making this a Private Sub here in this form. It's easy to do that first and then think global, but eventually we're going to make this global. So let's call this Private Sub SelectAllText.
I'm going to paste that code in here. In Credit_Click, all you have to do is say SelectAllText. So when this event runs, it's just going to call this sub. Then other fields can call that same sub.
Now, I don't know at this point what control I'm in, what field I'm in, so we need to get that. We're going to say Dim c As Control. A control can be a text box, a combo box, any control on this form, a subform, a button, all that stuff. We're dimming a control.
Now I'm going to say Set c = Screen.ActiveControl. What does that mean? Take the active control, meaning the control that you're actually in, where the focus is on the screen, and set our variable c equal to that.
You have to use the Set keyword anytime you use an object and the control is an object. When I'm done with my variables that require Set, at the very bottom I say Set c = Nothing, so it clears that memory.
Now here I'm going to check for the value of that control. I need to say If IsNull(c.Value). For some reason, Value isn't in the Intellisense drop-down, but it works. Trust me.
Note to Access team: Why is Value not in here? It doesn't have that. No, put that in there, please. Someone needs to go through all my videos and make a list of every time I say note to Access team. Get some AI doing that. I was keeping a list for a while because Microsoft has a spot where you can make suggestions, but whatever, they don't listen to me. I love you, Access team, but I got a list. You guys are busy enough, I know.
Now we've checked to make sure it's not null and now here we can say c.SelStart = 0 and c.SelLength = Len(c.Text). Now, this will work with any control on this form. It's a private sub.
Let's test it. I like to get rid of my empty spaces here. I don't need that. I don't need that. If you think it makes it more readable, you can leave extra spaces.
All right. Save it. Now, so far the only one calling it is Credit_Click, but let's just test it with just Credit. Let's just test it. Click. All right. It's working still. So what we've got is still working. We didn't break anything. But now it's generic; it's not for other controls.
But we're going to make it an event handler function. An event handler function goes here like this in the property. This way you don't have to have tons and tons of duplicated code in your VBA window. You just put that in whatever property you wanted it for the events.
You want to learn more? Here's a video. Go watch this.
So instead of this, we'll get rid of that. We're going to change this to a function. It has to be a function. That should change the function here. Why function? I don't know, that's how they did it. Normally functions return values, but this guy's not going to return a value.
Copy this. Actually, let's copy the whole thing like that. Come over here. Now we're going to select whatever fields we want that function to be in. So I'm going to click on Credit, hold the Shift key down, Balance and Pending. I'm not going to bother with these two since we can't change those. Those are just calculated values from somewhere else. And we'll do Date.
Now in the OnClick event here, put in =FunctionName(). It looks like that. And that should go in that multiple selection.
Save it. Control+S. Close it. Open it up. And click. Look at that. Click. Click. Click. Click. Oh, it doesn't work there, it doesn't work there. In fact, those are locked. Click. See? Isn't that nice?
And how do we get it over here? This is a different form. We can't use that in all the forms unless we put it in a global module. So come back over here. Take this guy, cut it out. All right. Go over, meow. Come down, meow. Find a global module. If you don't have one, make one. I'm going to put it right here after Sleep. Paste it there. And don't forget to make it Public. So I'm going to copy the word Public and put it meow.
So now everybody can call this, and you don't need to send it the form name or the field name. Screen.ActiveControl works with whatever form you happen to be on. It's a cool little property there.
What happened? Oh, my DoorDash order has been picked up. All right. Save it. Let's give it a Debug Compile just to make sure. Once in a while, hit Debug Compile, check all the code in your database.
Now let me close this back up here. Are you ready? Slide... Oh, we can do the Date too over here.
Right click. Design View. I'm going to click you and you, and OnClick, and I got my code in there. Let me see, it's still in there. Oh, I didn't save it in my clipboard.
And with SelectAllText: =SelectAllText(). It shows up in the IntelliSense, so it's good. It's a user-defined function.
Save it. Close it. Close it. Start from the beginning. Open. Click. Oh, look at that. Click. Click. Click. And it's working wonderfully. Beautiful. Now you can use that anywhere you want in your database.
Now as the saying goes, all good things must come to an end. This is going to be the final part of this series, Part 9, Account Balances series. I've covered most of what I wanted to cover in the free videos.
There will still be an extended cut for this video. We're going to make it so you can check off those cleared transactions instead of deleting them, and this way the data will still be stored in the table. It just won't show up on the transactions form.
If you're wondering, hey, did I really do that transaction or not? It'll still be in there, it just won't show up here or be in the math.
I'm not about deleting data if I don't have to. We're going to make some stuff where we auto-close the child form, so if you close the one form, the child form underneath it will close automatically. If you close your account list, the transactions will close. We'll warn if something that you type in is normally supposed to be negative and you type in a positive number; we're going to say, hey, are you sure you meant to do that? And we'll fix it. That'll be covered in the extended cut for the members.
Coming up in my next free TechHelp video, we're going to be doing an expense tracker that will build into this database. Things that you do on a regular basis every month, every week, every two weeks, every year. You pay your electric bill; it's once a month. It's usually about that amount. Your annual Disney pass. That's going to be in tomorrow's video.
I also took all of these lessons - the free ones, the member extended cut ones - I added a bunch more stuff to it and I put together the Account Balances and Recurring Expenses Template, which has lots more cool features that aren't even in the extended cuts. If you want to learn more about that, I put a link down here. You can go there and I'll tell you more about it there. So check it out.
But that is going to be your TechHelp video for today.
Part of the reason why I'm only doing nine parts instead of keeping going with this is because as time goes on, fewer and fewer people watch the next lesson. It's the same problem I have with my regular classes. You might get X number of people that watch beginner level one, they get the basics, and they're good. Slightly fewer people watch beginner two and then so on up the list.
That's why I sell my beginner lessons for only a couple dollars each, and then my developer lessons, I have to charge $20, $30, $40 for because fewer people watch the developer lessons, and I've got those to pay too. So, in order to make it worth my while to produce those, I have to charge more for them.
So it's the same thing with these series that I do on YouTube. I'm going for views. And if one fifth of the people watch Part 9 that started watching Part 1, I've got to go on to the next topic.
But if you want to learn more about this stuff, check out the extended cuts, check out the template. And if you have any questions or comments or you want to see more of something, feel free to post them down below in the comments section.
I hope you learned something. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main functionality being added to fields in this tutorial?
A. Automatically sum up values in a field
B. Select all text in a field when the user clicks on it
C. Prevent users from editing any field
D. Add a new field automatically when data is entered
Q2. Why does the instructor prefer using the OnClick event instead of the OnGotFocus event for selecting all text?
A. OnGotFocus does not trigger for all types of controls
B. OnClick happens after the mouse click is registered, ensuring the selection is not overwritten
C. OnGotFocus is not available in Access
D. OnClick is simpler to program than OnGotFocus
Q3. How does Excel typically behave when the user clicks inside a cell to update a value?
A. Only the cursor is placed where clicked; nothing is selected
B. The entire content of the cell is automatically selected for replacement
C. The cell is locked for editing
D. No action occurs until you double-click
Q4. What properties of a control are used in VBA to select all the text inside it?
A. SetFocus and GotFocus
B. Caption and Value
C. SelStart and SelLength
D. Lock and Enabled
Q5. What is the purpose of using the Screen.ActiveControl property in the global subroutine?
A. It locks the control to prevent editing
B. It retrieves the currently active field/control within the form
C. It clears the value of the selected control
D. It automatically saves changes to the field
Q6. In the code to select all text, why is Len(c.Text) used instead of Len(c.Value)?
A. Because c.Text returns the plain value, not the formatted text
B. Because c.Text includes the formatting shown in the control
C. Because c.Text is always an integer
D. Because Len(c.Value) causes an error in Access
Q7. Why does the instructor suggest making the SelectAllText routine global?
A. So it can be used only in the current form
B. To allow it to be reused on multiple forms and controls
C. To make it easier to type
D. To disable editing globally
Q8. What is the recommended way to call the SelectAllText function from a control's property sheet?
A. Enter =SelectAllText() in the OnClick property
B. Add SelectAllText to a macro
C. Use SelectAllText in the Control Source property
D. Place the code in the Form Load event
Q9. What does the initial check If IsNull(c.Value) Then Exit Sub accomplish in the SelectAllText subroutine?
A. Forces the field to always display zero
B. Prevents an error if the control's value is null
C. Sets the SelLength to zero
D. Ensures the control is always locked
Q10. Why does the instructor lock certain fields like account and type on the form?
A. To make them easier to find
B. To prevent accidental edits by users from this screen
C. To allow calculations only on these fields
D. To sort the data automatically
Q11. When refactoring the code for reuse, which programming construct is used to reference any control, not just a specific one like Credit?
A. Dim c As Collection
B. Dim ctrl As String
C. Dim c As Control
D. Dim this As Form
Q12. What is the advantage of using a public routine in a global module in Access?
A. It allows the routine to be called from any form or report throughout the entire database
B. It makes the function run faster
C. It prevents users from running the code
D. It only works in the form it was created in
Q13. What should you do periodically in the VBA editor to check for code errors?
A. Press Shift+F2
B. Use Debug Compile to check all code in the database
C. Export and re-import all modules
D. Only check when an error is visible
Q14. What happens if you set Allow Edits to No on a form in Access?
A. The user can only edit specific fields
B. The user cannot edit any field on that form
C. The user can only edit fields with OnClick events
D. The user can edit new records only
Q15. When the instructor refers to "event handler functions," what does he mean?
A. A function that returns a value based on user input
B. A function that can be assigned to handle events like OnClick for multiple controls
C. A function that sets the default value for a field
D. A function that only runs when the form loads
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-B; 7-B; 8-A; 9-B; 10-B; 11-C; 12-A; 13-B; 14-B; 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 TechHelp tutorial from Access Learning Zone will cover how to automatically select all text in a field when a user clicks on it. This is Part 9 of my Account Balances series. If you have not watched Parts 1 through 8, I recommend checking those out first to understand the full context.
Today, we are addressing a small usability issue that has always bothered me. In Excel, when you click on a cell, the entire contents are selected, so you can immediately type over the value. Access does not behave this way by default. When you click into a field in Access, the cursor is placed at the position where you click, requiring you to either manually select the text or position the cursor at the beginning before you can type. This often slows down data entry, especially for tasks like updating your savings account balance.
Our goal is to replicate Excel's behavior in Access so that clicking on a field selects all text automatically and allows for quicker data input.
As a side note, I made some minor adjustments to my form layout, including resizing fields and labels, to improve usability and appearance. I also locked a few fields, such as Account and Type, to prevent accidental editing. You can still change these values by opening the record in a different location, but they are locked on this main screen for safety. Locking specific fields is important when you want to restrict edits without disabling editing for the whole form.
Back to our main topic, the default behavior in Access requires at least two clicks or a click and drag to replace text in a field. To improve this, we need to write a small amount of VBA code, but the key is knowing which form event to use. Although it might seem logical to use the OnGotFocus event, this fires before the click is registered. If you use OnGotFocus, Access does briefly select the text, but then places the cursor where you clicked, negating the selection. Instead, we need to use the OnClick event, which occurs after the field has been clicked. Tabbing into a field will select the text automatically, so we do not need to handle that scenario.
Online discussions reveal that this is a common point of confusion, so I want to clarify this approach for you. The solution involves just a few lines of code using the SelStart and SelLength properties to select the text.
Let me walk you through the process with the Credit field as an example. In the field's OnClick event, I write code to first check if the value is null. Typically, the field should never be null because we set a default value elsewhere, but this check prevents potential errors. Then, I use SelStart to set the cursor position to the very beginning, and SelLength to select all text in the field.
At first, you might use the actual value of the field to get its length, but that does not account for any formatting, like commas. For formatted fields, you really want the length of the displayed text, so you should use the Text property instead. This ensures that all visible characters are selected.
That's the basic technique, and it only takes two or three lines of code. This is an excellent example of how a small amount of programming knowledge can greatly enhance the usability of your databases.
If you want this functionality on multiple fields, you would need the same code in each field's OnClick event. Obviously, copying and pasting code everywhere is not ideal. Instead, I recommend creating a reusable subroutine. By making a global function or subroutine, you can call the same code from any field, on any form, without duplication.
Normally, I cover these reusable enhancements in the extended cut videos for members, but today I am including that here as a bonus to encourage you to check out all the resources available for members, which go into even more depth.
So, the next step is to place this code into a private subroutine in your form. In each field's Click event, simply call this subroutine. To make the routine generic, we declare a variable as a Control and set it equal to the currently active control. The Set keyword is necessary here because controls are objects. It is good practice to clear any such object variables by setting them to Nothing at the end of your subroutine.
To determine if the current value is null, you can use IsNull with the Value property. Even though you might not see Value appear in the Intellisense, it does work as expected.
With this setup, your code works for any control on the form. Now, you can further refine the solution by turning your subroutine into an event handler function. This allows you to enter the function name in the property sheet of each field you want to support this behavior. This avoids cluttering your VBA editor with redundant code and streamlines your event handling.
If you want to apply this behavior on multiple forms, you can place the function in a global module and declare it as Public. With the use of Screen.ActiveControl, there is no need to specify the form or control names - the function automatically works with whichever field currently has focus.
Once you've saved and compiled your code, you can apply it to as many fields and forms as needed, simply by referencing the function in the OnClick property. This approach can be used throughout your database to provide instant, user-friendly editing.
As for what's coming next, this is the final part in the free Account Balances series. I've covered most of what I planned to for these lessons. However, today's video does include an Extended Cut for members. In that, we will cover how to check off cleared transactions instead of deleting them, so you can preserve your data without showing it on the transactions form. We'll also look at automatically closing child forms when a parent form is closed, and adding a warning if you enter a value that should normally be negative as a positive instead. If those issues interest you, be sure to check out the Extended Cut.
Looking ahead, my next free TechHelp lesson will introduce an expense tracker that integrates with this database, making it easier to handle recurring expenses like monthly bills or subscriptions.
All of these lessons, including additional features not available even in the Extended Cut, have been compiled into my Account Balances and Recurring Expenses Template. If you are interested in that, I have provided a link where you can find more information.
One reason this series is wrapping up at nine parts is because, as series progress, fewer people typically watch each subsequent video. This is one of the reasons the more advanced lesson materials are priced higher - fewer people purchase them, but they are much more in depth and require more of my time.
If you want to explore these advanced topics further, definitely check out the Extended Cut videos and the template. If there is a specific topic you want to see more of, or if you have questions or comments, please post them below.
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 Automatically selecting all text in a field on click
Difference between Excel and Access text selection behavior
Locking specific fields in an Access form
Using the OnClick event to select text in Access
Why OnGotFocus event does not work for selecting text
Using SelStart and SelLength properties in VBA
Using .Text property to get formatted field text
Creating a subroutine to select all text in a field
Making the text selection routine generic for any control
Referencing Screen.ActiveControl in VBA
Converting the subroutine to a global module function
Assigning the function to multiple fields' OnClick events
Using the function across multiple forms in Access |
