Databases - MS Access - Calendar Example

MS Access - Calendar Example

This page was originally written for MS Access 97. Starting with MS Access 2007, a new method is used to accomplish this task and the ActiveX controls the rest of this page talks about are no longer available to use.

With MS Access 2010, the date method no longer returns the current date - instead it produces a compile error!

This has turned out to be much more involved than I thought. There are 2 Calendar Control examples

One that acts like a combo box
One that displays as a Dialog Box

Overview

There are 3 calendar controls. They can be selected via either of 2 methods

In the Toolbox, click the More Controls icon and select the control from the list. The associated filenames are not listed.
From the menu, select
- Insert / ActiveX Control...
These lists are easier to use and the associated filenames are listed ... except for controls implemented via COMCTL32.DLL (and perhaps a few others). (MS Access 97 does not show the filenames.)

The following lists the controls (and their libraries) to select from the list.

MonthView - places a single month on the form: Select - Microsoft MonthView Control
c:\win98\system\MSComCt2.OCX
DTPicker - a ComboBox Calendar control: Select - Microsoft Date and Time Picker Control, Version x.x
c:\win98\system\MSComCt2.OCX
Calendar - another single month calendar: Select - Calendar Control x.x
c:\win98\system\MSCAL.OCX (MS Access 97 / Office 97)
C:\Program Files\Microsoft Office\Office10\MSCAL.OCX (MS Access 2002 / Office XP)

Warning:

I sometimes work with the Toolbox docked at the bottom of the screen. In this configuration, the More Controls icon won't work - sometimes nothing happens, but more frequently the kernel produces a general protection fault and MS Access crashes. (Of course, you loose all un-saved changes.) The solution is to un-dock the Toolbox before selecting More Controls. Of course, you could simply use the menu instead.
(Unbelievable. Don't they test anything? Verified with Microsoft Access 97 SR-2.)

Note:

Tools / ActiveX Controls...

Insert / ActiveX Control...

DTPicker

This control should be available on machines that have one of the Microsoft compilers installed - VisualBasic, VisualC++ and the like. Be careful, my previous experience with another ActiveX control indicates that it is fairly easy to get these to work on machines where Visual Studio is installed, but almost impossible to use them on other machines.

This control is easy to use - just place it on your form. It does not require any special code. Click the down arrow to display the calendar. As soon as you click on a date, the calendar closes and the date is copied to the edit field.

Warning: This control does not like null values. In its default configuration, if you create a record and don't automatically assign default values to all the date fields, you will get an error.

In order to allow null values, you must enable the integrated check box. When the box is not checked, the value is Null, and the field shows today's date as grayed out; when checked, you can set the date. (It looks pretty dorkey.)

I prefer the Calendar Control discussed next because it shows a null value as just a blank field.

Calendar Control

There is a significant issue with the Calendar Control

On Dbl Click

I found a solution to this 5-04-05.

Therefore, there is no way to indicate that you have made a selection and that the Calendar Control should close. The code provided in the next section provides one solution.

First, on a form, place 3 components

A Text Box
- Name = TheTextBoxName
A Toggle Button - "6" in Marlett is a down arrow
- Name = TheToggleButtonName
- Font = Marlett
- Caption = 6
- Heigth = 0.2083"
- Width = 0.2083"
A Calendar Control
- Name = Calendar_1
- Visible = false

Make the following modifications to the form's code page. Refer to the code in the next section .

Define the 2 global variables
Be sure NOT to name any components with these names.
Copy the ShowCalendar subroutine

In the Toggle Button's On Click event place

ShowCalendar TheTextBoxName, TheToggleButtonName

Copy the code in Calendar_1_LostFocus to the Calendar Control's Lost Focus event.

To use the calendar, type a date in the text box and click the toggle button. The calendar will be displayed showing the date from the text box. Click in the calendar control to select another date. If you then click any other control on the form, the calendar will loose focus, the currently selected date is copied to the associated text box, the focus is moved to the associated text box, and the Calendar Control is hidden.

This code is designed so that, if there are several text controls associated with dates, you only need one Calendar Control and you don't need to manage several copies of almost identical code, one set per control. Instead, each toggle button calls ShowCalendar and passes the names of the appropriate Text Box / Toggle Button pair. The code even positions the calendar under the associated text box.

There are a number of instances where the autopositioning algorithm fails (when your text box is too close to the edge of the form, the algorithm places the calendar so that it overlaps the edge of the form) and you will get an error. To solve this, I have provided ShowCalendar_xy which requires a Text Box / Toggle Button pair and the x/y position where you want the calendar displayed. (The x/y values must be in inches.)

In order to get the correct x/y position values, I suggest manually placing the calendar control where you want it displayed and using its Left and Top properties as your x/y values, respectively.

More Events

Unless you know 2 tricks, documentation for MSCAL.Calendar.7 is poor at best.

There are 2 combo boxes at the top of the window used to enter code. Use these to see all 14 available events - only 5 are listed in the Properties viewer. (The help warns not to use Updated.)
Both Click and DblClick are available this way and missing in the Properties viewer.
To see the help pages (and these are pretty good), in design mode, double click the Calendar control to display the properties dialog box. Then press the Help button.

Use this to update a text control when the calendar is clicked

Private Sub Calendar_1_Click()
  Calendar_UIEdit.Value = Calendar_1.Value
End Sub

and this to close the Calendar when it is double clicked

Private Sub Calendar_2_DblClick()
  Text14.SetFocus
  Calendar_2.Visible = False
End Sub

I spent 5 hours getting the double click to work. Every time Text14.SetFocus was executed, Access showed
Run-time error '2110':
Microsoft Access can't move the focus to the control Text14.
Eventually, I added another Calendar control and the code worked. Apparently, Access has some sort of design problem.

References

The references strongly suggest NOT using the Calendar control (MSCal.OCX) because

It is not installed on all machines. However, because it IS installed with MS Office, and because MS Access (a part of MS Office) must be installed on the machine this is a weak argument.
The installed version of the Calendar component depends on the version of MS Office and various updates that may be installed on your machine.
As a result, when you *distribute* an application, there may be issues. (But that is always true with MS Access ... it is one of its major problems.)

I agree with all of these (but I also use the control ... sometimes).

A Pop-up Calendar for your Access Forms is well written and provides detailed instructions on how to use this Calendar control. Several downloads are available - zip'ed, un-zip'ed, 97, 2000.
These 2 *.mdb files demonstrate how to use calendars. One uses MSCal.OCX and the other does not :) The main page contains additional downloads and other information.
Calendars and Microsoft Access provides links to additional sources.

