Replacing the Calendar Control in Access 2010 Applications
Summary: Learn how to replace the deprecated Calendar control in Microsoft Access 2010 applications.
Applies to: Microsoft Office 2010 | Microsoft Access 2010
Published: October 2010
Provided by: Ken Getz, MCW Technologies, LLC
Microsoft Access has included a Calendar control since it first added support for ActiveX controls, in Access 2.0. The calendar control, MSCAL.OCX, is not included with Access 2010. In addition, if you attempt to open a form that includes the control, you will receive the following error: "Your Microsoft Office Access database or project contains a missing or broken reference to the file ꞌMSCAL.OCXꞌ."
To replace the Calendar control in Access 2010 applications, you have several options. You can:
This Visual How To shows all except the final option. To get started, download the sample database, CalendarDemo.accdb (an Access 2010 database). Be aware that the database includes a table named Birthdates with three fields (ID, CustomerName, and Birthdate), and two forms:
If your current applications use the MSCAL.OCX Calendar control, you must remove each instance of the control before you can use the forms that currently contain the control. This example shows the next step─that is, using alternatives to the Access Calendar control.
Use the Built-In DatePicker
Access 2007 and Access 2010 enable you to attach a date picker to any text box control that is bound to a date field. To see the form in action, open the Birthdates form (see Figure 1). The Birthdate text box is bound to the Birthdate field in the Birthdates table, and when the text box has the focus, you can click the small calendar icon next to the field to display a calendar and select a date. The calendar icon only appears when the text box has the focus.
Figure 1. Built-in date picker
To use the built-in date picker, you must confirm that the ShowDatePicker property was set to For Dates, as shown in Figure 2. The TextBox control must be bound to a date field, and you must remove the InputMask property for the control, if you had previously created an input mask.
Figure 2. ShowDatePicker property
Use the DatePicker Control
The Windows Common Controls provides a DatePicker ActiveX control for use across all applications, and your Access applications can use this control also. Using this control requires more effort than using the built-in date picker, but provides more flexibility.
The sample application includes several instances of the Windows DatePicker control. However, you should try to add an instance yourself. To use the Windows DatePicker control, open the Birthdates form in Design view. In the ribbon, click the drop-down button in the lower-right corner of the group of controls to reveal all of the controls, and select ActiveX Controls from the list of options (see Figure 3).
Figure 3. ActiveX Controls options
In the Insert Active Control dialog box, scroll down to find Microsoft Date and Time Picker Control 6.0 (SP4) (the control on your computer might have a later SP such as SP6), and then click OK to insert the control (see Figure 4). Be aware that your list of available controls may not match the figure.
Figure 4. Microsoft Date and Time Picker control
Selecting the control inserts an instance on the current form. You can resize, move, and modify the control exactly like any other control in Access (exactly as you could with the original Calendar control). Delete the control that you previously added─there is already an instance of the DatePicker control on the form (see Figure 5).
Be aware of the following about this control:
Figure 5. Sample form that contains a DatePicker control
Try the control: Open the Birthdates form, and then click the drop-down arrow in the control (see Figure 6). Select a date, move to the next row and back, and you will see that you have successfully changed the birthdate for the current person. Move to the new row, and notice that the check box in the control is not selected (indicating that the value is null). When you are finished, close the form.
Figure 6. DatePicker control
Use a Custom Calendar Form
In addition to the previous techniques, you may want a calendar that always appears, exactly like the old Calendar control. One way to achieve this is to use a subform that includes all the behavior of a calendar. At least one of these was created for you, and the sample application includes the form that is named frmCalendar. The form is self-contained─you just import the form into your own application and then you can embed it into any other form as a subform. Open frmCalendar in Design view (see Figure 7) and you will see that it is a small form with a buttons and other controls─the code within the form does all the work of updating and managing the form. In the ribbon, in the Tools group, select View Code. Review the code. Do not change the code. Close the Visual Basic editor and then frmCalendar when you are finished.
Figure 7. Sample frmCalendar in Design view
To try the calendar form, in the ribbon, select Create, and then Form Design. From the navigation pane, drag a copy of frmCalendar onto the new form. This action creates a new subform, as shown in Figure 8. By default, the name for the subform is Calendar. You can change the name, but leave it as is for now.
Figure 8. Instance of frmCalendar on a new form
The calendar form is not useful if you can only select dates─you must also have to be able to determine when the selected date changes. The sample form raises a DateChanged event, which passes to its event handler a parameter that contains the new selected date. Reacting to this event requires additional work, because the subform is not a control. Actually, it requires only two additional lines of code: The following steps show how to hook up the event handler.
With the new form that you previously created open in Design view, add a TextBox control to the form, next to the calendar. Name the new TextBox control selectedDate. On the ribbon, select View Code. In the form module, add the following declaration.
This declaration defines a variable that can refer to the calendar form class (named Form_frmCalendar, by default─every form provides a class whose name is Form_<the form name>). Using the WithEvents keyword enables you to react to events that the object the variable refers to raises. In this case, the object raises only the DateChanged event, and you will add an event handler for that event.
From the Object drop-down list at the upper-left corner of the Visual Basic editor window, select Form. This action creates the Form_Load event handler. Modify the event handler so that it resembles the following code example.
This code sets the variable that you created previously so that it refers to the Form property of the subform you created on the form. That is, it enables you to interact programmatically with the calendar form.
Finally, in the Object drop-down list, select calendarForm (the variable that you declared by using the WithEvents keyword earlier). This action creates the calendarForm_DateChanged event handler. Modify the event handler so that it resembles the following code example.
This code takes the parameter that is passed to the event handler by the calendar form, and copies the value into the selectedDate text box on the form.
Save your changes, naming the form Form1, and switch back to Access 2010. Open the form, and select a date in the calendar. You should see the selected date appear within the TextBox control (see Figure 9). Close the form when you are finished.
Figure 9. Calendar date
Although you cannot bind the calendar form to a table field, as you could with the Calendar control, you can write code to emulate the behavior. The sample form, Birthdates, includes some simple code to hook up binding. Open the form in Design view, and select View Code. Be aware that the code in the form module resembles the code that you have already seen, except for one addition: the Current event handler of the form as shown in the following code example.
This code verifies that as you move from row to row on the form, the calendar form displays the Birthdate field in the current row. The addition of the On Error Resume Next statement allows the code to avoid special error handling that would otherwise be required for the new row, in which the Birthdate field is null.
Also, the DateChanged event handler sets the Birthdate field value of the form, rather than the value of a TextBox control on the form.
This change from the earlier code causes changes in the calendar form to propagate back to the underlying Birthdate field immediately, so any other control bound to the field updates as soon as you make the change.
Close the Visual Basic editor window and open the Birthdates form. Move from row to row and verify that the calendar form updates to match the current Birthdate field. Change the birthdate in the calendar form and verify that the other controls bound to the Birthdate field update also. Close the form when you are finished.
Given the options shown here, you should be able to replace the Calendar control with the built-in date picker, the DatePicker control, or a calendar subform. None of these work exactly like the original Calendar control. However, it should be possible to replace the control with one of these options.
Consider the following pros and cons of each option:
Watch the video
About the Author