Working with ActiveX Controls

3 4

ActiveX controls supplement the standard controls provided with Access. The DateTimePicker control, for example, pops up a small calendar for selecting a date. Ideally, an ActiveX control should work equally well in all Microsoft Office applications, but that's not always the case; you can't assume that an ActiveX control that works in Microsoft Outlook or Excel will work in Access (or vice versa). Additionally, some ActiveX controls have properties or methods that don't work in all Office applications or that work on Visual Basic forms but not on Office or Access forms. This means that when you want to use an ActiveX control on an Access form, you might have to experiment to find out whether the control works, or which of its properties, methods, and events are functional on an Access form.

InsideOut

If you look up Help for an ActiveX control in the MSDN Library (either using the MSDN CD or online), be aware that as you click links, you might be transported into Help topics that are part of the Visual Basic or Microsoft Visual C++ documentation, leaving you wondering why the code examples look strange and the Help text refersto properties that aren't there when you look at the control's properties sheet on an Access form. Note the Help book listed in the Location column-it usually gives you a clue about the context of the topic. If you aren't in the Office or Microsoft Office Developer (MOD) documentation any more, be careful! What you read might not apply to using the ActiveX control in Access.

One ActiveX control is included with Access-the Calendar control. The Calendar control is described in the section "Using ActiveX Controls to Add Functionality to Forms."

Using Controls from MOD

The Microsoft Office XP Developer (MOD) edition of Office XP includes a set of ActiveX controls to supplement those provided with Office Premium. Some of these controls can be bound to data, although not by the usual Access method of selecting a data source from a Record Source, Row Source, or Control Source property. Instead, the MOD controls must be bound to data in VBA code. The MOD ActiveX controls provided with Office XP Developer are listed in Table 6-3, with the data-bound controls indicated by an asterisk.

Table 6-3. MOD ActiveX controls

Control	Description
*ADO Data Control	Binds to data in a table or other data source. Used to supply data to other controls.
Common Dialog	Displays one of several Microsoft Windows dialog boxes, such as the File Open and Colors dialog boxes.
Chart Control	Displays data in a chart format, similar to an Excel chart.
*Data Report	Used with the Data Report Designer to place fields of data on a report.
*DataCombo	Displays a data-bound combo box.
*DataGrid	Displays a data-bound datasheet.
*DataList	Displays a data-bound list box.
*Hierarchical Flexgrid	Displays data from linked tables, similar to the new subdatasheet feature in Access.
ImageCombo	Similar to a combo box except that you can include images with the list items.
ImageList	Stores a list of images that can be displayed in other controls, such as the TreeView control.
Internet Transfer	Lets you connect to Web sites and FTP sites.
ListView	Displays data as ListItem objects, which can have an associated icon. Can be used to display an expanded view of a TreeView control node.
ProgressBar	Graphically represents the progress of a transaction.
Rich Textbox	Displays rich text, that is, text with color, emphasis, and different fonts.
Slider	Displays a bar with a "thumb" control (similar to a Windows scroll bar). The user moves the thumb to adjust a value.
StatusBar	Displays information about the status of various application components.
SysInfo	Detects system events such as desktop resizing or resolution changes. Can also display operating system or version information.
Tabbed Dialog	Presents several pages of information with tabs for selecting a page.
TabStrip	Displays a strip of tabs used to define multiple pages for a window or dialog box.
ToolBar	Contains a collection of buttons used to create a toolbar for an application.
TreeView	Displays data in a hierarchical tree, like Windows Explorer.
UpDown	Increments or decrements a number. (This control is used in many of the built-in Windows and Office dialog boxes.)
Winsock	Lets you connect to a remote computer using either the UDP or TCP protocol.

tip - Find the controls that will work on Access forms

---

To winnow out the ActiveX controls that will actually work in Access from the hundreds of controls listed in the list opened from the More Controls tool in the Toolbox, look for controls that start with the word Microsoft and end with the identifier v. 6.0, possibly with either the (OLE DB) or (SP4) qualifier.

In addition to the Calendar control provided with Access itself and the ActiveX controls provided with MOD XP, you might be able to purchase third-party ActiveX controls from such vendors as FMS and Database Creations. In purchasing third-party controls, be sure that the vendor has tested the controls with Access; otherwise, you might be disappointed when you try to use the controls on an Access form.

Using ActiveX Controls to Add Functionality to Forms