Use with Multiple Detail Records

Some controls don't work well when several records are shown in the same form - the Toggle Button is one of them. When used in repeating detail sections - when one Toggle Button is depressed, they are all depressed. (Yuk!)

The "solution" is to use a Push Button (Command Button) control. This does not render the button as depressed while the Calendar is displayed (the Windows standard), but it is better than having them all depressed. Set the properties the same as for the Toggle Button above.

If you use the routines below, notice that they require you to pass a Toggle Button. I just place a hidden button (Visible = No) somewhere on the form and pass that.

For some forms (particularly those with multiple detail records and/or a subform), no section is large enough to display a Calendar. In those cases, I use a Calendar dialog box to provide this functionality.

Code that works with the "Calendar Control"

Option Compare Database
Option Explicit
Dim Calendar_UIEdit As TextBox, Calendar_UIToggleButton As ToggleButton

Private Sub Calendar_1_Click()
  Calendar_UIEdit.Value = Calendar_1.Value
End Sub

Private Sub Calendar_1_DblClick()
  Calendar_UIEdit.SetFocus        ' This line fails in some cases - probable MS Access design problem
  Calendar_1.Visible = False
End Sub

Private Sub Calendar_1_LostFocus()
  If Not IsEmpty(Calendar_1.Value) Then
    Calendar_UIEdit.Value = Calendar_1.Value
  End If
  Calendar_UIToggleButton.Value = False
  Calendar_UIEdit.SetFocus
  Calendar_1.Visible = False
End Sub

Private Sub OtherDate_UIToggle_Click()
  ShowCalendar OtherDate_UIEdit, OtherDate_UIToggle
End Sub

Private Sub Toggle12_Click()
  ShowCalendar_xy Text10, Toggle12, 1.5417, 0
End Sub

Private Sub Toggle3_Click()
  ShowCalendar TestDateUI_Edit, Toggle3
End Sub

Private Sub ShowCalendar_xy_Inches(EditField As TextBox, ToggleButton As ToggleButton, _
      x As Single, y As Single)     
  Set Calendar_UIEdit = EditField
  Set Calendar_UIToggleButton = ToggleButton
  Calendar_1.Value = EditField.Value
  Calendar_1.Top = y * 1440   ' 1440 converts inches to twips
  Calendar_1.Left = x * 1440
  
  Calendar_1.Visible = True
  Calendar_1.SetFocus
End Sub

Private Sub ShowCalendar_xy_Pixels(EditField As TextBox, ToggleButton As ToggleButton, _
      x As Integer, y As Integer)     
  Set Calendar_UIEdit = EditField
  Set Calendar_UIToggleButton = ToggleButton
  Calendar_1.Value = EditField.Value
  Calendar_1.Top = y    ' no conversion
  Calendar_1.Left = x
  
  Calendar_1.Visible = True
  Calendar_1.SetFocus
End Sub


