Tab Out of Subform
By Richard Rost
8 months ago
Tab Out of a Subform in Access Using Keystrokes or VBA In this Microsoft Access tutorial, I will show you how to efficiently tab out of a subform, addressing a common issue faced by many users. Whether you're a beginner relying on simple keystrokes or an advanced user looking to implement a VBA solution, I've got you covered. You'll learn how to make the subform a tab stop, use control tab to navigate smoothly, and apply the cycle property for continuous form navigation. For developers, I'll demonstrate using the key down event in VBA to streamline tabbed navigation in your Access forms. Join me as we tackle this practical challenge in Access. Carter from Rancho Cucamonga, California (a Platinum Member) asks: I have a form with a subform, and my users keep getting confused when they get into the subform. Once they tab into it, they feel stuck and can't figure out how to get back to the main form without stopping to grab the mouse or clicking somewhere else. Is there an easier way to let them tab out of the subform and keep the data entry flowing smoothly? 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, tab out of subform, subform keystrokes, VBA key down event, set focus on parent form, subform tab stop, cycle current record, key code nine, control tab shortcut, swallow keystroke, short circuit evaluation, continuous form navigation, AccessTechHelp, design view, Access form navigation tips
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Today, I'm going to show you how to tab out of a subform in Microsoft Access using either keystrokes for the beginners or, for the advanced users, the developers, I'll show you how to do it with a little VBA.
Today's question comes from Carter in Rancho Cucamonga, California, one of my platinum members. Carter says, "I have a form with a subform. I use your keep getting confused when they get into the subform. Once they tab into it, they feel stuck and can't figure out how to get back to the main form without stopping to grab the mouse or clicking somewhere else. Is there an easier way to let them tab out of the subform and keep the data entry flowing smoothly?"
Well, yes. There are two ways we can do this. You can do it with some keystrokes, which will involve just some training. That's the easy way for you, but they have to remember it, obviously, all the new users. Or we can do it with a little VBA. I'm going to show you both solutions.
First off, some prerequisites. If you're not familiar with the tab order stop and cycle, go watch this video. This is for the beginners. You have to know how tab order works, how to make fields, tab stops, and how the tab cycle works. If you want to continue on to the VBA example, go watch my key down event. This teaches you how to trap key clicks on the keyboard. Key presses, because key presses are a different event. It lets you trap when the user hits a key, and that's what we're going to use for the VBA solution. These are free videos, and this one has some prerequisites too, including my intro to VBA class. So, go watch this if you're just getting started with the VBA before you watch the key down video. Let me show you the beginner method first.
All right, here I am in my TechHelp free template. This is a free database. You can get it off my website if you want to. In here, we've got a customer form, and customers have contacts. As part of showing you how I built this, I also built the same thing as customers with contacts, so it's got a subform instead of opening up a second form.
Now, the way this is currently set up is I have it so that the subform is not a tab stop. As you hit the tab key, it tabs right past it and goes to the next record. We're going to change this. We're going to make it so that the subform is a tab stop, and I'm also going to change the cycle so that it just repeats the current record.
Here's what we're going to do. Design view. We have to select the subform object itself. Make sure it's orange all the way around it, not anything inside of here. You have to get the subform object. The subform object is an object on the parent form. Double-click on that. That'll bring up this property sheet. It says "subform subreport contact F." Now, we're going to find other and set tab stop to yes. That means that when we're tabbing, it will go into the subform. I turned that off when I built this. Save that, and if I reopen it now, you'll watch as I go tap, tap, tap, tap, tap, tap. It tabs right into it. Now, the problem is we get stuck down here. I'm hitting tab, tap, tap, tap, tap, and nothing's happening because that is the end of the records in the subform. It's a continuous form.
Here's where the keystrokes come in for the beginners. If you hit control tab, it tabs past this to the next field on the parent form. Since this is the last field in the tab order, the next field happens to be the first field on the next record. It's just like if you were here, and you tap, tap, tap, tap past all of these controls, it just goes to the next record. That's the same thing that happened here. Control tab will tab you out of the subform to the next record.
That's why I said let's make it so that the cycle is the current record. Go up here, pick the properties for the parent form, and we're going to change the cycle to current record. That way, it just tabs back to the same person.
Save it, close it, and open it back up again. Now, I can go tap, tap, tap, tap, tap, tap. I just want to hold the tab key down. I'll get way to the end here. Now, if I hit control tab, it brings me right back up here to the first field in the parent form. You can also use control shift tab. If you're down here, control tab goes to the next control after the subform. Control shift tab goes to the previous control in the parent form, whatever was right before the subform. But getting people just to remember control tab is enough. You can put down here, if you want to. If it's a training thing, if you plan on going with the beginner method, just put a little note down here in a label that says, "Hey, hit control tab to go back to the parent form." That's a training issue.
As a side note, the way I've got this set up is I've got the date and the description up here as fields in the continuous form. Down here is the footer of the form in here. If you're right there, and you want to go to that notes field, you can hit F6. That'll jump you down to the notes field. I don't like F6, though, because if you keep hitting F6 again, it'll jump you over to the navigation pane and then up to the ribbon and then up to there and then back there. So, F6 just moves you around to the next group. I try not to use F6. I got most of this stuff turned off anyways in databases that my users get their hands on.
So there's basically control tab and control shift tab.
Now, what about the programming method? VBA, put your VBA hats on for the developers in there. What I'm going to do is in here, as I'm going tab, tab, tab, tab, tab. At this point now, I want to look at the key down event. So, when the user hits a key, if they hit the tab key, and I'm on the blank new record. This is the blank new record. You can't see it because it's not on here, but the contact ID will be null on this record because it doesn't have one. If you start typing in here, now it's got a value. But if you don't, this contact ID, which is the primary key of the contact table, is null. I'm going to say in the key down event, if the contact ID is null, and if they hit key code nine, which is tab, in the key code video, I show you how to figure out what all these key codes are. But the tab key is key code nine. If that's the case, then I want you to go up here. I want you to set the focus on the parent forms customer ID.
What does this look like? Well, let's right-click, design view. Let's go to this guy. You have to click on it twice because remember, the first click is the subform, the second click is the description field. Double-click on its border, that'll bring up the property sheet for the description field.
Go to events, we're finding the key down event. Then I'm going to hit the dot button to bring up my builder. Here we are, we're in the description, key down event. We're going to come in here. I'm going to say if is null, contact ID, then right, we're on the last record, that's null. Do some stuff and then end if. Then if that's the case, we're going to say if the key code equals nine, then that's the tab key. Do some stuff, right, end if. Why am I making it two separate if blocks? We'll talk about that at the end of the video. If that's the case, we're going to say parent, customer ID, that's the field I want to jump to, it's the customer ID field on the parent form. This way, you don't got to worry about what the name of the form is, just say parent. Dot set focus, jump to it.
Now, there's one more thing you have to do, and this is very important. In fact, this came up in the forums on my website a couple of days ago. You have to swallow that keystroke. You have to say key code equals zero, swallow it. Otherwise, you're just reacting to the tab key, but you're still allowing it to happen. So, you have to set key code equals zero, and that tells Access to ignore that keystroke. I don't want you to process it. This came up a couple of days in the Access developer form on my website. David came up with the same, very similar question. He had the code just right, except he forgot to swallow the keystroke. And then I told him at the end here's like, you got to swallow it.
So, let's save it. Always throw in a debug compile once in a while. Close it, open it. Ready? Tap, tap, tap, tap, tap, tap, tap, tap, tap, right back up there. See, or wherever else you want to jump to. I jumped to the customer ID, you could jump to the first name, you could jump wherever you could jump to your mom's house. I don't care. There we go. Oh, interesting thing to note also is, if you leave the tab here, when you tab off of it, that's the spot where your cursor is in that subform. So, you notice what happened? And if I go, tab, tab, tab, tab, tab, tab, tab, tab, tab, it's still jumping back down here. So, if you want, you could trap another event, you could say right in here with this guy, if the user hits tab off of here, go to the first record and here using go to record. You could do that as well. And if anybody wants to see it, you can't figure it out, let me know. Maybe I'll do another video. It's really easy. I've got a whole separate video on go to record and go to control, you can use those.
Now, why did I make this and this two separate commands instead of just saying, if is null contact ID and key code equals nine? Anybody know? It would basically look like this. This looks like shorter code. This is because VBA does not practice something called short circuit evaluation. And this is a very, very nerdy topic. The only reason that I even bother with this is because I used to be a C programmer, and in the C language, if you evaluate something and it turns out to be whatever you're looking for, true or false, it will immediately exit that and bypass the loop. Whereas with VBA, VBA is going to evaluate everything on that line, and it's going to check this, it's going to check this, it's going to check all the conditions and then put them all together. Whereas other languages, like I said, with C, it won't do that. So, if this is an AND condition, as soon as it hits this and if this is not true, it'll drop out. Now, for an example like this, it's silly because these are both going to happen instantaneously. But sometimes you've got maybe functions in here with D lookups and something can take a lot of time. If you're saying, you know, if decount customer T, blah, blah, blah, and that's gotta look through 5,000 records, or D look at customer F, blah, blah. So, if you've got items in your statement here that take a long time to evaluate, you're better off doing this and making each one of those a separate F then. Because if this one turns out to be false, it bypasses it and jumps past everything else. Whereas if you write it like this and each one of these is a very long statement, it's going to take a lot longer. So that's why, when it comes to VBA, I am in the habit of writing stuff like this.
That's all how you tab out of a subform. I know a lot of people have asked about that. This has been on my list for a while and that issue came up in the forums and I'm like, you know what, I've been wanting to make a video about this for a while now. So there you go, that's how you tab out of a subform. And that's going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
If you enjoyed this video, hit that thumbs up button right now and give me a like. Also, be sure to subscribe to my channel, which is completely free, and make sure you click that bell icon and select all to receive notifications whenever I post a new video. Do you need help with your Microsoft Access project? Whether you need a tutor, a consultant, or a developer to build something for you, check out my Access Developer Network. It's a directory I put together personally of Access Experts who can help with your project. Visit my website to learn more.
Any links or other resources that I mentioned in the video can be found in the description text below the video. Just click on that show more link right there. YouTube's pretty good about hiding that, but it's there, just look for it.
Now, if you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, tables, all that stuff. It's over four hours long. You can find it on my website or my YouTube channel. I'll include a link below you can click on, and did I mention it's completely free? If you like level one, level two is just one dollar. That's it. It's free for members of my YouTube channel at any level. Speaking of memberships, if you're interested in joining my channel, you get all kinds of awesome perks. Silver members get access to all of my extended cut TechHelp videos, and there's hundreds of them by now. They also get one free beginner class each month. Yes, those are from my full courses. Gold members get the previous perks plus access to download all of the sample databases that I build in my TechHelp videos. Plus, you get access to my code vault where I keep tons of different functions and all kinds of source code that I use. Gold members get one free expert class every month after completing the beginner series.
Platinum members get all of the previous perks plus they get all of my beginner courses, all of them from every subject. You get one free advanced or developer class every month after finishing the expert series.
So that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com.I hope you enjoyed. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.
TOPICS:
Tabbing out of a subform in Access
Keystroke method for subform exit
VBA method for advanced users
Setting subform as a tab stop
Changing the cycle to current record
Using control tab to exit subform
Control shift tab for previous control
Swallowing keystrokes in VBA
Key down event for tab key detection
Parent form focus with VBA
Short circuit evaluation explanation
COMMERCIAL:
In today's video, we're going to explore how to navigate subforms in Microsoft Access more efficiently. For beginners, we'll discuss keystroke techniques to tab out of the subform seamlessly, and for the developers, I will show you how to automate the process using VBA code. First, we'll ensure your form's tab order and cycle options are set correctly. Then, I'll guide you through using Control + Tab to switch between the subform and the main form. For those interested in a programming solution, I will demonstrate how to implement key down events in VBA to enhance navigation. 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 is the primary issue that Carter from Rancho Cucamonga is experiencing with Microsoft Access forms?
A. Difficulty in creating new forms
B. Users feel stuck in the subform and cannot navigate back to the main form easily
C. Problems with database connections
D. Visual formatting issues with the forms
Q2. What is the simple, non-programming method to tab out of a subform?
A. Pressing the Enter key
B. Using the mouse to click on another field
C. Pressing Control + Tab
D. Pressing F1 key for help
Q3. For advanced users, what programming language does Richard suggest using to solve the tabbing issue?
A. Python
B. C++
C. Java
D. VBA (Visual Basic for Applications)
Q4. What does setting the "Cycle" property to "current record" achieve?
A. It enables the records to be deleted automatically.
B. It allows continuous tabbing within a single record instead of moving to the next one.
C. It automatically saves the record after exiting the subform.
D. It restarts the database connection.
Q5. In the VBA solution, what is the key code for the Tab key?
A. 7
B. 8
C. 9
D. 10
Q6. What must be done in the VBA code to prevent Access from processing the tab keystroke normally?
A. Use a DoCmd command
B. Set keyCode equal to zero
C. Use a While loop to break the process
D. Set an If condition to false
Q7. Why does Richard prefer writing two separate If conditions in VBA rather than a compound condition?
A. To save memory space
B. To account for short-circuit evaluation in VBA
C. To facilitate easier debugging
D. To enhance system security
Q8. What type of Access form property must be modified to swallow the tab keystroke using VBA?
A. Form layout property
B. KeyPress property
C. KeyCode property
D. Conditional formatting property
Q9. What additional function does Richard mention to navigate between the form sections without using the tab key?
A. F3
B. F4
C. F5
D. F6
Q10. How should you deploy the VBA solution to effectively set focus on a field on the parent form?
A. Use parent.setFocus with a specified field name
B. Use a simple print statement
C. Modify the form directly in the layout view
D. Use Shift key combination
Answers: 1-B; 2-C; 3-D; 4-B; 5-C; 6-B; 7-B; 8-C; 9-D; 10-A.
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 focuses on solving a common issue in Microsoft Access: how to tab out of a subform. I'm Richard Rost, your instructor for today. If you've ever felt stuck in a subform, unable to navigate back to the main form smoothly, you're not alone. This tutorial will show you two methods to make this process seamless, using either simple keystrokes or a bit of VBA for those more comfortable with programming.
Carter from Rancho Cucamonga, California, brings us today's question. He mentions that users often feel trapped in a subform and must rely on the mouse to return to the main form. The good news is that there are two solutions. The first method, suitable for beginners, involves using specific keystrokes to navigate out of the subform. For advanced users, I'll show you how to accomplish this with a little VBA.
Before jumping into the methods, let's cover some essentials. It's crucial to understand the tab order, tab stops, and how the tab cycle functions. If you're not familiar with these concepts, you might find it useful to watch some introductory videos on these topics. For those interested in the VBA method, make sure to check out videos on the key down event, which demonstrate how to handle keypresses in Access. These resources are available for free and provide a solid foundation before you tackle the VBA solution.
Let's begin with the beginner method. In my sample database, we have a Customer form with a subform for contacts. By default, the subform is not a tab stop, so pressing the tab key skips over it. To change this, we'll modify the subform properties in Design view to make it a tab stop and adjust the cycle property so that it loops back to the current record.
With these changes, pressing the tab key now takes you into the subform, but when you reach the end, you get stuck. The solution here is simple. By pressing Ctrl + Tab, you can move focus from the subform back to the main form. This keystroke acts like the tab key between different sections. For smoother data entry within the same record, set the cycle to "Current Record" on the parent form's properties. This way, using the tab key will cycle within the current record rather than moving to the next one.
Now, moving on to the programming method using VBA. For developers, I recommend setting up a key down event in the subform. When the tab key is pressed, and you are on a new, unsaved record (indicated by a null primary key), use the code to move focus back to the main form's desired field. Remember to set the key code to zero to prevent the keystroke from taking default action. This method requires understanding how to handle focus and keypresses programmatically.
Some final coding tips: Avoid combining conditions with potentially lengthy evaluations in a single line, as VBA evaluates each condition fully. Instead, break them into separate statements for efficiency.
That's how you can allow smooth tabbing out of a subform in Access. This problem has been requested many times, and I hope this helps you in your projects. A full video with detailed step-by-step instructions is available on my website. Check the link below for access.
Live long and prosper, my friends.
Topic List
Tabbing out of a subform in Access
Keystroke method for subform exit
VBA method for advanced users
Setting subform as a tab stop
Changing the cycle to current record
Using control tab to exit subform
Control shift tab for previous control
Swallowing keystrokes in VBA
Key down event for tab key detection
Parent form focus with VBA
Short circuit evaluation explanation
|
