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".
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)
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: