One of the powerful aspects of Access 95 is its extensibility. In addition to the controls that are available as part of the product, you can incorporate OLE (Object Linking and Embedding) custom controls on your forms. This means that you are not limited by what Access provides, but instead you are limited only by the imaginations of third-party developers who design OLE custom controls.
OLE custom controls support the OLE 2.0 custom control architecture and provide support for 32-bit operating systems. They contain their own code, methods, events, and properties. An OLE custom control's functionality is stored in a file with an .OCX extension, which is why OLE controls are more commonly referred to as OCXs. A calendar OCX ships as part of Microsoft Access. Additional OCX controls are included in the Microsoft Access Developer's Toolkit and are available from third-party vendors. These vendors include Crescent, Sheridan, Far Point, and many others.
Two types of OLE custom controls are available. The first type is visible at both design time and runtime. After being placed on a form, it provides a front-end interface that allows the user to directly manipulate the object in some way. One example is the Calendar control that ships with Access 95. The second type of OLE control is visible at design time but not at runtime. An example of such a control is a control that gives you access to all of Windows common dialog controls, such as Open, Print, and so on. The control itself is not visible to the user, but its functionality is available to the user at runtime. Another example is a timer control. This control operates within the application, triggering event code to run, but it is not actually visible to the user.
OLE controls enable you to easily incorporate additional functionality in your applications. For example, if you need to include a calendar on your form, you do not need to worry about how to build your own. Instead, you can include a custom calendar control on the form. You can modify the calendar's behavior by changing its properties and executing its methods.
Before you can incorporate an OLE custom control in your application, you must perform three steps:
When you purchase a custom control, it generally ships with an installation program. Usually, the installation program copies the OCX file to your Windows system directory. The name of this directory can vary depending on whether you are running Windows 95 or Windows NT and what you named your Windows directory during your operating system installation.
After you have properly installed the control, you are ready to register it with Access. Often, a control is automatically registered during the installation process. This is true with the Calendar OCX that ships with Access, as well as all the OCX controls that are included as part of the Access Developer's Toolkit. OCX controls are registered within the HKEY_LOCAL_MACHINE SOFTWARE class in the Window's Registry (see Figure 26.1). In Figure 26.1, the Image List control, registered as ImagelistCtrl, is selected.
Figure 26.1. OCX Controls in the Windows Registry.
If a custom control is not registered, you can register it using the Custom Controls dialog. To open this dialog, select Tools|Custom Controls. The Custom Controls dialog is shown in Figure 26.2.
Figure 26.2. The Custom Controls dialog enables you to register custom controls.
The Custom Controls dialog contains a list of all the custom controls that are currently registered within Access. To add a custom control to the list, click Register. The Add Custom Control dialog appears (see Figure 26.3).
Figure 26.3. The Add Custom Control dialog enables you to locate the custom control that you want to register.
Make sure you are pointing to the directory containing the OCX you want to register. The control that you are registering must already be installed. If it has not been installed, it does not appear on the list. Select the OCX you want to register, and click OK. You are returned to the Custom Controls dialog, and the custom control you selected now appears on the list of registered controls. You are ready to include the control on a form.
If you no longer plan to use a custom control, you should use the Unregister function. This function removes the Registry entries for controls that you no longer plan to use.
After you have registered a custom control, you are ready to include it on your forms. Custom controls can be added to forms in one of two ways:
If you plan to use the custom control on a regular basis, you probably should add it to a toolbar or the toolbox. To add the control to a toolbar or the toolbox, click with your right mouse button anywhere over the toolbox (or over any toolbar) and select Customize from the popup menu. The Customize Toolbars dialog appears. If you scroll down through the list of categories, you find a category named Custom Controls. If you click on that category, all the registered custom controls appear. The Customize Toolbars dialog is shown in Figure 26.4.
Figure 26.4. The Customize Toolbars dialog enables you to add a custom control to the toolbar.
To add a custom control to a toolbar or toolbox, click and drag the object to place it anywhere on the toolbar or toolbox. You can add additional controls to the toolbox and then close the Customize Toolbars dialog when you are done. Any tools you add now appear on the toolbox or toolbar. Figure 26.5 shows the toolbox with the Calendar control added. When you have added the custom control to a toolbar or the toolbox, you can add it to a form just like any other control.
Figure 26.5. The Toolbox with the Calendar control added.
If you are not planning to use the custom control on a regular basis, you can select Custom Control from the Insert menu. The Insert OLE Custom Controls dialog appears (see Figure 26.6). After selecting a control from the Select a Custom Control list box, the control is placed on the form. You can move the control around the form and size it as needed.
Figure 26.6. The Insert OLE Custom Controls dialog enables you to add a custom control to a form.
After you have placed a custom control on a form, the control is ready to operate in its default format. If you insert the Calendar OCX in a form and run the form, it looks like the OCX in Figure 26.7.
Figure 26.7. A Calendar OCX with no properties explicitly set.
The Calendar control knows how to display all the months of the year, along with the corresponding days for each particular month. So far, you have not set any properties for the calendar, nor have you written code to respond to any events of the calendar. Setting custom control properties, executing custom control methods, and responding to custom control events are all covered in the sections that follow.
When you insert a custom control on a form, Access automatically creates a reference to the control's Type Library. This reference appears in the References dialog (see Figure 26.8). To invoke the References dialog, select Tools|References with the Code window active. Note that the full path to the control is stored within the References dialog. For example, Figure 26.8 shows that the Calendar OCX is stored within C:\WIN95\SYSTEM. If the OCX is moved, VBA might not be able to resolve the reference. If this occurs, you must open the References dialog and manually remove the check from the reference marked as missing and set a reference to the custom control in its new location.
Figure 26.8. The References dialog enables you to add and remove library references.
If you are distributing an application containing custom controls, the application might not work without problems. Access does its best to attempt to resolve references to custom controls. If the controls reside in the Windows\System directory or in the directory within which Access is installed, Access is able to automatically resolve the references, even if the application is installed in a different directory on the user's machine than it was on your machine.
Remember that custom controls not only need to be referenced, but they also need to be registered in the Windows Registry. If you use the Setup Wizard included with the Access Developer's Toolkit to distribute your application, the OCXs are automatically registered when the user installs your application. If you do not use the Access Developer's Toolkit setup program to distribute your application, you either need to write code to register the OCX, or the user must manually register the OCX control.
The methods, events, and properties associated with each custom control differ. They are specific to that control and are determined by the author of the control. They are used to manipulate the control's appearance and behavior. Each control's methods, events, and properties are contained in a separate OCX file.
If you do not modify a control's properties, it functions with its default appearance and behavior. Much of the richness of third-party controls comes from the ability to customize the controls by changing their properties at both design time and runtime. Some controls support data binding. Data binding enables you to store or display data in a control from an underlying field in a table. Furthermore, the ability to respond to a custom control's events allows you to respond to the user's interaction with the control. Finally, the ability to execute the control's methods enables you to manipulate the control.
Figure 26.9 shows some of the Calendar control's many properties. As with any control, most of the properties of the Calendar control can be set at design time and modified or read at runtime.
Figure 26.9. The Calendar control Property sheet.
Another way to set properties for a control is to set them graphically. This can be accomplished by selecting Custom from the object's Property sheet. For example, if you select Custom from the Calendar control's Property sheet, the Calendar Control Properties dialog appears (see Figure 26.10). The Calendar Control Properties dialog allows you to modify many important attributes of the calendar, including the first day of the week, whether you want the days of the week to show, and the colors and fonts for the calendar. The properties shown in this dialog vary for each control.
Figure 26.10. The Calendar Control Properties dialog.
Just as the properties of the control can be set or evaluated at runtime, the events of the control can be coded. To obtain a list of all the events associated with a custom control, open the Proc box in the Module window. Make sure the control name for your custom control is listed in the Object box. Figure 26.11 shows all the events for the Calendar control.
Figure 26.11. Viewing the events of the Calendar control.
The AfterUpdate event of the Calendar control is triggered when the user selects a date from the calendar. The following code changes the value of a text box named txtDateSelected to the Value property of the calPickADay control. This code is placed in the AfterUpdate event of the Calendar control so that it executes any time the user selects a date on the calendar.
Private Sub calPickADay_AfterUpdate() txtDateSelected.Value = calPickADay.Value End Sub
This code and most of the code in this chapter is contained in a file called CHAP26EX.MDB located on the sample code CD. This example is found in the form called frmPickADay.
The Calendar control is one of the more powerful OCX controls available. Fortunately, the Calendar control ships as part of the standard Access package. Understanding the properties and methods associated with the Calendar control makes it a lot easier to work with it. The properties and methods specific to the Calendar control are covered in the sections that follow.
The Day, Month, and Year properties are used to designate the day, month, and year displayed on the calendar. These properties are automatically changed at runtime as the user selects different dates on the calendar. You can modify the values programmatically, thereby changing the day, month, or year that is selected.
The Value property is one of the most important properties of the Calendar control. It is used to retrieve the selected calendar date or move the date highlight to a specific day. The following code uses the Value property to display the selected day in a message box:
Private Sub cmdDisplayDate_Click() MsgBox calSelectADay.Value End Sub
The ValueIsNull property enables you to indicate that no date is selected on the calendar. This property is used when you want to ensure that the user explicitly selects a date.
The DayFont and DayFontColor properties are used to specify the font and color for the display of the day titles. The DayFont property is further broken down into the properties Name, Size, Bold, Italic, Underline, and Strikethrough. An individual property can be modified like this:
calSelectADay.DayFont.Italic = True
You can use the With...End With construct to change several font properties at once:
With calSelectADay.DayFont .Bold = True .Italic = True .Name = "Arial" End With
The DayFontColor property can be used to easily modify the color of the day titles:
calSelectADay.DayFontColor = 16711680
The GridFont and GridFontColor properties are similar to DayFont and DayFontColor properties. The GridFont is used to determine the Font attributes for the text within the calendar, and the DayFontColor property is used to indicate the color of the text within the calendar. For example, the following routine modifies the Bold, Italic, and Name properties of the GridFont and changes the color of the days that are displayed on the calendar:
Private Sub cmdChangeGridFont_Click() With calSelectADay.GridFont .Bold = True .Italic = True .Name = "Arial" End With calSelectADay.GridFontColor = 8388736 End Sub
The DayLength and MonthLength properties are used to designate how you want the day or month titles to display. The available choices for DayLength are Short, Medium, and Long. Short displays the day as one character, Medium displays the day as a three-character abbreviation, and Long displays the full day (such as Monday). The available choices for MonthLength are Short and Long. Short displays the month as a three-character abbreviation, and Long displays the full month name. The following code is used to display the DayLength as Short and the MonthLength as Short.
Private Sub cmdChangeLength_Click() calSelectADay.DayLength = 0 calSelectADay.MonthLength = 0 End Sub
The ShowDateSelectors property is used to indicate whether combo boxes appear at the top of the calendar, allowing the user to select a month and year. This property can be set to True or False.
The ShowTitle property is used to indicate whether the month and year are displayed at the top of the calendar.
The GridLinesFormat and GridLinesColor properties are used to specify whether the gridlines are Raised, Sunken, or Flat, and they also specify the color of the gridlines.
Just as the Calendar control has numerous properties, it has several methods. These methods are actions that you can take upon the Calendar object.
The NextDay, PreviousDay, NextWeek, PreviousWeek, NextMonth, PreviousMonth, NextYear, and PreviousYear methods are all used to move the Value property of the control forward or backward by the specified period of time.
Figure 26.12 illustrates the use of the Calendar control. As you can see, the form called frmCalendar enables the user to move from day to day, month to month, or year to year. The user can also move to the current day. The user can even select a date and then click the Display Orders for Selected Date command button to view all the orders placed on the selected date.
Figure 26.12. An example of use of the Calendar control.
The code behind the Today command button illustrates the use of the Today method:
Private Sub cmdToday_Click() calPickADay.Today End Sub
Because the Today method is issued on the Calendar control, the selected day becomes the current date. The code behind the Display Orders for Selected Date command button looks like this:
Private Sub cmdOrders_Click() frmOrdersByDate.Form.RecordSource = _ "Select * from qryOrdersByDate Where OrderDate = #" _ & calPickADay.Value & "#" End Sub
This code changes the RecordSource of the subform to include only those records where the OrderDate is equal to the selected calendar date. The remainder of the code behind the frmCalendar form is discussed in the following section.
The SpinButton control is used to increment and decrement values. For example, on the frmCalendar form, SpinButton controls are used to increment and decrement the selected day, month, and year. Like the Calendar control, the SpinButton control has its own built-in properties and methods. Although the properties can be modified on the Other tab of the Properties window, it is easiest to modify them using the SpinButton properties dialog (see Figure 26.13). The easiest way to access the SpinButton dialog is to double-click on the SpinButton control whose properties you want to modify.
Figure 26.13. The SpinButton Properties dialog.
One of the most important SpinButton properties is the SpinOrientation property. This property is used to indicate whether you want the SpinButton control to be displayed vertically or horizontally. The remainder of the properties (BorderThickness, BackColor, and so on) are used to affect the appearance of the control.
The two most commonly used events of a SpinButton control are the SpinUp event and the SpinDown event. These events are used to specify what happens when the user clicks on either button of the control. The following code is placed in the SpinDown event of the spnDay SpinButton control found on frmCalendar. Notice that the code executes a PreviousDay method on the calPickADay control, causing the Calendar control to set the focus to the previous day.
Private Sub spnDay_SpinDown() calPickADay.PreviousDay End Sub
The SpinUp event of the spnDay control uses the NextDay method of the calPickADay control to cause the focus to shift to the next day.
Private Sub spnDay_SpinUp() calPickADay.NextDay End Sub
The SpinDown event of the spnMonth control uses the PreviousMonth method of the Calendar control to move focus to the same day in the previous month.
Private Sub spnMonth_SpinDown() calPickADay.PreviousMonth End Sub
The SpinUp event of the spnMonth control uses the NextMonth method of the Calendar control to move focus to the same day in the next month.
Private Sub spnMonth_SpinUp() calPickADay.NextMonth End Sub
The SpinDown and SpinUp events of the spnYear control use the PreviousYear and NextYear methods of the Calendar control to move backward and forward a year within the calendar.
Private Sub spnYear_SpinDown() calPickADay.PreviousYear End Sub Private Sub spnYear_SpinUp() calPickADay.NextYear End Sub
As you can see, by combining the various OCX controls, you can create exciting, user-friendly, utilitarian applications.
The StatusBar control enables you to quickly and easily add professional-looking status bars to your forms. An example of the use of the StatusBar control is illustrated in frmCalendar (see Figure 26.12). The StatusBar displayed in the figure has six panels. The first two panels have been configured to display the current date and time. The last three panels have been configured to display the status of the Caps, Num, and Ins keys.
Properties can be set for the StatusBar control as a whole or for the individual panels. The properties for the StatusBar control are shown in Figure 26.14. The Style property of the StatusBar control is used to specify whether you want the status bar to include multiple panels or only a single panel. The SimpleText property is used only for single-panel status bars. It is used to specify the text contained within the panel. Finally, the MousePointer property allows you to select the type of mouse pointer that appears when the mouse is over the StatusBar control.
Figure 26.14. The General properties of the StatusBar control.
Each panel of the StatusBar control has properties that can be used to affect the look and behavior of that panel. The panel properties are shown in Figure 26.15. The Style property is an important property. It is used to specify what information is displayed within the panel. It can be set to Text, Caps, Num Lock, Ins, Scroll, Time, Date, or Kana Lock. When it is set, the control can automatically sense whether the Caps Lock or other keys are active. The Text property is used to indicate the text displayed within the panel when the Style property is set to Text. The value of this property is often modified at runtime to display a specific message to the user. The Alignment property is used to specify whether the information is left- or right-aligned or centered within the panel. The Bevel property of the panel can be set to None, Insert, or Raised.
Figure 26.15. The StatusBar panel properties.
As you insert and remove panels, each panel is assigned an index. The Index property is used to refer to a specific panel at runtime. Here's an example:
Private Sub calPickADay_AfterUpdate() If calPickADay.Value = Date Then sbrStatus.Panels(3).Text = "TODAY!!!" Else sbrStatus.Panels(3).Text = "" End If End Sub
This code evaluates the calPickADay value to see whether it is equal to the current date. If so, the text of the third panel is set to "TODAY!!!". Otherwise, the text of the third panel is set to a zero-length string.
The Data Outline control allows you to view information in a hierarchical manner. For example, you can display details for customers, orders, and order detail items as you need to. Figure 26.16 illustrates this concept. Notice that all orders for the customer with the customer ID of ALFKI are displayed. Order detail items are displayed for order #10692. To expand the detail for any item, the user simply clicks on the plus sign. To collapse the detail, the user can click on the minus sign.
Figure 26.16. The Data Outline control.
The Data Outline Control Properties dialog is shown in Figure 26.17. Properties can be set for the control as a whole or for specific group levels. The properties for Level 1 are shown in Figure 26.18. Notice that the RecordSource property of the level is set to "SELECT * FROM [tblCustomers]". This causes data from the Customer table to be displayed in the first level of the outline. Because the Display Fields property is set only to CustomerID, only the customer ID from the Customer table is displayed.
Figure 26.17. The General tab of the Data Outline Control Properties dialog.
Figure 26.18. Setting Properties for a level of the Data Outline control.
If you prefer, you can configure the properties of the Data Outline control by selecting each level and filling in the properties. Fortunately, there is an easier way to accomplish this task. The Data Outline control contains a wizard to assist you with the process of designating properties. To invoke the Data Outline Control Wizard, follow these steps:
Figure 26.19. The Data Outline Control Wizard.
Figure 26.20. Using an associated form to display additional data for the selected item.
Figure 26.21. Establishing a relationship for a second level of data.
As you can see, the Data Outline control and its associated wizard are quite powerful and useful. You can easily create forms that allow a user to drill down through levels of detail quickly and easily.
The Common Dialog control is actually like many controls in one. It is used to display the standard Windows File Open, File Save As, Font, Color, and Print common dialog boxes. It is a hidden control that does not appear at runtime but whose properties and methods can be manipulated using VBA code. The form called frmCommonAndRich is shown in Figure 26.22. This form illustrates the use of several of the common dialog boxes as well as the Rich Textbox control, which is covered in the next section.
Figure 26.22. The form used to illustrate common dialog and rich text boxes.
The Button Font and Screen Color command buttons illustrate the use of the Common Dialog control. They invoke the Color and Font common dialog boxes, respectively. The code under the Click event of the cmdColor command button looks like this:
Private Sub cmdColor_Click() dlgCommon.Flags = 2 dlgCommon.ShowColor Me.Detail.BackColor = dlgCommon.Color End Sub
The code begins by setting the Flags property of the common dialog control. The Flags property is used to specify attributes of the common dialog. The value of 2 for the Color common dialog indicates that the entire Color dialog, including the portion that enables the user to create custom colors, will be displayed. The ShowColor method, when applied to the Common Dialog control, invokes the Color common dialog (see Figure 26.23). The color that the user selects is filled into the Color property of the common dialog control. This color is used to modify the BackColor property of the detail section of the form.
Figure 26.23. The Color common dialog.
Private Sub cmdFont_Click() Dim ctl As Control dlgCommon.Flags = 1 dlgCommon.ShowFont For Each ctl In Controls If TypeOf ctl Is CommandButton Then With ctl .FontName = dlgCommon.FontName .FontBold = dlgCommon.FontBold .FontItalic = dlgCommon.FontItalic .FontSize = dlgCommon.FontSize End With End If Next ctl End Sub
The Click event of cmdFont first sets the Flags property of the common dialog control to 1. For the Font common dialog, the value of 1 causes the dialog to list only the screen fonts supported by the user's system. The ShowFont method is used to invoke the actual dialog (see Figure 26.24). Using a With...End With construct, the code takes each property set in the common dialog and uses it to loop through the Controls collection of the form, modifying the font attributes of each command button.
Figure 26.24. The Font common dialog.
The File Open, File Save, and File Print common dialogs are covered in the next section.
The Rich Textbox control allows you to design a text box that gives you the opportunity to write code affecting the selected text. Properties that can be specified for the selected text include the Font, Font Size, Bold, and Italic properties. You can even add bullet points to the selected text. Furthermore, you can save the contents of the Rich Textbox control in a rich text format (RTF) file and later retrieve it back into the control. Figure 26.25 shows the many properties that can be affected for the selected text. The following code illustrates the use of several of the properties:
Figure 26.25. Properties that can be set for selected text in a rich text box.
Private Sub cmdTextColor_Click() dlgCommon.ShowColor rtfDocument.SelColor = dlgCommon.Color End Sub
This code uses the Color common dialog, discussed in the previous section, to set the SelColor property of the Rich Textbox control. The selected text appears in whatever color the user selects from the common dialog.
Private Sub cmdTextFont_Click() dlgCommon.Flags = 1 dlgCommon.ShowFont With rtfDocument .SelFontName = dlgCommon.FontName .SelBold = dlgCommon.FontBold .SelItalic = dlgCommon.FontItalic .SelFontSize = dlgCommon.FontSize End With End Sub
The Click event of the cmdTextFont command button sets the SelFontName, SelBold, SelItalic, and SelFontSize properties of the Rich Textbox control to the font, style, and size that are selected in the Font common dialog. The selected attributes are applied only to the selected text.
The Rich Textbox control contains a method called ShowSave. This method allows you to save the contents of the Rich Textbox control to an RTF file. The code looks like this:
Private Sub cmdSave_Click() dlgCommon.Filter = "RTF Files (*.rtf)|*.rtf" dlgCommon.ShowSave If dlgCommon.FileName = "" Then MsgBox "You Must Specify a File Name", vbExclamation, "File NOT Saved!" Else rtfDocument.SaveFile dlgCommon.FileName End If End Sub
The code begins by setting the Filter property of the common dialog control. This filters the filenames that are displayed in the File Save common dialog. The ShowSave method is used to invoke the Save common dialog (see Figure 26.26). After the user types in or selects a filename, the FileName property of the common dialog control is filled in with the name of the file that the user specified. If the user clicks Cancel, the FileName property contains a zero-length string, and the user is warned that the file was not saved.
Figure 26.26. The Save As common dialog.
As mentioned, just as you can save the contents of a Rich Textbox control, you can retrieve the contents of an RTF file into the control. The code looks like this:
Private Sub cmdOpen_Click() dlgCommon.FileName = "" dlgCommon.Filter = "RTF Files (*.rtf)|*.rtf" dlgCommon.InitDir = CurDir dlgCommon.ShowOpen If dlgCommon.FileName = "" Then MsgBox "You Must Specify a File Name", vbExclamation, "File Cannot _ Be Opened!" Else rtfDocument.LoadFile dlgCommon.FileName End If End Sub
The Click event of the cmdOpen command button uses the ShowOpen method to invoke the File Open common dialog (see Figure 26.27). If the user selects a file, the LoadFile method of the Rich Textbox control uses the FileName property of the Common Dialog control as the name of the file to open.
Figure 26.27. The Open common dialog.
In addition to being able to open and save the contents of a Rich Textbox control, you can print the contents of the control. The Click event of the cmdPrint command button sets the Flags property of the Common Dialog control to 0. This selects the All option button in the Print dialog (and deselects the Pages and Selection option buttons). The ShowPrinter method displays the Print common dialog (see Figure 26.28). The SelPrint method of the Rich Textbox control is then used to print the selected text with the printer that is selected in the Print common dialog.
Figure 26.28. The Print common dialog.
Private Sub cmdPrint_Click() dlgCommon.Flags = 0 dlgCommon.ShowPrinter rtfDocument.SelPrint dlgCommon.hDC End Sub
The TabStrip control enables you to conserve screen real estate by displaying data on different "pages" of the same form. The TabStrip control that ships with the Access Developer's Toolkit is the same control that you are accustomed to seeing within applications such as Microsoft Word and Microsoft Excel. It is easy to implement this control within your own forms. Figure 26.29 shows a form called frmTabbed that uses the TabStrip control. As the user clicks on each tab, the appropriate information is displayed. For example, if the user selects a customer on the Customers tab and then clicks the Orders tab, all orders relating to the selected customer are displayed. If the user selects an order on the Orders tab and then clicks the Order Details tab, all order details relating to the selected order are displayed. The code looks like this:
Private Sub tabSelect_Click() Select Case Me!tabSelect.SelectedItem.INDEX Case 1 Me!fsubCustomers.Visible = True Me!fsubOrders.Visible = False Me!fsubOrderDetails.Visible = False Case 2 Me!fsubOrders.Form.RecordSource = _ "Select * from tblOrders Where CustomerID = '" _ & Me!fsubCustomers.Form!CustomerID & "';" Me!fsubCustomers.Visible = False Me!fsubOrders.Visible = True Me!fsubOrderDetails.Visible = False Case 3 Me!fsubOrders.Form.RecordSource = _ "Select * from tblOrderDetails Where OrderID = " _ & Me!fsubOrders.Form!OrderID & ";" Me!fsubCustomers.Visible = False Me!fsubOrders.Visible = False Me!fsubOrderDetails.Visible = True End Select End Sub
Figure 26.29. A form that uses the TabStrip control.
Here's how it works. After adding a TabStrip control to a form, you can double-click the control to view its properties (see Figure 26.30). The TabStrip Control Properties dialog allows you to set properties for the TabStrip control as a whole, as well as for each tab. After the tabs have been added, you can code the Click event of the TabStrip control to determine what will happen as the user clicks on each tab.
Figure 26.30. The TabStrip control properties.
Like the StatusBar control, the TabStrip control is one-based. A Case statement is used to evaluate which tab was selected. The frmTabbed form contains three subforms: fsubCustomers, fsubOrders, and fsubOrderDetails. When the frmTabbed form first displays, only the fsubCustomers subform control is visible. As the user clicks on each tab in the TabStrip, the appropriate subform is displayed, and the other two subforms are hidden. The RecordSource for fsubOrders is modified at runtime to show only orders for the selected customer from the fsubCustomers subform, and the RecordSource for fsubOrderDetails is modified at runtime to show only the order detail items for the order selected on the fsubOrders subform.
The TabStrip control can be enhanced using an ImageList control. The ImageList control is used to store images that you will use within the form. It is populated at design time with the images that you will use. It is hidden at runtime, but any of the images it contains can be used within your form.
The form called frmImageList, shown in Figure 26.31, is similar to the frmTabbed form. The difference is that each tab contains an image. The images come from the ImageList control called imgPictures. The properties of the imgPictures ImageList control appear in Figure 26.32. Notice that three pictures have been inserted. The size on the General tab has been set to 16x16. The tabSelect TabStrip control has been modified to include imgPictures as its ImageList under the General properties tab. The index of each picture in the imgPictures ImageList control has been added as the Image property for each tab in the TabStrip control. The Image property is used to specify which image in the bound ImageList should be displayed within the particular tab.
Figure 26.31. The form frmImageList, with pictures for tabs.
Figure 26.32. The properties of the ImageList control.
Two MAPI (Messaging Application Program Interface) controls ship with Visual Basic 4.0. They can be utilized within your Access applications. One is called the MAPI Session control, and the other is called the MAPI Message control. Together, they allow you to send a mail message from within your Access application. Figure 26.33 shows a simple form, frmSendMail, that allows the user to supply a recipient and a message. When the user clicks the Send Message command button, the message is sent to the recipient. Here's what the code looks like:
Private Sub cmdSendMessage_Click() mpiSession.LogonUI = True mpiSession.DownloadMail = False mpiSession.SignOn With mpiMessage .MsgIndex = -1 .MsgNoteText = txtMessage .RecipDisplayName = txtRecipient .AddressResolveUI = True .SessionID = mpiSession.SessionID End With On Error Resume Next mpiMessage.ResolveName Select Case Err.Number Case 0 mpiMessage.Send False Case Else MsgBox "Error # " & Err.Number & ": " & Err.Description End Select End Sub
Figure 26.33. Using the MAPI controls.
This routine begins by setting the LogonUI property to True. When set to True, the LogonUI property indicates that a dialog is provided for sign-on. Next, the DownloadMail property is set to False. This means that no mail will be downloaded during the process. Then, the SignOn method is issued on the mpiSession control. The SignOn method logs the user into the account specified by the UserName and Password properties and provides a session handle to the underlying message subsystem.
Now the routine is ready to interact with the mpiMessage control. Several properties of the mpiMessage control are set. The MessageIndex property is set to -1. This sets the index for the current message. Next, the MsgNoteText property is set. This property is used to indicate the text for the message. The RecipDisplayName property is used to reference the currently indexed recipient. The AddressResolveUI is set to True, which means that the recipient name dialog is displayed if Access is unable to resolve the recipient. Finally, the SessionID property is set to the messaging handle from the mpiSession control.
After all the properties have been set, you are ready to send the message. The ResolveName method of the mpiMessage control resolves the name of the currently indexed recipient. If the name is resolved, the Send method of the mpiMessage control is used to send the message.
Some OCX controls can be distributed freely, and others contain various levels of restrictions. The licensing policies for a particular OCX control are determined by its vendor.
The licensing rules that are in effect for an OCX are enforceable by law. This means that improper distribution of the control is a crime. Distributing an OCX control without proper licensing is just like copying a software product illegally.
If you have any questions about the licensing of a third-party control, consult the vendor who authored the control. Sometimes a one-time fee is required so that you can freely distribute the OCX. In other cases, a royalty might be required for each copy of the control that is distributed. If you aren't sure whether you want to purchase a third-party control, you might want to contact the vendor of the control. Many vendors allow potential customers to try out their products for a limited period of time. In fact, many of the demo versions are available online.
Custom controls can be utilized in many places in the time and billing application. Your imagination determines where controls will enhance the usability of the application. The following examples illustrate a few potential uses of the OLE custom controls.
One example is in the frmReportDateRange dialog shown in Figure 26.34. The Calendar control can be used to populate the Beginning Date and Ending Date text boxes. The code looks like this:
Private Sub cmdSetDates_Click() On Error GoTo cmdSetDates_Error If cmdSetDates.Caption = "Set Beginning Date" Then BeginDate = calSetDates.Value cmdSetDates.Caption = "Set Ending Date" Else EndDate = calSetDates.Value cmdSetDates.Caption = "Set Beginning Date" End If Exit Sub cmdSetDates_Error: MsgBox "Error # " & Err.Number & ": " & Err.Description Exit Sub End Sub
Figure 26.34. Adding the Calendar control to the Report Criteria form.
Because the same calendar is used to populate the beginning date and ending date text boxes, the form contains a command button with a caption that toggles. The user can select a date and then click Set Beginning Date. The BeginDate text box is populated with the value selected on the calendar, and the caption of the command button is set to display "Set Ending Date". If the caption of the command button says "Set Ending Date" and the user clicks the command button, the EndDate text box is populated with the value selected on the calendar, and the caption of the command button is changed to say "Set Beginning Date".
Another potential use of an OLE custom control is to use the Data Outline control to display client, project, and hours billed on one form. Figure 26.35 illustrates this use.
Figure 26.35. Using the Data Outline control to display client, project, and billing information.
The top level of the Data Outline control displays the ClientID and CompanyName from tblClient. The top level is linked by ClientID to the second level, which displays the ProjectID, ProjectName, ProjectBeginDate, and ProjectEndDate from the tblProjects table. The third and final level of the Data Outline control displays the TimeCardID, DateWorked, BillableHours, and BillingRate from the tblTimeCardHours table. The first level is linked to the frmClientProjectBillingInformation form so that additional information about a client is displayed as the user moves from client to client in the outline.
OCX controls greatly extend the capabilities of Access 95. They allow you to incorporate additional functionality into your applications. OCX controls are easy to use and extremely powerful. Each one contains its own properties, events, and methods.
By modifying properties, reacting to events, and executing methods, you can take advantage of the rich features contained within each OCX. The licensing of OCX controls varies. Therefore, you need to investigate the licensing aspects of each particular control that you want to use to know whether and under what conditions you can distribute it to your users.