Introducing ActiveX Controls-The Calendar Control

Although Access 2007 certainly provides a useful collection of controls to help you design your forms, for some tasks you might need something more complex. Access supports many ActiveX controls that provide functionality beyond the basic set you can find in the Controls group. An ActiveX control is a small program that supports the ActiveX interface to allow Access to see the control’s properties and build a window to display the control’s user interface.

The 2007 Microsoft Office system installs dozens of ActiveX controls on your computer. It uses many of these in other applications such as Microsoft Office Outlook 2007 or Microsoft Office Excel 2007. Access 2007 makes some of these controls available directly in the Controls group through the Insert ActiveX Control button, such as Office PivotTable, Office Chart, and Office Spreadsheet. These controls are available for forms you design, but they’re not intended for that purpose. Controls that you can effectively use in your Access forms include the Calendar control (which presents a calendar to make it easy to select a date value), the ListView control (which allows you to navigate data in a tree structure), the ProgressBar control (which allows you to graphically display progress of a complex task, but you must write code to update the bar), and the Slider control (with which a user can set a value by moving a slider).

The Conrad Systems Contacts application contains many date/time fields, so the Calendar control might be ideal to provide a graphical way to set a date value. In the ContactsDataCopy.accdb sample database, open frmXmplContactEvents as shown in Figure 13–37. This is a simple form to directly edit records from the tblContactEvents table. In this table, the ContactDateTime field includes both a date and a time, but the ContactFollowUpDate field has a date value only. The Calendar ActiveX control, which provides a date value only, might be ideal to use to set this value.

Figure 13–37: You can use this basic form to edit contact events using standard controls.

Note 

Access 2007 provides a new feature called the Date Picker that you can activate for text box controls. If you set the Show Date Picker property of a text box control to For Dates, Access displays a small calendar icon next to the text box when it contains a date/time value and the text box has the focus. You can try it out on the frmXmplContactEvents form by clicking in the Follow-Up Date field and then clicking the icon that appears to the right of the text box.

Switch to the Design view of this form, delete the ContactFollowUpDate text box control, and expand the Detail section downward about 2, inches to give yourself some room to work. Click the ActiveX Controls button in the Controls group on the Design tab to open the Insert ActiveX Control dialog box that lists all the registered ActiveX controls on your computer as shown in Figure 13–38. You can use the scroll bar on the right side to move up or down the list.

Figure 13–38: The Insert ActiveX Control dialog box displays all ActiveX controls that are registered on your computer.

Click the Calendar Control 12.0 item in the list to select that control, and then click OK. The dialog box closes, and Access places the new control in the upper-left corner of the Detail section. Click the control and drag it just under the FollowUp check box control, and then align the Calendar control with the other text boxes. (Tip: If you select the FollowUp check box control before inserting the calendar, Access places the calendar below that control.) If you want, resize the Calendar control to match the width of the Notes text box control. Make sure the control is selected, and open the Property Sheet window. Most ActiveX controls have custom properties that Access recognizes and shows in the Property Sheet window. Most controls also display these custom properties in their own dialog boxes. Click the Other tab in the Property Sheet window to see the list of custom properties available for this control. You can click the Custom property and then click the Build button next to the property box to open the Calendar Properties dialog box, as shown in Figure 13–39. (You can’t actually type anything in the Custom property box-it’s simply a way that Access provides to allow you to easily open the control’s Custom Properties dialog box.) Another way to open the Custom Properties dialog box for the Calendar ActiveX control is to right-click the control, click Calendar Object, and click Properties on the submenu.

You’re going to set this control bound to the ContactFollowUpDate field, so you don’t need to worry about setting the Month, Day, or Year properties of the control. You might set these values if you wanted to use the control to provide a date value for some other purpose. As you can see, you also have options to include the month and year title, include selectors for the month and year, list the days of the week across the top, and show or hide horizontal and vertical grids. Click OK to close the Calendar Properties dialog box.

Figure 13–39: You can set custom properties for the Calendar ActiveX control in the Property Sheet window and in the control’s Custom Properties dialog box.

This control might look better with some additional contrast, so click the BackColor property and click the Build button to open the Color Picker to set the color. (If you know the color code you want, you can also enter it directly in the property.) Choose Light Gray 2, under Standard Colors (a value of #C0C0C0 or 12632256 decimal). Click the All tab in the Property Sheet window, and set the Control Source property near the top of the list to the ContactFollowUpDate field. Now add a label to the form grid for the calendar, set its caption to Follow-Up Date:, and then align it with the other labels and with the top of the Calendar control. Switch to Form view, and your form should now look like Figure 13–40. Notice that when you move through the records, the calendar changes to display the value stored in the record. You can change the calendar date by clicking one of the date boxes to update the field. You can find this form saved as frmXmplContactEventsCalendar in the sample database.

Figure 13–40: You can see the ActiveX Calendar control in action.

Note 

The initial release of Access 2007 has a bug that prevents the Build button from being displayed on any properties that affect color. As a result, you cannot open the Custom Properties dialog box for these properties, and you can’t pick a color from the Color Palette window. To match our example, enter the decimal value 12632256 in the BackColor property.

In Chapter 24, “The Finishing Touches,” you’ll learn how to execute the methods of the ActiveX Calendar control to command it to move to a different year, month, or day.