Access Tips: A Pop-up Calendar for Your Access Forms

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 forms.

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.

Select 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 calendar...

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 requirements.

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 on space.

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 the Microsoft web site at: http://www.microsoft.com/officedev/freestuff/mscaldl.htm.

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
ocxCalendar.SetFocus

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 date:

If Not IsNull(cboStartDate) Then
   ocxCalendar.Value = cboStartDate.Value
Else
   ocxCalendar.Value = Date
End If

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

cboStartDate.Value = ocxCalendar.Value
cboStartDate.SetFocus
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!

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?... No!

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 object variable.

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 variable.

 Set cboOriginator = Nothing

Note: the copyright of this page does not belong to me, I took from a page that I cannot find its bookmark, if you are the owner of this text please e-mail to me so as I put your reference here thanks

A Pop-up Calendar for your Access Forms

Why Use a Calendar?