AutoTab to Next Field
By Richard Rost
6 years ago
AutoTab to Automatically Move to Next Field when Finished Typing
Learn how to use the AutoTab and Input Mask features in Microsoft Access to enter a specific number of digits and then jump focus to the next field to speed up data entry.
Richard from North Adams, MA (a Developer student) asks: I have a field that I want to store as a number (long integer) but I want to display it as two digits (00, 01, 02). Also, when the user types in both digits, I would like to automatically move to the next field. How can I do this?
Members
I'll show you how to use the BeforeUpdate event, OnChange event, and the Text property to perform the same task in VBA with a lot more flexibility and control.
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
Intro In this video, I will show you how to use the AutoTab property in Microsoft Access to automatically move the focus to the next field when entering data, such as a two-digit number. We will cover setting up the format and input mask for a number field, applying a validation rule, and enabling AutoTab so data entry becomes faster and more efficient. This example uses a Family Size field to demonstrate how to format values as two digits, limit the input, and have Access tab to the next field when you finish entering the maximum allowed characters.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor.
In today's lesson, I am going to show you how to automatically move focus to the next field after data entry using the AutoTab property. If you set a field for two characters, for example, or two digits, as soon as you type in those two digits, it moves you to the next field. That is what we are going to cover in today's video.
Today's question comes from another Richard in North Adams, Massachusetts, one of my developer students. Richard says, "I have a field that I want to store as a number, a long integer internally. But I want to display it as two digits, 01, 02, and so on. Also, when the user types in both digits, I would like to automatically move to the next field. How can I do this?"
Well, Richard, there are two ways to do this. There is the easy way, which is quite simple, or there is the more complicated, but more flexible way. I will show the complicated way in the extended cut, but let me show you the easy way first.
Let's open up my blank customer template. You can download this free from my website. There is a link down below in the description.
Here is my blank customer template. Let's go to the customer form. Down here, we have a number called Family Size. Let's apply Richard's parameters to this Family Size field. Go to Design View and open up the properties for Family Size.
Now, Family Size internally is stored as a number. If we go to the customer table and design it, you will see right here Family Size. There you are. It is a long integer. So, in the form, if I just wanted to display this as a two-digit value, I can go to the format and put in 00. 0 says, "Show me a number in this spot." If you put two of them there, then you see two digits.
Save changes. You will see every number shows up with two digits. If there is more, if you put in 233, it will show three digits. But if there is only one, you will get two. That is how the format works. So, that is how we display the value, how we want to see it.
What about entering in the data? There is a property called Input Mask. Over the Data tab, go to Input Mask. For the Input Mask, type in 09. What does that mean? 0 says there has to be a value entered in this spot, a number. 9 says there may be a value entered in this spot. So, this says you have to input at least one digit, but you can input two. That is what 09 means.
I cover formatting and input masks, by the way, in a lot more detail in my Access Beginner Level 3 class. I will put a link to it down in the description below. There are all kinds of different things you can do with input masks. You can format for dates, you can format for text, for numbers, all kinds of things.
Close this and save it. Go back in. Now when I click on Family Size, you can see there is a little input mask popping up there. See that little underscore?
Let's say I am up here and I tab down to it. Tab, tab, tab, tab. If I tap in a 1, look at it, I get a little underscore there. That is the input mask running. Now, I can leave it as 1 and hit Tab, and the format kicks in and shows it like that. Or I can type in 1, 1.
What if I try typing in 500? I cannot. I am in the third digit there and nothing is working. Or I can backspace and I can type in just a 9. See that? So I can type in at least one and at most two digits.
If you want to control the values that go in there, use a validation rule. For example, you can go to Validation Rule. Let's say the maximum family size you allow is 20. So this has to be less than or equal to 20. Max value is between 0 and 20. See that? You can put this at the table level too.
I usually prefer doing it on the form because you might have a separate form, like an administrator form, where you allow things that you do not allow the users to put in your form. So I do not put the validation rules at the table level. That is just my preference.
Now, if I come down here and I try putting in 35, it yells at me. So I have to hit escape or fix it.
Now here comes the third component. We have the format to display it, the input mask to enter the data. Now, I want to tab automatically to that next field once I am done typing in two digits. I want it to go 1, 1, and then immediately jump down to Customer Since. How do you do that? Well, that is the star of the show, the AutoTab property.
Right click, Design View. Go into the properties for Family Size again. On the Other tab, find this guy right here, AutoTab. It says, "Automatic tab after the last character permitted by input mask is entered." Set that to Yes.
This property works in conjunction with the input mask. Once someone reaches the end of the input mask, it jumps to the next one. It does not work with a free-formatted text string where you do not know how many characters you are going to type in. So if you know it is always going to be two characters, or always going to be four characters, like a month or a year, or credit card number, or something like that, set the AutoTab property after you set your input mask.
Now, customer form. Come in here, tab to Family Size, 05. Boom, right down to the next one. Come up here. 66, back it up. 15, boom. 22, nope, cannot do it. 01, same. Your people will get used to the fact that they can either go 1 Tab, or they can go 03, and it jumps them.
People get in a habit when they are doing data entry, especially a lot in a row. They start going 03, 04, 05. You get used to that. So that is the easy way to do it.
The more difficult way, which adds a bit of flexibility, involves a bit of coding, and I will show that in the extended cut for the members.
Want to learn more? Want to see my more extended version of this? Get the extended cut. Members only, Silver Members and up. I am going to show you how to do this without the validation rule, without the input mask, and without the AutoTab property. It adds more flexibility; there is more you can do with it. We will learn about the OnChange event, the BeforeUpdate event, and the Text property that every field has.
One of the benefits of my new method is if you come down here and type in a valid field, I change it so that its values are from 1 to 12. I am pretending we are doing a month here. If I type in 11, it is fine. If I type in 37, nothing happens. It lets you type in what you want, and as soon as you try to leave the field, it pops up "invalid month." But as soon as you type in something right, it lets you do it. That is what we do with VBA code. A little bit of code, maybe ten lines.
The programming method offers a lot more flexibility; there is a lot more you can do with it. I cover that in the extended cut. Silver Members and up get access to all of my extended cut videos.
How do you become a member? Click on the Join button below the video. Silver Members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you will see a list of all the different membership levels that are available, each with its own special perks.
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.Quiz Q1. What is the main function of the AutoTab property in Microsoft Access forms?
A. It automatically validates the field data as you type.
B. It moves focus to the next field after the last character permitted by the input mask is entered.
C. It formats the value entered in the field.
D. It restricts users from leaving the current field.
Q2. What input mask would you use to require at least one digit but allow up to two digits in a field?
A. 00
B. 09
C. 99
D. 0#
Q3. How is the Family Size field stored internally in the customer table?
A. As text with leading zeros
B. As a short text string
C. As a long integer number
D. As a date value
Q4. What does a field format of "00" do in the properties of an Access form field?
A. Prevents entering numbers larger than 99
B. Ensures numbers greater than 99 show a warning
C. Displays all numbers as two digits, adding leading zeros if necessary
D. Rounds numbers to the nearest whole number
Q5. What is the effect of entering more than two digits for Family Size with the input mask "09"?
A. The extra digits are automatically truncated
B. You are allowed to enter as many digits as you want
C. Only the first digit is saved
D. The third digit is ignored; you cannot enter more than two digits
Q6. Where does Richard recommend to generally set validation rules for fields that require different rules on different forms?
A. At the table level only
B. Never use validation rules
C. On the form level, for more flexibility
D. In the query design
Q7. Which combination of settings ensures that after two digits are entered in a field, the focus automatically moves to the next field?
A. Format 00 only
B. Input Mask 09 only
C. AutoTab set to Yes and Input Mask set to allow two digits
D. Validation Rule requiring exactly two digits
Q8. What additional topics does Richard mention will be covered in the "extended cut" for members?
A. Using macros and queries
B. Formatting dates and times only
C. Using OnChange and BeforeUpdate events, and the Text property with VBA code
D. How to export data to Excel
Q9. According to the video, what benefit does the programming method (using VBA code) provide over the Input Mask and AutoTab method?
A. Less flexibility but easier to use
B. More flexibility and more control over validation
C. It requires no code and is always recommended
D. It removes the need for tables in Access
Q10. What is the best way to allow administrators to bypass some validation that regular users cannot, according to Richard?
A. Set validation rules at the table level
B. Remove all validation from forms
C. Place validation rules at the form level for user forms, and use separate forms for admins
D. Use queries to validate data
Answers: 1-B; 2-B; 3-C; 4-C; 5-D; 6-C; 7-C; 8-C; 9-B; 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 video from Access Learning Zone covers how to automatically move the focus to the next field in a Microsoft Access form as soon as a user finishes entering data, using the AutoTab property. This is particularly useful when you want to streamline data entry, such as when you have a field meant for just two digits: as soon as these two digits are entered, the cursor jumps immediately to the next field.
The question for today comes from a developer who wants to store a value as a long integer but display it as a two-digit number—like 01, 02, etc.—and, upon data entry, have the focus automatically advance to the next control after those digits are entered.
There are generally two ways to tackle this problem. There is a simple approach using built-in Access properties, and a more complex but flexible solution that requires some VBA programming. In this lesson, I walk through the easier method first.
We start with a blank customer template, which you can download for free from my website. Within this template, I focus on a field called Family Size. Internally, Family Size is stored as a long integer in the customer table. To make these numbers always appear as two digits in the form, just set the format property of the control to "00." This tells Access to display numbers like 01, 02, and so on. If a user enters a larger number, such as 233, it displays as 233. For single-digit numbers, you see a leading zero, thanks to the custom format.
Next comes the data entry aspect. To control how users enter values, use the Input Mask property. Switch to the Data tab of the control's properties and set the input mask to "09." Here, 0 means a digit is required, while 9 indicates a digit is optional. This setup will require at least one digit and allow for two digits at most. If you want more details on formatting and input masks, I discuss these topics extensively in my Access Beginner Level 3 class.
With the input mask applied, you'll notice when entering data into the Family Size field that only one or two digits are accepted. For example, trying to enter three digits is not possible due to the input mask. If you need to limit the allowed range further—for instance, so the family size cannot be greater than 20—you can set up a validation rule in the field's properties. While you can put validation rules at the table level, I usually prefer to set them on the form itself, so you have more control over which forms enforce which rules.
Now for the automatic movement to the next field, which is the real highlight here: the AutoTab property. In Design View, find the AutoTab property under the Other tab for the control, and set it to Yes. AutoTab only works in conjunction with an input mask. Once the user enters the maximum number of characters allowed by the input mask, Access automatically moves the cursor to the next control. This works well if the field always contains a known, fixed length of data.
With everything set up, when you enter a value in the Family Size field, as soon as the required digits are filled, the focus automatically shifts to the following control. This feature can significantly speed up data entry, especially when a user is entering many records in sequence.
The method shared so far involves setting the format property, the input mask, and AutoTab. All three combine to make data entry efficient and restrict it to the correct format. Users quickly get accustomed to typing, say, 03, 04, 05, and watching the cursor jump ahead automatically.
If you need even more flexibility—say, for non-numeric input or more sophisticated validation—you can use VBA code. This programming-based approach is more advanced but lets you customize behavior even further, such as validating inputs against a list of acceptable values, providing custom error messages, and more. In the extended cut, available to members, I show how to use the OnChange and BeforeUpdate events, as well as the Text property, for fine-grained control without relying solely on input masks or AutoTab. This method allows the user to enter any value, but validation only kicks in when leaving the field, making it less restrictive during entry and more user-friendly overall.
If you want access to the extended version of this tutorial, where I walk through the programming method and additional features, those are available for Silver Members and above on my site. Members also get access to other exclusive content, live lessons, and member chats.
As always, all basic TechHelp video lessons will continue to be available for free. For a complete, step-by-step video walkthrough of everything covered here, visit my website at the link below. Live long and prosper, my friends.Topic List Displaying a number as two digits using the Format property
Setting up an Input Mask for numeric fields
Configuring Validation Rule to limit field values
Using the AutoTab property to move focus automatically
Combining Format, Input Mask, and AutoTab for data entry efficiency
Testing field behavior with different values in a form
Deciding where to apply validation rules: form vs table |