Private Sub ShowCalendar(EditField As TextBox, ToggleButton As ToggleButton)
Dim i
  Set Calendar_UIEdit = EditField
  Set Calendar_UIToggleButton = ToggleButton
  Calendar_1.Value = EditField.Value
  i = EditField.Top + EditField.Height + 100
  If (i + Calendar_1.Height) < Detail.Height Then
    Calendar_1.Top = i
  Else
    Calendar_1.Top = Detail.Height - Calendar_1.Height - 8 ' - 300 
  End If
  
  i = EditField.Left - Calendar_1.Width / 3
  If i < 300 Then
    Calendar_1.Left = 300
  Else
    Calendar_1.Left = i
  End If
  
  If (Calendar_1.Left + Calendar_1.Width) > Me.Width Then
    Calendar_1.Left = Me.Width - Calendar_1.Width
  End If
  
  
  Calendar_1.Visible = True
  Calendar_1.SetFocus
End Sub

A Calendar Dialog Box

The Calendar Control needs to be 2 inches high. However, many forms have space limitations and no individual section is that large. For instance, when many records are shown at one time, they should be as narrow as possible (to show as many records as possible). In these case, it is better to place the Calendar in a dialog box. Creating it is simple

Create a dialog box
Set the calendar's date to the passed parameter when the dialog box opens
Get the date when it closes

The trick is to set the Calendar's date in Form_Load ...

Calendar_1.Value = #3/4/2004#

fails in Form_Open.

For a basic Calendar dialog box

Place 3 components - Calendar, OK Button, Cancel Button
On the form, set the following to Yes
- Pop Up
- Modal
- Auto Center
On the form, turn off
- Scroll bars
- Record Selectors
- Navigation Buttons
- Dividing Lines
- ControlBox
On the form, set
- Border Style to Dialog
- Caption to Select a date
Use the code in the next section
Set the form size as appropriate

Code for a Calendar Dialog Box

Code in the calling form - attached to a push button (command button)

Private Sub ShowCalendar_UICommand_Click()
  Dim DialogName As String
  Dim ResultFlag As Integer
  
  DialogName = "Calendar_frm"  ' The name of the dialog box
 
 'DoCmd.OpenForm "FormName", , , , ,         , "Passed Parameter"
  DoCmd.OpenForm DialogName, , , , , acDialog, Date_

  ResultFlag = Forms(DialogName).Form.Tag ' Uses the standard MsgBox constants 
 
  If ResultFlag = vbOK Then      ' Test that OK was pressed
    Date_ = Forms(DialogName)!Calendar_1.Value
  End If
 
    ' Without an explicit "DoCmd.Close",
    '   * The dialog box's Form_Open is run only
    '     the first time "DoCmd.OpenForm" is called
    '   * When the form is merely hidden,
    '     "DoCmd.OpenForm" does not always wait for the
    '     user to hide the dialog box - very unpredictable
  DoCmd.Close acForm, DialogName

End Sub

The following code is placed in the Calendar dialog box

Private Sub Form_Load()
  If Not IsNull(Me.OpenArgs) Then
    Calendar_1.Value = Me.OpenArgs
  End If
End Sub

Private Sub Cancel_UICommand_Click()
 ' Hide dialog box so the parameters are still available
    Me.Visible = False
    Tag = vbCancel
End Sub

Private Sub OK_UICommand_Click()
 ' Hide dialog box so the parameters are still available
    Me.Visible = False
    Tag = vbOK
End Sub

In Form_Load, either of these will work

    Calendar_1 = Me.OpenArgs
    Calendar_1.Value = Me.OpenArgs

MS Access 2007

Starting with MS Access 2007, the date/calendar functionality is built in and there is no longer a need for a separate ActiveX component.

Place a text box on the form
Format it to display dates
Enable the display of the Date picker

When the user clicks inside the text box, a calendar icon will be displayed to the right of the text field. When the icon is clicked, the calendar will be displayed.

MS Access 2010

Well, with the 2010 MS Office update, Microsoft has managed to screw customers yet again. I tried to use an application that has worked for several years and got


		Compile Error: Can't find project or library

with the word date highlighted in the following code.

Dim intDate, intYear, intMonth

    intDate  = Date
    intYear  = Year (intDate)
    intMonth = Month(intDate)

This is what it had to be changed to to work with the 2010 version.

   'intDate  = Date               ' removed no longer works
    intYear  = Year (CDate(Now))  ' changed to a function call
    intMonth = Month(CDate(Now))  ' changed to a function call

Though these are simple changes, they are not documented on any Microsoft page. In fact, as of July 2014, Microsoft still claims that the old (no longer working) date function still works. (This failure has been verified on 3 separate systems.)

(By the way, the spacing shown above is not allowed in MS Access - it simply removes the extra spaces to make the code less readable!)

I am going out on a limb here and making a guess. It appears that date is now a variable type and, therefore, no longer gets the current date. As a result, MS Access no longer knows what to do with it. (Unfortunately, I no longer have access to an older system to check any of this.)

At any rate, this problem happened because the corporate bosses pushed the MS Office "update" to every machine .. without warning. One day, everything was simply broken. Of course, these clueless morons were not able to fix (or even admit to) the disaster they caused.

My rule still stands - never update working software. EVER!!

Author: Robert Clemenzi