On Click
By Richard Rost
5 years ago
Using the On Click Event. Increment a Date Field.
In this Microsoft Access tutorial, I'm going to teach you how to use the On Click event to increment a date field by one day. We'll make labels right next to the field. You can click once on a (+1) label to add a day and (-1) to subtract a day.
Anderson from Buffalo, New York (a Platinum Member) asks: Is there a way that I could click a button to move someone's appointment date forward one day. This happens a lot in our office, and I'm sick of having to click on the field, type in a value, or click on the little popup calendar and click on the date. I just want one CLICK and it's done.
Members
I'll show you how to add a combo box with a bunch of different intervals in it (days, weeks, months, quarters, years). The date value will be changed based on the value you pick in the combo box!
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!
 Links
Intro to VBA: https://599cd.com/IntroVBA
Date Picker Control: https://599cd.com/DatePicker
Time Picker Control: https://599cd.com/TimePicker
Subscribe to On Click
Get notifications when this page is updated
Intro
In this video, I will show you how to use the On Click event in Microsoft Access to create labels and buttons that let you quickly adjust the value of a date field, such as moving an appointment date forward or backward by a day. You will learn how to add a button or label to your form, write simple VBA code to increment or decrement the date, and work with event properties. I will also cover tips for naming controls, understanding event types, and handling common issues with Access form labels and controls.
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to use the on-click event to make a label that changes a date field. See these little guys here? Plus one, plus one, plus one. Minus one.
You can use these for appointments or any other kind of date field. Today's question comes from Anderson from Buffalo, New York, my old stomping grounds. One of my Platinum members, Anderson says, is there a way that I could click a button to move someone's appointment date forward one day? That should be a question. Usually I catch those, but I just copied and pasted this from his email. Sorry, Anderson, watch your grammar.
This happens a lot in our office and I am sick of having to click on the field, type in a value, or click on the little pop-up calendar and then click on the date. There are a lot of steps involved in changing a date. I know. I just want one click and it's done.
So let's see how this works here. Here is a copy of my TechHelp Free Template. This is a free download from my website. Go grab a copy if you like.
On here, I have got a customer form and I have got a customer since field, but let's pretend this is their appointment date. So we will just use customer since, and there is already a date field there.
Now, this is going to involve one line of programming. If you have not watched my Intro to VB video, go watch it right now. That is free. It teaches you all the basics of getting into VBA programming. It is not hard. Go watch that lesson now if you have never done VBA programming and then come back.
Now, these buttons right here that I have created before use something called an on-click event. When you click on this button, it opens this form and there is an event that happens when you click on it. There is an event that happens when you click on these fields too, but there is nothing assigned to that right now.
So let's see where that event is. I click Design View. Open up the properties for the Orders button, for example. You will see right here it says on-click under the Event tab. There is also on double-click. There is on-got focus, which is what happens when you tab to this or click on a field, for example. On-lost focus is when you leave a field. There is all kinds of stuff you can do. On-click is the one you want.
One click means when I click on this object, whether it is a button or a text box or any of that stuff, do some stuff. What is the stuff that you do? Well, event procedure means we have got some VBA code, so click on the dot, dot, dot button. In here, there is already some code. I built this in a previous video. It is DoCmd.OpenForm. It just opens up the order form for the current customer.
I explained this in the template video, but let's do something else. Let's do something from scratch. So ignore these buttons for now. In fact, I am going to take these buttons and move them over here to get them out of the way. Pretend these do not exist. Let me teach you. Those are different sizes, aren't they? I didn't even catch that.
Let's see how we can make a little button to change the date in the customer since field. I want to move it ahead one day, just like Anderson wants to.
Come up top on the Design tab, find a command button right there. They used to call it Command Button. Now it is just Button. Drop it there, and we are going to cancel the wizard. What we want to do is not in the wizard. Love the wizard, it does some great stuff, but it does not do this.
Let's change the caption to plus one. There is the caption in the button itself. We will slide that right there next to customer since.
Now, it is called Command30 right now. It actually just gives it a name, Command30. Let's change this. Let's give it a good name. Let's call this PlusOneDayBTN for button.
Give it a good name. Go to events, go to On Click, and hit the dot, dot, dot button, or On Click is the default event. You can just right click on the button, and come up to Build Event. It is way up top here off the screen, Build Event.
If you have not watched my intro to VBA video, which you should have by now, then you might get a window that says What builder do you want? Pick the code builder that makes sure you get the Visual Basic window. I am right inside here in the PlusOneDayButton_Click. This is the on click event for this button.
What do I want to do in here? It is very simple. I want to increase the date of the customer since field by one day. Remember, dates in Access work like this: one equals one day. If you want to increase it a week, add 7 to it.
So I am going to say: CustomerSince = CustomerSince + 1.
That is it. Remember I said this required one line of code. That is it. One line of code. You just have to know where to put the line of code and how to get there. Once you know how to do it, it is simple.
Save it. Close this. Close down the properties. Close down the form. Open it back up again and watch this. Click, click, click, click, click, click, click, click, click, click, click, click, click, click, click, click, click, click. See Anderson? Nice and easy.
Now you do not have to click on this and hit the little calendar. There it goes. Sometimes that calendar, there is another object too. That little calendar guy, if that pop up hides, but it is still there. Then you have to come down here and click on that.
This is so much easier. Notice it is still there. It still exists there. You could turn that off by the way if that is a problem. Double click on this guy. Go to Format, find Show Date Picker, and then turn it off. Never.
If you have your own little button here, that date picker actually pops up there and it is kind of being obscured by our button. So I honestly do not like that date picker. I do not use it a lot. I have my own that I built. It is on my website, and I will put a link to it down below if you are interested. I have got one also where you can pop up the time too.
Here is my date picker. Looks a lot better. You click on a field, you hit select date, this thing pops up. Here is my time picker. Same thing, but for times. You click a button, this pops up, and you can click on 11:55 AM if you want to.
I will put links to this stuff down below. Let's say you do not want to use a button though. Let's say you want something a little fancier or you have got a lot of stuff you want to put on here and you do not want to clutter it up with buttons. Let's get rid of this button. Let's use a little tiny label.
I like to do this trick. This is one of my favorite tricks. Find a label in here, click right there, and go plus one. That is it. Let's make it blue. Like that. Just make it as small as we can but still fit, and put it next to it.
Open up its properties. Go to All. Let's give it a name: PlusOneDayLabel. Anything that I am going to put code in or refer to, I like to give it a good name.
My buddy Alex taught me that one because I used to just leave it as Command15 if I was not going to refer to it anywhere else. But it is better to name your stuff.
Click event. Dot, dot, dot. Now we are in here. We got rid of the button, so we can actually delete this code. But first I am going to copy that line. Copy and paste it right in there. Now we can delete the button stuff because I deleted the button. When you delete an object like a button, it does not delete any associated code with it, so you have to be aware of that when you are programming.
Save it. Close it. Open it back up again. Look at that tiny plus one there. Click. Click. That is because an on-click event is associated with that label.
You can associate them with fields themselves. In fact, I like to use a double-click event. I have a video coming up on double-click. I will show you how to do that. You can just double-click on the field and that will increase the date in there. That way you do not have the labels everywhere. But the labels are good if you want plus one day, plus one week. In fact, I am going to show that in the extended cut for the members. We will make little buttons here for plus a day, plus a week, minus a week, plus a month, plus a year.
You can have little values in here. One more thing I am going to note because I know some of you will get tripped up on this one. If you look at the properties for a label and you do not see any events in here, that is because you cannot tie events to labels that are bound to a text box.
Notice if I click on this guy and drag it, it moves the text box with it. That is because these two are bound together. If you want to assign a label to an event, you have to make it unbound.
So let's say, for example, I wanted to assign a label to Family Size. Just cut it and then paste it again. It will come up here but it is not associated with that text box anymore. There. See that? Now it has events. Now I can put events in there. If it is associated with the text box, it will not take events. I get asked that a lot too.
Want to add another one for minus one? Just copy and paste this guy. I am going to slide plus one over. We will put minus one on the left. We will make this one minus one like that. That is what this guy is called. That is PlusOneDayLabel. Let's make this one MinusOneDayLabel.
That should be the name of it. The event is going to be dot, dot, dot. Now I am in the MinusOneDayLabel_Click event. CustomerSince = CustomerSince - 1.
Save it. Close it. Open it. Ready? Go. Minus, minus, minus, minus, minus, plus, plus, plus. There you go. There is your on-click event.
Want to learn more with the on-click event and some VBA? In the extended cut for members, I will show you how to make this combo box here where you can pick the interval - so day, week, month, quarter, year. You want to add six months? You drop it down, pick month, click plus one, plus one, plus one, six times. I will show you how to make it a function too, so you can pass any value you want to it. If you want to add a six there, you can make it add six months. Two clicks, you are done.
That is in the extended cut for members as a reminder. Remember, as an Up member, you get access to all of my extended cut videos and there are a lot of them now.
How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available.
Silver Members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold Members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my Code Vault, where I keep tons of different functions that I use.
Platinum Members get all the previous perks, plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted. If you would like to get an email every time I post a new video, click on the link to join my mailing list.
If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just $1, and it is also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.
Quiz
Q1. What is the purpose of using an on-click event with a label or button in Microsoft Access as shown in the video?
A. To perform an action when a user clicks the object, such as changing a date field
B. To resize the form automatically
C. To close the form when the label is clicked
D. To open the help documentation
Q2. What is the advantage of using a button or label to change a date field, instead of manually entering a new date or using the date picker?
A. It allows a date to be updated with a single click, making the process faster and easier
B. It prevents the user from selecting invalid dates
C. It updates all records in the database simultaneously
D. It creates a backup every time the date changes
Q3. What VBA code is used to increment a date field named CustomerSince by one day?
A. CustomerSince = CustomerSince + 1
B. CustomerSince = CustomerSince - 1
C. CustomerSince = DateAdd("m", 1, CustomerSince)
D. CustomerSince = Now()
Q4. How do you access the property sheet to assign code to the On Click event of a button or label?
A. Open the form in Design View, select the object, then use the Events tab in the property sheet
B. Double-click the form in Datasheet View
C. Use the Relationships window
D. Right-click in the navigation pane and select 'Code View only'
Q5. Why might the event properties (like On Click) for a label not be visible or available in the property sheet?
A. The label is bound to a text box and must be unbound to assign events
B. The form is in Layout View
C. The label has no caption
D. The label is hidden behind another control
Q6. What should you do if you delete a button or label that has VBA code associated with its event?
A. Manually delete the orphaned code from the VBA editor
B. The code is deleted automatically
C. Ignore it, as it will not affect your database
D. The database will prompt you to remove the code
Q7. What is the benefit of giving meaningful names to buttons and labels in your forms?
A. It makes your code easier to read and maintain
B. It makes your database run faster
C. It enables sorting of buttons alphabetically on the form
D. It prevents users from changing the button's caption
Q8. What can you do if you do not want the built-in Access date picker to appear for a date field?
A. Change the date field's Show Date Picker property to Never
B. Make the date field read-only
C. Set the font color to white
D. Delete the date field from the form
Q9. If you want to decrease the date field by one day using the same logic, what code would you write in the event?
A. CustomerSince = CustomerSince - 1
B. CustomerSince = CustomerSince + 7
C. CustomerSince = Date()
D. CustomerSince = CustomerSince + 1
Q10. What alternative did the video suggest for users who want more intervals than just plus or minus one day?
A. Using a combo box to select various intervals like day, week, month, or year
B. Creating a separate button for every possible interval
C. Manually editing the VBA code every time an interval changes
D. Using Excel for more complex date calculations
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 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 video from Access Learning Zone covers how to use the on-click event in Microsoft Access to create a label that adjusts the value of a date field, such as moving an appointment forward or backward by one day.
The idea came from a question about making it easier to change dates, particularly for situations where you need to quickly bump an appointment date by a day without manually entering the new value or using the pop-up calendar, which can be tedious. Instead, we can streamline the process to a single click.
As a starting point, I use my free Access TechHelp Template, which you can download from my website. Imagine you have a form with a customer date field. In my example, it's called Customer Since, but you can substitute it for any appointment or event date field. The goal is to provide an easy way to increment this date.
Accomplishing this requires a small amount of VBA. If you're new to VBA, I recommend watching my free Intro to VBA video before proceeding, as it walks through the basics. Once you're comfortable with that, you'll be ready.
Access forms allow us to run code whenever certain actions take place, like clicking a button. You'll see various event options in the properties for any object on your form. The On Click event is what we want to focus on; it tells Access to run our code every time someone clicks the object.
To begin, add a command button next to your date field in design view. Set its caption to something like "+1" to indicate it will increase the date by one day. Rename the button to something meaningful, such as PlusOneDayBTN. Then, in its On Click event, write a single line of code that increases the date field's value by one. In Access, date fields are numeric under the hood; adding 1 increases the date by one day, adding 7 is a week, and so on. The code needed really is just one line. Save, close, and test the form. Clicking the button now quickly updates the date as desired.
If you prefer a cleaner interface or don't want to clutter your form with buttons, you can use a label instead. Add a label (for example, "+1"), make it visually distinctive, and position it near your date field. Give it a name like PlusOneDayLabel, then use its On Click event for the same code to adjust the date. Removing the button and switching to a label will not remove any code from the form automatically; you must clean up code for deleted controls manually.
This trick allows you to quickly update the date with minimal clutter. You can even create additional labels for different increments, like "-1" for moving back a day. If you'd rather not use labels either, consider using double-click events on the text boxes themselves, a method I'll discuss in another video.
One important note: if a label is still bound to a text box (that is, it moves together when you drag them), Access will not allow you to assign events to that label. To make the label independent—and able to have event code assigned—cut and paste it so it is no longer associated with the text box.
Copy and paste labels as needed to add more increment or decrement options, giving users convenient, one-click control over date adjustments.
For members, in the Extended Cut, I demonstrate how to create a combo box for selecting different increment values, like days, weeks, months, quarters, or years. You'll learn to design a flexible function where you can select your interval—so adding six months, for example, is just a couple of clicks. This allows for fast, customized date changes.
If you want to learn more VBA and take advantage of these more advanced techniques, consider becoming a member. Memberships come in different levels, each offering access to things like my extended cut videos, live sessions, sample databases, and my Code Vault. Platinum Members also receive my complete beginner and some expert courses covering not just Access but other products like Word, Excel, and Visual Basic.
Rest assured, I will continue producing free TechHelp videos like this one for everyone. If you enjoyed the lesson, please like and comment—I do read all comments. Subscribe to my channel for free to ensure you get notified about new lessons. If you want email alerts when new videos go live, you can find a link to my mailing list under the video.
If you're brand new to Access, try my Access Level 1 course. It's over three hours long and provides a solid foundation. Level 2 is available for just one dollar or free for any channel member.
If you want your questions answered in a video, visit my TechHelp page and submit your query.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.
Topic List
Using the on-click event to update a date field
Adding a command button to increment a date by one day
Naming and organizing form controls for clarity
Assigning VBA code to a button's on-click event
Explaining how Access date arithmetic works
Saving and testing button functionality to change dates
Creating and configuring a label as a clickable incrementer
Assigning VBA code to a label's on-click event
Unbinding labels from text boxes to allow event handling
Copying and modifying labels for increment and decrement actions
Setting up the minus one label to decrement the date field
Testing label click events to increase and decrease dates
|
