Microsoft Access Tips for Serious Users

Provided by allenbrowne.com, October 2004, updated August 2008.

Popup Calendar

There are plenty of ActiveX control calendars, but they have issues with versioning, broken references, and handling Nulls. This is an ordinary Access form you can import into any database.

Download the zip file (30 KB) for Access 2000 and later or Access 97.

Access 2007 has a popup calendar built in, so this form is not needed (though it does work.) Just set the Show Date Picker property of the text box to "For dates."

Adding the calendar to your database

To use the calendar:

1. Import form frmCalendar and module ajbCalendar into your database.
2. Copy the calendar button from the sample form onto your form. (There are two styles to choose from.)
3. Set the On Click property of the button to something like this:
      =CalendarFor([SaleDate], "Select the sale date")
4. Change SaleDate to the name of your date text box. The quoted text is optional, for the title bar of the calendar.

When you click the button, it pops up the calendar, reading the date from your text box. Select a date and click Ok, or double-click a date to write it back to your text box and close the calendar. Click Cancel to close the calendar without changing the date.

The command button that opens the calendar is tied to a particular text box, so there is no problem with adding multiple command buttons and reusing the popup calendar if your form needs several dates.

Tips for using the calendar

There are no restrictions on how you use this calendar. You may freely use it in any database you develop, for personal or commercial purposes.

Pause the mouse over the question mark to see the list of keyboard shortcuts. Alt+T is the hotkey for today's date.

A "Type Mismatch" message (Error 13) will occur if your text box contains a non-date value. To avoid this:

1. If your text box is unbound, set its Format property to a date format (e.g. Short Date), so Access knows it is a date.
2. Do not assign a non-date value (such as a zero-length-string) to your text box. Use Null instead, e.g.: Me.SaleDate = Null
3. do not use a non-date value in the Default Value of your text box.

To show Monday as the first column in the calendar, change the line:
   Private Const lngcFirstDayOfWeek = vbSunday
to:
   Private Const lngcFirstDayOfWeek = vbMonday

To use a different color for Saturdays and Sundays, change the line:
   Private Const lngcWeekendForeColor = 192&
to a different RGB value or color name, such as:
   Private Const lngcWeekendForeColor = vbBlack

As supplied, the calendar works only with text boxes, not combos.

For other alternatives, Stephen Lebans has a wrapper class for the Microsoft Month Calendar Common Control. Tony Toews and Jeff Conrad list other options.