A Pop-up Calendar for your Access Forms
Why Use a Calendar?
Getting dates entered accurately always manages to be a problem.
Everyone seems to have their own way of writing a date. In the USA
the standard format is month/day/year whereas most of Europe uses
day/month/year. What about date separators... should you use a
slash, a dash or a dot, or is it OK to use nothing at all? Or can
you type something like 27-Sep-02? In fact, most of these options
are acceptable date formats and can be catered for with Windows
settings, and even field property settings in Access tables and
Difficulties arise when you create an unbound text box to accept
a date on a form that is designed to, for example, pass criteria to
a query. Because there is no underlying form field to reject
incorrect dates, you need some other way to make sure that the user
has entered good data. Sometimes you just don't know what date to
enter... "the 3rd Thursday in April next year".
What you really need is a calendar, and thankfully Microsoft has
provided us with one that can be placed on a form. This tutorial
shows you how to find the calendar tool and use it to include pop-up
calendars on your Access forms, and explains how to add the simple
VBA programming instructions necessary to make the calendar work.
How to Add a Calendar to an Access Form
There are three steps to creating a pop-up calendar.
First you must prepare the form, then draw and customize the
calendar to your requirements, and finally add the VBA code which
will drive the process.
The calendar will be hidden
until the user clicks the form's date field, when the calendar will
appear. When a date is chosen the calendar will pass the it to the
date field before being hidden again. If the user uses their [TAB]
key to enter the date field, the calendar will not appear, and they
will be able to type a date directly into the field if they wish.
Step 1: Prepare the Form
Date fields on forms are usually represented by
simple Text Box controls. The first step is to convert your date
field text box into a Combo Box. The reason for this is to prompt
the user to click it! When a user sees the arrow button on a combo
box they know that clicking it will reveal a list. In this case,
instead of a list, they will see a calendar.
the date field text box on your form and go to Format > Change To
> Combo Box...
This will convert your existing text box to a combo box.
Alternatively you could draw a new combo box on your form with the
Combo Box tool in the form design toolbox.
Step 2: Draw the Calendar
You won't find the a calendar tool in the form
design toolbox but one is provided as an "optional extra". Click the
More Controls button on the toolbox, and wait a moment while
Access builds its list of available controls. Then look for
Calendar Control 10.0 (for Access 2002, or 9.0 in Access
2000, or 8.0 in Access 97) and select it...
The mouse pointer will turn into the Calendar
Control tool and you can click anywhere on your form to insert a
You will probably want to resize and position the calendar to suit
the design of your form. Double-click the calendar to open its own
properties window (alternatively, right-click the calendar and
choose Properties in the usual way, then go to the Other
tab and find Custom. Click the build button [...] to
open the custom properties window). These properties are additional to the
regular set of properties that you normally see for form controls.
You can change font, colour scheme, and layout to suit your own
In this example, I want to make the
pop-up calendar quite small, so I have opted not to see the
Month/Year Title and also set the font size to 8. You can
resize the calendar in the normal way by dragging one of the handles
around its edge. I have also given the calendar a size 1
border using the Line/Border Width tool on the form design
toolbar. Here's how my calendar will look in Form View...
Remember that the calendar is going to be hidden until the user asks
for it by clicking on the date field combo box (you don't have to do
it this way but I prefer to!). Make sure that your form is big
enough to display the calendar when it is un-hidden. If you wish,
you can place the calendar over existing controls. This does
make building the form a bit difficult but it can help to economise
NOTE: The Calendar Control is an
ActiveX control (actually a file called mscal.ocx) supplied
with Microsoft Office. It is normally installed with a standard
installation of Microsoft Office, Excel or Access, but if you can't
find it on the list you will need to get hold of a copy. If you are
distributing your file, or planning to use it on more than one
computer, you will also need to make sure that the host computer has
the mscal.ocx file installed. You will find it on the CD that your
copy of Excel came on, or you can download a copy from my web site
Finally, right-click on the calendar control and
choose Properties to open its properties sheet and on the
Format tab set the Visible property to No.
Step 3: Write the Code to Power the Calendar
Code to Display the Calendar
Two code procedures are required: one to un-hide the
calendar when the user clicks the date field combo box; and another
to re-hide the calendar when the user chooses a date. The first
procedure will be refined to match the calendar's date with any date
that is already shown in the date field (otherwise to display the
current date). The second procedure must include an instruction to
transfer the chosen date to the date field combo box.
Open the properties sheet for the date field combo box and on the
Event tab click in the space next to On Mouse Down. Then
click the build button [...] to open the Choose Builder
dialog, choose Code Builder and click OK...
The code editor window opens with the first and last
lines of the date field combo's mouse down event procedure already
written, and your cursor is in the space between. Enter the
following two lines, the first to unhide the calendar and the second
to transfer the focus to it:
ocxCalendar.Visible = True
NOTE: I have named the date field combo box on my
form cboStartDate and the calendar ocxCalendar.
Substitute the names of your combo and calendar for these when you
write the code.
Now enter the following lines, in
which an If Statement checks the date field combo box to see
if it already contains a date. If it does, then the calendar is
instructed to display this date. If not, the procedure uses the VBA
Date function to instruct the calendar to display the current
If Not IsNull(cboStartDate) Then
ocxCalendar.Value = cboStartDate.Value
ocxCalendar.Value = Date
The finished procedure should look like this:
Return to your form and test the code (now is a good time to save
the form in case anything goes wrong - just click the save button on
the toolbar). Switch the form to Form View and click the form
field combo box. The calendar should appear displaying either
today's date (if the combo box was empty) or the same date as shown
in the combo box.
Code to Hide the Calendar and
Transfer the Date
Return to the code editor window and open the
left-side combo box at the top of the code window. This displays a
list of objects on the form. Choose the name of your calendar (in
this example ocxCalendar), then open the right-side combo box
to display a list of procedures and choose Click. This
prompts the editor to create the first and last lines of the
calendar's click event procedure...
(NOTE: if the lines of any other procedure are
created when you make the first choice you can just delete them.)
Enter the following lines of code:
cboStartDate.Value = ocxCalendar.Value
ocxCalendar.Visible = False
The first line transfers the date chosen in the
calendar to the date field combo box. The next line sets the focus
back to the date field combo box. The last line hides the calendar
again. The finished code should look like this:
Return to the form design window and save the form. Switch to
Form View and test your calendar. If anything fails to work
properly, go back to the code window and check your typing!
Your pop-up calendar is finished! Unless of course...
What About Multiple Calendars?
It's quite likely that you will have more than one date field on a
form. In which case, do you need a separate calendar for each?...
Each date field combo box can call the same calendar control, but
the calendar needs to know which combo called it so that it can
return a date to the correct one. This needs only a minor change to
the code we have already used.
Declare the Variable
First of all, declare a variable to hold the name of the combo box
that called the calendar. I'll name my variable cboOriginator
and declare it with the line...
Dim cboOriginator As ComboBox
...at the very top of the form's code window after the
Option Compare Database and Option Explicit statements
but before the first procedure, like this:
Declaring the variable like this makes it global so that it will
hold a value after the procedure that gave it the value has
finished, and can pass a value from one procedure to another.
Pass the Originator's Name to the Variable
There will be a series of almost identical MouseDown
procedures, one for each date field combo box. Add a line to each
one to pass the name of the originating combo box to the variable:
Set cboOriginator = cboStartDate
NOTE: the use of the "Set" keyword when assigning a value to the
variable. The set keyword is required when assigning a value to an
Other references to the combo box in the procedure can now be
written as cboOriginator:
Pass the Date Back to the Originating Combo Box
The calendar's Click procedure is similarly modified to pass
the date back to the combo box named in the cboOriginator
A final line:
Set cboOriginator = Nothing
...empties the variable, ready for the next call.
You can download a fully working demo database
illustrating the techniques covered in this tutorial. The files are
provided in Access 97 and Access 2000 format, and also as Zip files
for faster download (you will need a copy of WinZip or a similar
program to extract the zipped files).