Custom Date Picker 2
By Richard Rost
20 months 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.
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 values
Quiz
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.
|
