Home Page
Tips And Tricks
Helpful Links
e-mail me

Picture of building from outside Tips And Tricks

I seem to run into things from time to time that I want to share with the world, usually things that I had to find out the hard way. Sometimes it will just be something that I had to dig for, and want to make more readily accessible. This page is intended to make life a little easier for other programmers. If it meets your need, then that's what it's here for.

MS Access Calendar control "Updated" event won't fire, and only way to track changes is "Lost Focus" event

Ok, I didn't figure this one out on my own, but I definitely did have to look it up. How would anyone ever know that more events exist for the standard calendar control than are shown in the MS Access properties window? Well, there is a way to find out, but you'd have to be extremely observant to see it. If some other helpful person on the internet hadn't pointed it out, I don't think I would have found it.

If you have a form with a date field on it, and you think to yourself, "Hey, I oughta put something on here to make it easier to point and click dates into that date field.", then what is the average person likely to do? Well, same thing that I would. Pull up the MS Access toolbox, and look for a calendar control. It's not listed on the main page of the toolbox, so you end up having to hit the "More Controls" icon. When you do that, you see a list of ActiveX object controls. You get down to the "C"s, and you find a control that says, "Calendar Control 9.0", or something along those lines. (Perhaps you have a slightly different version of MS Access) I work on a regular basis in both Access 2000 and Access 2003, and the same problem exists in both.

Anyway, you see that "Calendar Control 9.0", and you put it on your form. It's nice and pretty, and allows the user to pick and choose dates with a mouse, should they so desire, and also provides a visual aid to help insure that user enters the date that they intended to. So now, you want the date picked on the calendar to show up in the date field that was already on your form. You look through the events, and only see "On Updated", "On Enter", "On Exit", "On Got Focus", and "On Lost Focus".

Image of MS Access Calendar Control

Since the "On Updated" event seems to be the only event likely to do what you want, you write code to fire when "On Updated". But the code never runs. The event doesn't fire! Drat, now what can be done? Well, again, the average, typical person puts in message boxes to see what events DO fire, and then determines that "On Lost Focus" is one of the few ways to get the date from the calendar control into the date field. The side effect of using "On Lost Focus", however, is that the user can click on the calendar control all day long and not see the date appear in the date field until clicking some other control.

As a person who usually ends up making the computer do exactly what I want, this situation is frustrating. Not only is my user confused by the delay in updating the date field, but it seems to the user like the calendar control doesn't work right. There's GOT to be a better way!

Well, there is. Turns out that when you pull up module code behind a form which has a calendar control on it (somewhere near that code for the "On Updated" event that never gets run), there are more options in the pull-downs at the top of the Visual Basic module window. On the left side in the pull-down at the top, you have to find your calendar control. (The default name is something along the lines of "ActiveXCtl"). Once you choose your calendar control on the left, then you can use the pull-down on the right-hand side to see more events than are listed in the properties window for the calendar control. This includes events like "BeforeUpdate", "AfterUpdate", "Click", "DoubleClick", "NewMonth", "NewYear", etc. (It even shows that contrary "Updated" event that doesn't seem to ever fire)

Image of MS Access Hidden Events For Calendar Control

If you choose one of these "unlisted" events from the pull-down, the Visual Basic editor obligingly creates a subroutine for that event. You don't even have to copy it from another control. The problem is that those events are "hidden" from the average user. One would think that choosing the "All" tab in the properties window would show the hidden events, but it doesn't.

So now, all you have to do is put code in the "AfterUpdate" event of the calendar control such as "txtBegDate = selDate.value" (or "txtBegDate = ActiveXCtl.value"), and when the user clicks on the calendar control, the text box for the date gets updated immediately.

If you found this information to be useful, and would like to donate via PayPal to help offset the cost of maintaining this web site, use the following button:

|Home Page| |Custom Software| |Policies| |About| |Tips And Tricks| |Helpful Links|