Custom Date Picker 2
By Richard Rost
2 years ago
Build a Custom Date Picker in MS Access, Part 2 In this Microsoft Access tutorial, I will show you how to craft a custom date picker, integrating it with forms, and creating a global function for simplified coding. From handling dialog windows to assigning values with TempVars, this is part 2 of a comprehensive mini-series for enhancing user interfaces. This is part 2 of 2. MembersIn the extended cut, we will learn how to create a cancel button that prevents resetting the value in the field initially started with, and we will also add functionality to bring the starting value from that field into the date picker form. 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!
PrerequisitesRecommended Courses
Keywords TechHelp Access, custom date picker form, add subtract days weeks months, update date values, return value from field, getdatepicker, save date to tempvar, open form AC dialog, user select date field, button date selection, default value date fields, on double-click event, form design customization, disable built-in date picker, button with calendar icon, subform value return, extended cut features, cancel button handling, initial value in date picker.
Intro In this video, I will show you how to complete a custom date picker form in Microsoft Access using VBA. We will create a global function to open the date picker form as a dialog, return the selected value to any field, and use TempVars to manage data transfer. I will demonstrate how to add the picker to forms through double-click events or buttons, color-code fields for user guidance, handle pop-up positioning, and return values to both main forms and subforms. You will also learn how to disable the built-in date picker and apply these techniques for flexible data entry. This is part 2.Transcript Today is part 2 of my custom date picker mini-series, Part 2 of 2. So, if you haven't watched Part 1, go watch that now. You'll find a link down below, and then come on back. We now join the... yeah, okay, yeah, you get it, okay. All right, so yesterday, we built this guy, a little date picker pop-up form. Put a value in here; we can make little buttons and make changes; you got that.
Okay, now we have the okay button, and then the okay button, all it does is it saves that value, and it would temp our, and then it's going to, now we're going to return that value to whoever called it. Now, to make it easy, so we don't have a lot of coding all over our database, we're going to make a global function. So come down here to your global module or make one if you don't have one. Here's mine. Make this a little bit smaller here so we can see it. Come here.
All right. So down here, we're going to make our own public function called getDatePicker. It's not going to take any values in, but it's going to return a date value. Okay? Now when we call this function, what it's going to do is it's going to open up the form. Do command.openform, date, picker, F, comma, comma. Here's the important part, comma, comma, comma, comma. No, I'm just kidding. All right. When we get over to window mode, you can't see it because it's kind of off the screen here. Let me slide over.
These little things. At window mode, what you're looking for is AC dialogue. We talked about this in my message box series. What dialogue does is dialogue tells VBA, you're going to open this form, and you're going to stop. You're not going to do anything else until the user closes that form. And then at that point, the VBA in here can continue on.
Now, what does that form do? That form has the user put a date value in a field. And then the OK button assigns that value into a temp var. So here we can grab that temp var and assign it to the value of the function. So we're going to say getDatePicker equals temp vars["datePickerValue"]. That's the one we set in the form. So now we can return that value to whoever we want. Like a field on a form.
So now, save it, debug compile, always throw in a debug compile, I mean, now and then, folks. Let's do this field right here, all right? Design view. Now, this guy's got a static value in it, so let's go to data, and right here, let's get rid of that control source, delete. You can put it in the default value if you want you put it down here Equals date, like that. The difference is if it's in the control source, then that field is stuck on that date. It's always going to be today's date if you put it in the default value, though. You can change it. It just starts at that.
All right, we'll call this here. Just, you know, something whatever, so this guy is the current date. Let's change that out, like the current date. Let's call this just some date field. Okay? So what we can do is you can do it one of two ways. You can make a little button to go next to it, or you can make it a double-click event. I'll do it a double-click event. Let's make it so it's blue. And this is kind of a training issue. And if you've watched any of my other videos, you know that when I build forms, like the customer list form, like this guy, it's a training issue that I teach all my users. If you see something that's that shade of blue, you can double-click on it, and it does something else. Like in this case, it opens up another form. Same thing with date fields. It can make it so that you teach your users if it's blue, you double-click on it, and it'll do something. In this case, it's going to open up our date picker form.
All right. So in the event, on double click, where are you? On double click, right there, dot, dot, dot. Now, the way we've got this set up is we don't have too much coding in each one of these fields. All we need in here is some date field, that's the name of the field, equals get date picker. That's it. That's all you got to worry about at this level. So you can do this to any date field in your database that you want. Right?
Get date picker form, wait, the user will pick a date. Hit okay, it saves it in the temp var. The temp var comes back to the function, the function comes back here, and there you go. Save it. Open her up. Okay, it starts with today's date, double-click. There's the dialog. One problem with dialog boxes is they're like pop-up forms. If you've got a multi-monitor setup, it could pop up anywhere. For me, it popped up on a different screen, my lower screen. Sammy, make sure that's on the list for the access team. Pop-up forms should stay centered over the application window. That's one thing that's one of my little pet peeves with Access. But anyways, we could change the value here, hit okay, and it brings it back in there. See that? Isn't that nice? Isn't that cool? You can do the same thing with a button but a button next to it. I do that sometimes too, right? Wanted a little button, copy-paste. I guess I'd issue to make it like that. You can put an icon in here if you want to. Right?
Let's see here, exchange this too dp for a date picker, and then if you want to put an image in there. Right? As a picture, dot, dot, dot, you can pick, I don't know, what do we got in here, this little calendar picture, right, like that. You can do this, right, stick that there, and if you want to turn off the built-in date picker, what's that under? It's under format, I think. Yeah, show date picker, leave that to never, because it's either never or for dates, just leave it to never, and then your button will handle it. Right? Right-click, build event, same code goes in here. Some date field equals get date picker. Just like that. Save it, close it, close it, and now you've got your own date picker on the wrong monitor screen. Right? Pick a date, type one in, whatever, hit your other buttons, hit, you can even put D lookups in here to find stuff like what's the latest order date, whatever you want to do, it's your form, they're your Legos. Hit okay, puts it right back in there.
You can do the same thing on another form. All you got to do is just put the coding in, one line of code. Let's do the customer sense field. Make it blue, event, on double click, where are you? On double click, right there. Customer Sense equals get date picker like that. All right, close her up, open it, double-click, put that there. If you save it there, it usually tends to stick. Sometimes it doesn't though. Boom, there's the value. See, double click, there it goes.
All right, now the nice thing with this also is one of the things that Sammy mentioned is that sometimes it's a hard time bringing values back to a subform. OK? In fact, in my original date picker template, which I'm going to show you in a minute, I used a different technique for storing the data back in the form, and that has a problem with subforms. So watch this. Design view. Now, you don't have to do this with dates. You can do this with any value you want. Let's say you want to do it with the quantity field. It doesn't have to be a date. Let's do quantity.
So, like this, we'll do it as a double-click event. So quantity on double click, right there. We're going to say quantity equals get date picker. You can rename it to something else, obviously. Right? And now, go to Orders. Double-click. It's putting a date in there. Let's just put a 6. Right? Hit OK. And OK, it's looking for a date value. That's fine. We'll put in here 1-1. Hit OK, and boom. It brings the value back here. That's the point I'm trying to make is. Yeah, it's formatted for a date. But you can easily make it just a regular text box. You can put any kind of value in there that you want. But the point here is that it brought it back into the subform. See?
So, that's pretty straightforward, pretty easy to do, huh? Alright, if you want to learn more, in the extended cut for the members, we're going to do two things. We're going to make a cancel button, so if the user hits cancel, it doesn't reset the value that's in the field that you initially started with, okay, and we're going to add it so that it'll bring the starting value in from that field to the date picker form. So, for example, I don't know if you caught it earlier, but if you're starting here and it's 1987, November 1st, and you double-click, it's going to come in here with today's date. So now you got to go all the way back to find it. Okay, so we're going to make it so it takes this value and puts it in here to start with, and we'll make a cancel button. That's covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos, and gold members can download these databases and get access to the code vault.
And on my website, I've got a cool template that I built a while back where it's got a simple calendar replacement for the built-in Access date picker because there was a short time in there where Access had a date picker, and then it didn't have one, and then they added the new one that it has now. So I built this in that interim. I also have a bunch of cool time pickers with clocks and stuff. These are all on my website. Here's the address right there. I'll put a link down below. Check it out if you're interested. And of course, if you like learning this stuff with me, if you're enjoying learning and programming with me, come check out my developer lessons. I got hundreds of hours of lessons on my website. You'll find a link right there. Click on it. If you have any questions, feel free to post them in the comments down below. But that, my friends, is going to be your TechHelp video for today. I hope you learned something. Live long and prosper. I'll see you next time.
TOPICS:
Custom date picker creation
Programming an "OK" button in VBA
Using TempVars in Access VBA
Creating global functions in VBA
Working with the Command.OpenForm function
Utilizing window mode AC Dialog
Returning values from a form field
Debugging and compiling code in VBA
Editing control source properties
Implementing default value property
Color-coding fields for user interaction
Creating double-click events on fields
Passing values back to fields from forms
Using date picker with subforms
Troubleshooting popup form positions
Customizing form buttons with icons
Disabling built-in date picker
Coding best practices for reusability
Returning values to subforms
Extended cut for members: Cancel button
Extended cut for members: Preseting initial field valuesQuiz Q1. What is the purpose of creating a global function for the date picker in Access?
A. To reduce the amount of coding needed throughout the database
B. To make the database run faster
C. To enable multi-user access to the date picker form
D. To increase the security of the date selection process
Q2. What does the 'AC Dialog' window mode do when opening a form in VBA in Access?
A. It minimizes all other open windows in the database
B. It allows the user to open multiple instances of the form
C. It halts VBA processing until the user closes that form
D. It resizes the form to fit within a predefined area
Q3. How is the selected date from the date picker form returned to a calling function?
A. By assigning it to a temp var and then to the function's return value
B. By direct assignment from the date picker form's field to the calling function
C. By using a query to fetch the date from the form
D. By copying and pasting the date into the desired location
Q4. What change is suggested to ensure that the date picker pop-up form appears centered over the application window in multi-monitor setups?
A. To hardcode the form's position in pixels
B. To use the Moveable property of the form
C. To contact the Access team to address the issue
D. To always drag the form to the center before using it
Q5. What is the main advantage of using the getDatePicker function in the way it's implemented in the video?
A. It allows for the date field to have a static value
B. It enforces consistency of date formatting across the database
C. It enables simplified code for assigning dates to fields in the database
D. It prevents users from picking past dates
Q6. What is the difference between placing a date value in the 'control source' versus the 'default value' for a field in Access?
A. Control source binds the field permanently to that date, default value allows for changes later
B. Control source allows for future edits, default value does not
C. Default value is always today's date, control source can be any date
D. Control source dictates the field's format, default value determines the starting value
Q7. What additional functionality is introduced in the extended cut for members?
A. Adding a quick-pick calendar
B. Implementing range selection of dates
C. Adding a cancel button and bringing the starting field value into the date picker
D. Allowing the function to handle time values as well as dates
Answers: 1-A; 2-C; 3-A; 4-C; 5-C; 6-A; 7-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 TechHelp tutorial from Access Learning Zone covers part 2 of my mini-series on creating a custom date picker in Microsoft Access, so make sure you have already reviewed part 1 before moving forward. In the previous lesson, we built a simple date picker pop-up form that allows users to select a date and confirm the choice with an OK button. The next step is to make the form return the selected date to the field that invoked the date picker, and to do that efficiently, I will show you how to use a global function.
To avoid unnecessary repeated code throughout your database, it is best to set up a global function in a standard module. You can either use your existing global module or create a new one if you do not have one already. The function we are going to add will be called getDatePicker. It does not need to take any arguments, but it will return a date value.
The process works like this: when you invoke getDatePicker, it opens up your date picker form in dialog mode using the DoCmd.OpenForm command. Specifying dialog mode (using acDialog as the window mode) is essential because it pauses your VBA code and waits for the user to close the form before carrying on. This behavior guarantees that you receive the user's chosen date after they have interacted with the date picker.
When the form is open, the user selects a date, and pressing the OK button saves that value to a TempVar. Inside your global function, you can then retrieve the TempVar value and assign it as the return value for your function. This approach makes it simple to grab the selected date from anywhere in your database by simply calling getDatePicker.
Once you save your changes, always remember to compile your code to catch any errors before running. Let me give you a practical example. Suppose you have a form with a date field. Typically, this field might have a static value set either through its control source or as a default value, such as today's date. The difference is that a value in the control source locks the field, so it never changes. A default value only sets the initial value, allowing the user to change it later.
For integrating the custom date picker, you have two common options: add a button next to the date field, or use an event, such as a double-click on the date field itself. I often recommend using a double-click event, especially for fields you want to make interactive, and I even train my users to recognize any blue fields on a form as interactive—double-clicking on them triggers additional tools. This user interface training makes forms easier to use and more intuitive.
To trigger the picker, you only need a single line of code in the event. For example, in the field's On Dbl Click event, just set the field equal to getDatePicker. That is all the code required at this level, making it easy to apply this method to any date field throughout your database.
With this setup, double-clicking the field opens the picker, lets the user choose a date, and pressing OK returns the selected date right into the field. There is one limitation, though: Access pop-up forms can sometimes open on the wrong screen if you have a multi-monitor setup, which is something I have suggested to Microsoft as an improvement for future versions.
If you prefer a button instead of a double-click event, you can easily add a button control next to your field and use the same one-line code in the button's On Click event. To enhance the button, you can even add a calendar icon. If you want to disable Access's built-in date picker so it does not interfere, set the Show Date Picker property to Never in the format section of the property sheet for that field.
You can reuse this method for any similar scenario. Just set the relevant field (such as "SomeDateField") equal to getDatePicker in its event procedure. The picker form takes care of collecting and returning the value, making your code reusable and easy to maintain. This also works nicely when returning dates to fields on subforms, something that was more challenging with older methods due to how data was returned to the parent form.
While this tutorial focuses on dates, you can repurpose the structure for other data types too. For example, if you wanted to use the picker for a quantity field or something similar, simply wire up a similar event with the relevant field and handle the returned value appropriately.
If you are a member, the extended cut of today's lesson covers two enhancements. First, I will walk you through adding a Cancel button to the date picker. This way, if the user cancels the picker, the original field value is preserved and not overwritten. Second, I will show you how to preset the initial value of the picker with the value already in the field when it opens, saving users from having to reselect the current value if only a minor edit is needed.
You can also explore more on my website, where I have a full-featured calendar template that serves as a solid alternative to Access's built-in date picker. I developed this during a time when Access's default date picker was not available, and it still provides a lot of flexibility. In addition, there are other time picker solutions available too. You can find the links on my website.
If you are interested in learning more about Access, VBA, or overall database design, take a look at my developer lessons. I have extensive resources and video tutorials that cover a wide variety of topics in detail.
A complete video tutorial with step-by-step instructions covering everything discussed here is available on my website at the link below. Live long and prosper, my friends.Topic List Custom date picker form integration in Access
Creating a global GetDatePicker function
Returning selected date from a pop-up form
Using TempVars to store user input in forms
Opening forms in dialog mode with acDialog
Assigning form values from TempVars
Setting default values for date fields
Removing control source from form fields
Adding double-click events to form fields
Making interactive fields visually distinct
Triggering the date picker with a button
Adding images to command buttons
Disabling the built-in Access date picker
Returning values from the picker to any field
Applying the date picker in subforms
Applying the picker to non-date fields
Returning values to subforms with TempVars
Handling popup form multi-monitor issues |
