In today's video, we're going to talk about how to build a custom date picker. You can either click the button or double-click the date field or whatever you want to do. You get your own little date picker. You can have little buttons on it to add a day, add a week, add a month, all those kinds of little add up or subtract things. You hit okay and it puts the value back where you came from. We're going to talk about that in today's video.

Today's question comes from Madeline in Elk Grove Village, Illinois, one of my Platinum members. And I actually bumped this up the list a little bit because one of my moderators and friends, Sammy, has been working with an issue close to this, so I figured I'd put together a little database for it. But Madeline says, "I'd like to make my own little buttons to add days, weeks, or months to date fields, but I don't want to have to do this for every single date field in my database. Is there a way that I can make my own custom date pop-up picker where I can just attach this to a date field and then return the value?"

Well, sure, Madeline, and in fact, if you know the technique that I used in my custom message box video, this guy, where we made a custom message box. You can click yes, no, or whatever buttons you want. We can use a dialog form to return a value. You can use the same thing for a date picker or any kind of pop-up that you want.

So before we get into it, this is a developer level video. What does that mean? Well, it means we're going to be using some VBA. So if you've never done any VBA programming in Access before and you want to learn, go watch this video. It's about 20 minutes long. Teaches you everything you need to know to get started. Make sure you know how to use if-then statements. We're going to create our own function to return a value, so make sure you know how to build your own function. And make sure you've watched my tempVars video, unless you're Adam, in which case you probably haven't watched it yet. But these are all free videos, they're on my website, they're on my YouTube channel; go watch those and then come on back.

Okay, so here I am in my "TechHelp" free template. Oops, this is a free database. You can grab a copy of it off my website if you'd like to. And let's say I got a bunch of different dates in here. Here's a date field right there. Let's say the customer's got a date field here. We've got date fields on our order form. We've got date fields all over the place. And I want to be able to have my own custom set of controls.

So instead of this boring little pop-up here, right, I would like to make my own. That is a little pop-up form, and I can have little buttons to add a day, add a week, add a quarter, add a year, you know, add four, whatever. All right. You don't want to work with this; you want to make your own. I get it.

So let's see how we can do that. Let's start by putting together our own little date pop-up form. So, let's make—I'm going to copy my single F that I have here. It's just a blank single form. Copy-paste, control-C, control-V. We'll call this the DatePickerF. The date picker form, where are you? Right there. Let's go to design view. We can get rid of that ID right there, and we'll make this our date value. So we'll just put in here "date" like that. Okay. And we'll slide it up a little bit, maybe move it over a little bit like this. Maybe set the format if you want to. Well, let's give it a name too first here.

What do you want to call this thing? Let's just call it "my date," right, and the format will be short date. I like the ISO date format. I am on a mission to change the world to this date format—your month, day. It's the one that makes the most sense, and there's no ambiguity between it. Watch this video for details. After I get the world changed to this, I'm going to work on the 28-day month, but that's a whole other story. And since I have Windows set to the ISO date format, my short date should come in in that format in my Access database.

Alright, let's make this a little bit smaller, like so. Let's get rid of all the stuff we don't need because right now if we save this and take a look at it in Form View, we've got navigation buttons, record selectors, all this stuff. Let's get rid of all the stuff we don't need. So let's go to Format, click on the Form Properties here, go to Format, turn off the Record Selectors, Navigation Button, Scroll Bars. Now, I'm going to turn off the Control Box and the Close Button because I'm going to put my own OK button here that the user is going to click on, and that will close the form and do some stuff, including returning the value. I don't want them to hit the X up here, alright? Yeah, I know they can still hit Control F4, but A, most users don't know that, and B, we can intercept that key keyboard thing if we wanted to—the key press if you want to—but for now, let's not worry about that. So let's put an OK button on here, right, Form Design, grab a button, we'll put it right down here, that'll be my OK button.

