Open Date Picker On Load
By Richard Rost
2 years ago
Open Date Picker on Form Load in MS Access In this Microsoft Access tutorial, I will show you how to automatically open the date picker for a date field when a form loads. We will cover why the standard commands might not work and explore an effective solution using the timer event and send keys. Shloma from YouTube (a Silver Member) asks: The ShowDatePicker command doesn't work from the Form On Load event. How can I get it to pop up in the first focus control when the form opens? MembersThere is no extended cut, but here is the database 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, open date picker, date picker form load, form on load event, automatically pop up date picker, VBA programming date picker, date picker got focus event, set focus date picker, timer event date picker, run command show date picker, form open event, tab order date picker, Microsoft Access tutorial, date field focus
Intro In this video, you'll learn how to automatically open the date picker in Microsoft Access when your form loads. I'll show you why the usual GotFocus event doesn't trigger on form open, and explore alternative techniques using timer events and the SendKeys method. We'll discuss setting tab order, using DoCmd.RunCommand for the date picker, managing timer intervals in VBA, and how to programmatically set focus to a specific control. You'll also see how to use button controls to trigger events, and get an overview of using the OnCurrent event to keep the date picker responsive as you move through records.Transcript Today I'm going to show you how to open that date picker tool, the little calendar there where you can pick a date, right, the date picker, when your form opens. So if you got the date picker maybe in a, you know, the first form field and you want to automatically pop up in the user's face, well, we can do that. I'm going to show you how to do it in today's video.
Today's question comes from Shloma, one of my YouTube silver members. See, I do read the comments on YouTube. I don't read them as often as I read the forums on my own website, but I try to get to them a couple of times a month. Shloma says the show date picker command doesn't work from the form unload event. How can I get it to pop up in the first focus control when the form opens?
Okay, let's see if we can do that. I'll tell you right now, what you think would work isn't going to work, but there is a trick and I'll show you the trick in just a few minutes. But let's get some background first. Shloma posted this as a comment on my open date picker video, so if you have not watched this video yet, go watch it first so you understand what this is.
And of course, this is a developer-level video. What does that mean? That means if you haven't done any VBA programming and you want to learn, go watch this video first. It teaches you everything you need to know to get started in about 20 minutes.
Okay, here I am in my TechHelp free template. This is a free database. You can download it off my website if you want to. And in the other video, I taught you that you can make an event so when this guy gets the focus, it pops open the show date picker automatically. So if you're just tabbing, going tab, tab, tab, tab, tab, tab, pop open the box right there. And we can do that.
There are a couple of ways you can do it, but the easiest way is with a got focus event. So click on the box, go to events, go to on got focus, dot, dot, dot, that opens up the code window. Let me slide it over here. And now we're going to say do command dot run command, accmd show date picker. There it is. Okay. That's it. That's all you need, that one line of code. Save it.
Come back over here. Let's close it. Close it. Open it. And then I'll just click on the box. And there it is. Or if you're up here, and you go tab, there it is. There it is.
Okay, now what Shloma wants to do is he wants to pop that open when this guy first opens. Okay, so let's try moving the customer since field to the top of the tab order. That's the thing that makes the most sense. Let's make that the first field. So just visually, I'm going to slide all these down and let's move customer since. That's our date field. Let's put it up here and let's make sure it's at the top of the tab order. So it's the first field that gets the focus when the form opens.
All right, so let's go to form design. We'll go to tab order. We'll find customer since and we'll drag it up top. Are you up there? Okay. And if you don't know about tab order, I got a whole separate video on it. I'll put a link down below. That's a beginner topic. All right, let's close this and save changes. Yup. And open it back up again and it didn't fire the event because the box didn't really get focus.
Getting focus is really like a user event. The get focus doesn't fire unless you tab to it or you click on it. The form load event and I'll save you some time, the form open event won't fire it either. Here let's test it.
Let's go into here. Let's go to the form's properties and events and we'll try onload first. All right. We should be sitting in that got focus event. So let's just try putting this command in form load. Save it. Come over here. Let's close it down and open it and see, show date picker isn't available now.
All right, just for the sake of it, let's try it in the form open event. So I'm going to cut you out and delete. Actually, let's go over here and just pick form open, pick that, and let's try putting it there. All right, and delete, and save it. And nope, still not available. Still can't do it.
All right, so we can't use this in either the load or the open events. Well, I could do it in a button. We could make a button to do it. Let's just copy this button here. And I'll put the code in here. Let's try this. Let's say do command dot go to control. And the name of it is customer since. It should already be there, but let's just make sure. Because when we click on the button, it's going to move the focus. OK, now let's put our command in there. Copy and paste.
In fact, I'm curious. I haven't tested this, but I want to see if just going to that field fires the event. Let's see. Let's see. Ready, click. OK, it does fire the event if you do it from a button, without having to put the event in there twice. Because you could put it in the button code too. Okay, alright. But that's still not what we want. We want this to pop open automatically when the form opens.
How about if we tried a timer event? Maybe if we use the on timer event and like maybe one, you know, maybe 100 milliseconds, a tenth of a second after the form loads, then we try that. Let's see if that works. Let's try that.
All right, so make sure we don't have anything in here. Let's go down to the timer event on timer. We're going to set the timer interval to 100. Okay, remember, these are milliseconds. And if you're not familiar with the on timer event, again, I got a whole separate video on it. I'll put a link down below.
All right, so essentially, the form is going to load 100 milliseconds later, a tenth of a second, instantaneous as far as the user is concerned, it's going to run the timer event. Now the last thing we have to do when we're done with this event is say me.timerInterval equals zero. That turns off the timer event. Otherwise, this will run every tenth of a second. We don't want that. We want it to run once and stop. But now let's try putting our command in here and see if this works.
We know we can't use it when the form loads. How about a timer event?Try that. All right, come back out over here, close it, close it, and open it, and oh, look at that! Isn't that nice? See? You can use a timer event. A lot of good things can happen with that ultra-quick timer event because, like for example, I show in another video, if you've got, let's say, a continuous form that's got thousands of records in it that it loads up, it could seem like the form freezes while it's loading all those records.
So what you can do is, you can load up the form with a smaller set of records, or no records in fact, and then in the timer event, load the records by setting the record source property. There's all kinds of tricks you can play with timers. And this will work even if you don't have that as the first field in the form. Let's say hypothetically this was still back down here. Put everything back the way it was, like this.
And let's put customer cents back in the tab order after family size. So tab order, where's customer cents? Come here. Come down to the bottom. Where's families? Right there. Hit OK. Now what we have to do in our timer event, though, is we have to say, before we do that, when the timer event kicks off, we're going to say, customerSense.setFocus. In fact, I'm curious. I haven't tried this yet. That might be enough to fire that event because it's in the gotFocus event, too.
Let's try it. I haven't tried this yet. I knew we could do it with this, but let's see. I'm just curious. Oh yeah, that works perfectly. See, all you've got to do is go to that control, and it will fire its onGotFocus event. So it seems like the GotFocus event just won't run in the form load or the form open. All right, so we don't even need that line there. All you've got to do is go to that control and turn the time off if it's not the first field in the tab order. If so, then use that.
Another method that I was thinking about that also does work, I'm not going to run through it though, is you could use send keys. I know, I know, I know. Send keys is one of those things that you try not to use it, but sometimes it's handy. You could do the same thing. You don't even have to use a timer event and get rid of the timer interval. OK, it's set to zero.
What you could do when the form loads is you could go to family size, which is the field before it. Oh, someone's beaming in. Hold on. All right, so you could set the focus here and then send keys tab. It's cheesy, but it works. Watch this. Let's go back to either one of the open events. Let's go to the unload event. Here we go. Unload. Either one works. All right, we could say family size dot set focus. And then we could say send keys a tab. And tab is this, inside of curly braces. OK, save it. Throw in a debug compile once in a while. I just hit a new funny. Throw in a debug compile once in a while. I want someone to make me a shirt that says that.
All right, here we go. Close it, close it, open it, and boom, there you are. So you find the field before it and just send keys a tab. That's the cheesy one. I actually like the timer event better myself because if you've got anything else going on automated-wise, send keys is dangerous. Be careful with it. Never use send keys in any kind of automated database solution. If it's something where the user's actually working with that form, OK, fine. I'll allow a send keys. Like I use it for turning on and off like num locks and stuff.
But there you go. There's two different methods. You can either use a timer event or you can use send keys, whichever one you want. That's up to you. They're your Legos. I just show you how to put the pieces together. You can put them together. Alex was just telling me they released new Lord of the Rings Legos, so he's all excited about that. So I'm looking forward to seeing the pictures that he posts for those.
Oh, and remember, if you want this to happen not only when you open the form, but when you move from record to record, use the onCurrent event. That event fires when you move from record to record. Because right now it's only firing when we open up the form the first time. If you put that in the onCurrent, it will always jump there and fire open that date picker for you. I'll put a link to my onCurrent video down below.
Got lots of videos for you guys. While I got your attention, if you're sick of the built-in standard date picker, yeah, it's functional and it's kind of bland, I've got my own template. It's a date time picker template. I've got calendars like this which you can customize the colors and make it look all pretty. And I've got time pickers including an analog clock and digital clocks. You can do this format, this format, that format. And these will pop up anywhere in your database, any form you want to use them on, any field, and it will get the value and return it to whoever called it.
So that's pretty cool stuff. And if you want to learn everything there is to know about working with dates and times and calculations and reminders and moving parts and molecular structures and atomic physics, no, I'm just kidding about all that stuff. But I do have a thing called the Date Time Seminar. It's basically a collection of all of my great lessons on dates and times and all kinds of stuff. And it's long. It covers tons of stuff. And there's all kinds of code you get. And you can do holidays and exclusion dates and figure out all kinds of weird stuff.
So it's really really cool. Check it out. It's on my website. I'll put a link to this down below. And so yeah, there you go. I hope you learned something. That's going to be your TechHelp video for today. Live long and prosper my friends. I'll see you tomorrow for another one.
TOPICS:
Opening the date picker when the form opens
Using the GotFocus event for form fields
Implementing DoCmd.RunCommand for date picker
Setting tab order for form fields
Using the OnLoad and OnOpen events
Using button controls to trigger events
Using the OnTimer event to delay actions
Adjusting timer intervals in VBA
Setting focus on form controls
Firing focus events programmatically
Using SendKeys to simulate tab key press
Ensuring event-driven actions with OnCurrent eventQuiz Q1. What is the main goal of the video tutorial?
A. To create a new date picker tool from scratch
B. To make the date picker automatically pop up when the form opens
C. To add multiple fields to a form
D. To design a new form layout
Q2. Which event does Shloma report as not working for triggering the date picker on form load?
A. GotFocus event
B. OnCurrent event
C. Form Unload event
D. Form Click event
Q3. What is the correct line of code to open the date picker when a control gets focus?
A. DoCmd.RunCommand accmdShowDatepicker
B. DoCmd.ShowDatePicker accmdRunCommand
C. DoCmd.SetFocus ShowDatePicker
D. RunCommand accmdFocusPicker
Q4. Why doesn’t the GotFocus event work as intended when placed in the Form_Load event?
A. The form must be saved first
B. The form does not recognize GotFocus as a valid event
C. GotFocus does not fire unless manually triggered by the user
D. The date picker tool is not compatible with the Form_Load event
Q5. Which event was successfully used to automatically open the date picker after form load?
A. OnClick event
B. OnTimer event
C. OnActivate event
D. OnDoubleClick event
Q6. How would you set the timer interval to run a command 100 milliseconds after a form loads?
A. Me.TimerInterval = 100
B. Me.SetInterval = 100
C. Me.OnInterval = 100
D. Me.TimerStart = 100
Q7. What is one reason given for preferring the Timer event over SendKeys?
A. Timer event is simpler to code
B. Timer event is more reliable for automation
C. Timer event does not need any extra control properties
D. SendKeys cannot be used in VBA
Q8. What additional step is recommended when using the OnTimer event to prevent it from running continuously?
A. Disable the control manually
B. Reset the TimerInterval to zero
C. Remove the GotFocus event
D. Close and reopen the form
Q9. What should be used if you want the date picker to pop up not only when the form opens, but also when moving from record to record?
A. OnLoad event
B. OnCurrent event
C. OnOpen event
D. OnActivate event
Q10. What does the presenter recommend if the standard date picker tool is too bland or limited?
A. Creating a new VBA module for the date picker
B. Using their custom date time picker template
C. Looking for different software
D. Installing third-party date picker tools
Answers: 1-B; 2-C; 3-A; 4-C; 5-B; 6-A; 7-B; 8-B; 9-B; 10-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 open the date picker tool when a form is opened in Microsoft Access. If you're working with a form that has a date field and you want the date picker to pop up for your users as soon as the form opens, I'm going to explain a few techniques for making this work, including some of the common pitfalls and the best methods to achieve this behavior.
The question for today came from a YouTube viewer asking how to get the date picker to appear automatically in the first focus control when the form opens. Now, if you're not already familiar with programming in VBA, I recommend reviewing my beginner-level Access VBA tutorial that covers the basics in about 20 minutes. It's important to understand those concepts first, since we'll be working with event handlers and VBA code.
A bit of background: in one of my earlier videos, I covered how to set up a control, such as a date field, so the date picker automatically opens when that control receives focus. We typically accomplish this using the control's GotFocus event. When a user clicks into the field or tabs to it, we use a simple command to show the date picker.
To review, you create an event procedure for the GotFocus event on the control, and inside that procedure you use the appropriate command to display the date picker. This works fine if you want the date picker to appear when the user navigates to that field manually. You can test this by simply clicking the field or tabbing into it and you'll see the picker appear just as expected.
The next logical idea is to set your target date field at the top of the form's tab order. You might suspect this would make it receive focus as soon as the form opens, triggering the GotFocus event and thus displaying the date picker. However, upon testing, you'll find that Access does not actually fire the GotFocus event for that field when the form first opens. The event is only triggered by user actions like tabbing or clicking, not by the form loading or opening. Similarly, placing the code in the form's OnLoad or OnOpen events does not work either. Attempts at using these events confirm that the date picker command is simply not available at that point in the form's lifecycle.
Another approach is to use a button to move focus to the desired field. Putting code in a button's click event to move the focus will trigger the GotFocus event and display the date picker. This verifies that the logic works, but this requires user interaction with the button and does not solve our requirement to automatically show the picker right when the form is opened.
The workaround—and the one I recommend—is to use the OnTimer event of the form. With this approach, you assign a short timer interval (such as 100 milliseconds), which is nearly instantaneous for the user. When the timer fires just after the form is opened, you can set the focus to the date field and display the date picker. Importantly, once the task is accomplished, reset the timer interval to zero to disable the timer and prevent repeated firing. This method reliably triggers the GotFocus event immediately after the form opens and works regardless of the field's position in the tab order.
If your date field is not the first in the tab order, you can simply instruct your timer event to set focus directly to that specific control. Doing so consumes the OnGotFocus logic already implemented there, and the picker appears as desired. There's no need to duplicate code; the event will be triggered naturally when focus is set programmatically.
Another method that works, though less elegant, is to use the SendKeys function. In this method, you set the focus to the control immediately before your date field, then send a simulated Tab key press, moving the focus to the date field, which fires the GotFocus event. While this works, it is less reliable and prone to issues, so I recommend it only for lightweight applications where other automation is not present. SendKeys is generally discouraged in complex or automated databases.
As a tip, if you want this automatic date picker popup not only when the form opens but also every time you move between records, use the form's OnCurrent event. This event fires whenever the current record changes, so placing your code here will ensure the date picker appears each time the user navigates to a new record.
Finally, if you are looking for more customization than the standard date picker provides, I have developed my own date and time picker templates which are highly customizable and can be reused across your databases. There is also a comprehensive Date Time Seminar available on my website, which covers working with dates and times in depth, including advanced calculations, handling holidays, dealing with reminders, and much more.
For a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.Topic List Opening the date picker when a form opens
Using the GotFocus event to trigger the date picker
Implementing DoCmd.RunCommand to show the date picker
Reordering fields with tab order for focus control
Testing OnLoad and OnOpen events for automation
Triggering events from button controls
Using the OnTimer event to delay date picker display
Adjusting timer interval values for precise timing
Setting focus on controls programmatically
Using SendKeys to simulate tab key press
Applying the OnCurrent event to repeat actions on record change |