Whatever the origin of an ActiveX control, you place it on an Access form by selecting it from the list opened from the More Controls tool in the Toolbox or from the Insert ActiveX Control dialog box opened by choosing Tools, Insert ActiveX Control. Help for ActiveX controls can be hard to find. You might be able to open a Help page for an ActiveX control by right-clicking the control and selecting ControlType Properties from the shortcut menu to open the control's special properties sheet.

Most ActiveX special properties sheets have a Help button, and clicking the Help button might open a Help topic for the control. For the Calendar control, you'll get a Help topic that lists the properties, methods, and events of this control. Whether you will be able to get Help on a particular ActiveX control depends on a number of factors, such as whether you have installed MOD, or the MSDN Library that comes with MOD, or HTML Help for another program such as Visual Basic.

An ActiveX control's events might not all appear on the Events tab of the control's properties sheet. For example, the Calendar control's properties sheet lists only four events: Updated, Enter, Exit, Got Focus, and Lost Focus. Of these events, the Updated event looks promising for working with a selection, but it doesn't work in Access-when you click a date on the calendar, the Enter and Got Focus events fire but not the Updated event.

However, the Calendar control actually has many more events than appear in its properties sheet. To view these events, open the VBA window by pressing Alt+F11, select the form on which the Calendar control is inserted in the project list, and then select the Calendar control in the object box (the drop-down list on the left side of the module pane). You'll see a long list of Calendar control events in the procedures/events box (the drop-down list on the right side), as shown in Figure 6-30.

Figure 6-30. The events/procedures list for the Calendar control shows many more events than are listed in the control's properties sheet.

The Click event fires when a date is clicked on the calendar. You can use this event to get the value of the selected date and then use the value in code or write it to a text box, as is done in the frmActiveXControls form in the sample Controls 2002 database.

The following Click event procedure writes the date selected in the Calendar control to a text box on the form:

 Private Sub ocxCalendar_Click() On Error GoTo ErrorHandler     MsgBox "In Click event"     Me![txtSelectedDate].Value = Me![ocxCalendar].Value     ErrorHandlerExit:     Exit Sub ErrorHandler:     MsgBox "Error No: " & Err.Number & "; Description: " & _         Err.Description     Resume ErrorHandlerExit End Sub 

The Calendar control (in some form) has been available for several Access versions; the newer DateTimePicker and MonthView controls (introduced in MOD 2000) provide several different alternatives for selecting dates. Even if you don't have MOD, you may be able to use these controls on Access forms, if you have installed Visual Basic 6.0; a number of ActiveX controls are provided with Visual Basic.

The DateTimePicker control (similar in appearance to the controls used to select dates in Outlook) is a compact control that opens a small calendar when its drop-down button is clicked, and collapses to a small text box-like control displaying just the selected date. Because of its small size, this control is ideal for selecting dates on a form with limited available space.

The MonthView control is a large control, similar to the Calendar control except that it lets you display multiple months at once (by setting the MonthColumns property to the number of months to display). On this control, today's date is indicated by a dramatic red circle. Like the Calendar control, most of its events don't show up in its properties sheet, and also like the Calendar control, the Updated event doesn't work, and the Click event does work, as well as two extra control-specific events, DateClick and DateDblClick.

The MonthView control takes up a lot of form real estate, but it may be useful when you want to display a multi-month calendar, and you have room for it on the form. The code listed below uses the DateClick and DateDblClick events to write the start and end dates of a date range to txtStartDate and txtEndDate controls on a form.

tip

---

Because the Click event is fired (as well as the DblClick event) when you double-click a date, select the end date first.

 Private Sub ocxMonthView_DateClick(ByVal DateClicked As Date) On Error GoTo ErrorHandler    Me![txtStartDate].Value = Me![ocxMonthView].Value     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & _         Err.Description    Resume ErrorHandlerExit     End Sub Private Sub ocxMonthView_DateDblClick(ByVal DateDblClicked As Date) On Error GoTo ErrorHandler    Me![txtEndDate].Value = Me![ocxMonthView].Value     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & _         Err.Description    Resume ErrorHandlerExit     End Sub 

Figure 6-31 shows frmActiveXControls, with the Calendar control at the top left, the MonthView control at the bottom, and the DateTimePicker control on the right side, under text boxes that display the selected date, start date, and end date selected on the Calendar and MonthView controls.

Figure 6-31. frmActiveXControls shows three ActiveX controls used for selecting dates.