Or in a Cancel the wizard, as a wizard doesn't do this, so that okay, just "OK" there. Now here's where you can put all the other little controls that you want. Let's start this date value off here with a default value of "equals date." So it'll put today's date in that box to start with. But then we can make other little buttons to change it. I like stuff like this. Watch this. Copy, paste. Let's put a little button right there. Come here. There you go. We'll put in here "+1D," like that. That'll be plus one day. Make it nice and tiny. Okay, now give this button a name. Alright, let's call it "+1D" like that, and then we'll right-click, build event, and there's my code builder, right, and here we'll say in it "my date equals my date plus one." Remember, in date math in Access, one equals one day. You want to add an hour; you can add 124th or use the date add function. And you can do the same thing for minus a day, plus a week, plus three days, plus a month, whatever. You can put all kinds of buttons in here that you want, whatever buttons you like, line them up.

So when you're all done, you've got something that looks like this. We'll center it over here where we want it, and now we can hit the plus one day, hit minus a day, plus a week, whatever. All these buttons, you get it. I've done other videos on how to do these before. In fact, here's the video for it. You can take this guy, this form that I made here, and make this the pop-up that we're doing today if you want. Look at all those different buttons. You can even pick what kind you want: month, year, whatever. The user can go and change the date, whatever you want in here. In fact, you can even use this little guy too if you want to. There's nothing stopping you from doing that. Leave that on there too. More options the better.

Okay, so what's this "OK" button going to do?

Okay, now the form has to return the value that's in that "my date" box to whoever called it. Okay, now the easiest way to do that is to use temp vars. We're going to set a temp var in here to that date, and then whoever called that form can just read that temp var. Because forms by themselves don't really return values, but this is kind of a cheat to do that. We'll make the form act like a function, right?

So, go to design view and inside this OK button, which it's Command Force. So let's call this the OK Button. OK Button. There we go. Right-click, build event. Alright, so in here, we're going to make a temp var. So, tempVars, what do you want to call it? Let's call it "DatePickerValue" equals whatever is in "my date" there. Now, you've got to say "my date dot value" because if you don't put the dot value, it's going to try to assign the object "my date" to the temp var, and tempVars can't hold objects, only values. Just saving you an error message there. Alright, enter. Now after that, we've grabbed the value. Now we have to close the form. Because when we open the form in a minute, we're going to open it as a dialog form. And that's going to stop everything else from happening until this form is closed. So do command dot close, acForm, me dot name. That just saves you a step from having to put the actual name of the form in there. Comma. And then we're gonna put ACCloseSave. Yes, there's a long story behind that. I'm not gonna go over it again. I've covered it in about 15 videos. Okay, if you're curious, post a comment.

Save this. Close it. Close it. Now, when you open up this form and hit OK, it closes it, and it saves that value in a temp var. Now, all we've got to do is open that form and then read the temp var when it closes.

Okay.

Oh, and by the way, one other trick, if you want, while you've got this thing open here, if you want to make this the default button, right-click, design view, open up its properties, go to other, set default to "yes." When the user presses the enter key, it'll push that button, right? There's a video on that one.

And hint, yes, we can add a cancel button here if you want to. We'll do that in the extended cut.

Alright, so close that, save it. Now we've got our form built where we can put a date in here and little buttons to do whatever we want. Hit OK. Now the value is saved in a temp var. Now all we have to do is make a custom function to get that value from the form when it closes and put it in whatever field we want to.

And we will do that in part two tomorrow, so tune in tomorrow, same bat-time, same bat-channel, or if you're a member, you can watch it right now because I'm going to record it in just a few minutes. That's going to be your "TechHelp" video for today. Hope you learned something. Live long and prosper, my friends. I'll see you tomorrow.

TOPICS:
Building a custom date picker in Access
Clicking or double-clicking elements to activate date picker
Custom date picker popup with date manipulation buttons
Inserting the selected date back into the form field
Developing with VBA in Access
Creating dialog forms to return values
Setting up a developer environment in Access
Programming if-then statements in VBA
Creating custom functions in VBA
Using tempVars to store data across Access forms
Designing forms in Access for the date picker
Adding and formatting date controls on forms
Customizing form properties to remove default elements
Adding and coding buttons in Access for date manipulation
Utilizing date math in Access for button functions
Saving and closing forms programmatically in Access
Using tempVars to return values from closed forms
Setting default buttons on Access forms for enter key
Navigating and utilizing the design view in Access forms
Storing selected dates using tempVars
Building a custom function to retrieve and set form field values
Continuation of the tutorial in the next video part