Replacing the Calendar Control in Access 2010 Applications

Summary:  Learn how to replace the deprecated Calendar control in Microsoft Access 2010 applications.

Applies to: Access 2007 | Access 2010 | Access Services | Office 2007 | Office 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: Use the built-in DatePicker feature for any TextBox control that is bound to a Date field. Use the Windows DatePicker control, part of the Windows Common Controls. Use a subform that looks and functions like a calendar. Use a third-party date/calendar control. 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: Birthdates, bound to the sample table named Birthdates. frmCalendar, a sample replacement for the Calendar control. 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: The ControlSource property for the DatePicker control was set to Birthdate, so it looks and functions like any other Access control─when you change the date in the DatePicker, Access automatically stores the value in the Birthdate field for the current row of the form. The CheckBox property of the control was set to Yes. This property determines whether the control should display a check box indicating that it contains a non-null value. To enable users to add a new row to the underlying table of the form, set this property to Yes, or else the control cannot indicate the null value of the new row for the Birthdate field. You have a great control over the user interface of the control. You can show or hide up arrows and down arrows; you can change the font (set to Calibri in the sample, to match the other controls) and other visual characteristics. 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. Private WithEvents calendarForm as Form_frmCalendar 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. Private Sub Form_Load() Set calendarForm = Calendar.Form End Sub 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. Private Sub calendarForm_DateChanged(newDate As Date) selectedDate = newDate End Sub 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. Private Sub Form_Current() On Error Resume Next calendarForm.Value = Me.Birthdate End Sub 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. Private Sub calendarForm_DateChanged(newDate As Date) Me.Birthdate = newDate End Sub 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: Built-in date picker Simple to use (requires only setting the ShowDatePicker property). No customization available. Works only with TextBox controls bound to a Date field in a table. DatePicker Control Reasonably easy to use. Allows more customization than the built-in date picker. Can also be used to select times. Can be bound to a data source. Depends on the Microsoft Custom Controls DLL, which introduces serious versioning issues. Your end-users may not have the same version of the DLL installed as you do, and this requires updating their DLL to the latest version. Only appears as a drop-down, and cannot appear as an open calendar. Calendar Form Appears only as an open calendar (both a pro and a con). Completely customizable, but requires some work in the designer and in code. Easy to use─merely drag onto a form and write the code. Hooking up events requires additional code. Binding is not available without writing code.

Watch the video > [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/e5a185b4-443e-4032-94c2-39c74308ccba] Length: 12:24 Grab the Code Blog: Access 2010 Deprecated Features and Components Watch this: Make It Easier to Add a Date Calendars and Microsoft Access Access Developer Center Office Developer Center Microsoft Access Team Blog Access 2010─The 10,000 ft. View About the Author Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).