Microsoft Office 2000/Visual Basic Programmer's Guide

Working with Controls on Forms and Reports

Although forms, reports, and data access pages are the objects you use to present or gather data from users, it is really the controls on these objects that do all the work. Access contains a wide variety of built-in controls that you can use on these objects.

Forms, reports, and data access pages all use controls to display information or to allow the user to interact with the object or the data it contains. Forms and reports have a Controls property that returns a collection of all the controls on the object. For more information about working with controls on data access pages, see Chapter 12, "Using Web Technologies."

Controls Collections for Forms and Reports

You can refer to a control on a form or report as a member of the Controls collection or by using the name of the control itself. For example, the following lines of code illustrate three ways to return the RowSource property setting for a combo box control on a form. Since the Controls property is the default property of a Form object, you can refer to the control's name without explicitly specifying the Controls property, as shown in the second and third examples that follow:

strSource = Forms("SalesTotals").Controls("cboSelectSalesPerson").RowSource
strSource = Forms("SalesTotals")!cboSelectSalesPerson.RowSource
strSource = Forms!SalesTotals!cboSelectSalesPerson.RowSource

Note   The ! operator is used to refer to user-defined items, such as forms, reports, and controls on Access forms or reports.

You can also use the Controls property to work with all the controls on a form or report. For example, the following code loops through all the controls on a form and sets the Text property for each text box control to a zero-length string (""):

Sub ClearText(frmCurrent As Form)
   Dim ctlCurrent As Control

   For Each ctlCurrent In frmCurrent.Controls
      If ctlCurrent.ControlType = acTextBox Then
         ctlCurrent.Value = ""
      End If
   Next ctlCurrent
End Sub

The ClearText procedure is available in the modSolutionsUtilities module in the Solutions9.mdb file in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

You can pass the Form object to the ClearText procedure by using the Me property. The Me property returns an object representing the form, report, or class module where code is currently running. For example, you could call the ClearText procedure from a form by using the following syntax:

Call ClearText(Me)

Certain controls on forms and reports also have a Controls collection. For example, the option group control may contain a Controls collection representing option button, toggle button, check box, or label controls in the option group. The tab control has a Pages collection containing a Page object for each page in the tab control. Each Page object also has a Controls collection representing all the controls on a page in a tab control.

Subform and Subreport Controls

Forms and reports can also contain subform or subreport controls that contain another form or report. These controls allow you to display related records from another form or report within a main form or report. A common example of this is a Customers form that contains a subform containing customer orders. You use the SourceObject property of the subform or subreport control to specify the form or report that will be displayed in the control.

The form or report in the subform or subreport control can share a common field, known as the linking field, with the records displayed in the main form or report. The linking field is used to synchronize the records between the subform or subreport and the main form or report; for example, if the record sources for an Orders subform and a Customers main form both contain a CustomerID field, this would be the common field that links the two forms. To specify the linking field, you use the LinkChildFields property of the subform or subreport control and LinkMasterFields property of the main form or report. However, the easiest way to create a linked subform or subreport is to open the main form or report in Design view, drag the appropriate form or report from the Database window to the main form or report, and then release the mouse button.

You use the Form property of a subform control to refer to controls on a subform. You use the Report property of a subreport control to refer to controls on a subreport. The following examples illustrate how to get the value of a control on a subform or subreport by using VBA. The first two lines show alternative ways to reference a control named Quantity on a subform. The last line shows how to use the RecordCount property to get the number of records contained in the recordset associated with a subreport control:

lngOrderQuantity = Forms("CustomerOrders").Controls("SubForm1").Form!Quantity
lngOrderQuantity = Forms!CustomerOrders!SubForm1.Form!Quantity

lngNumProducts = Reports!SuppliersAndProducts!SubReport1.Form.Recordset.RecordCount

List Box and Combo Box Controls on Forms

List box and combo box controls are very powerful and versatile tools for displaying information and allowing the user to interact with the data displayed on a form. These controls work differently in Access than list box and combo box controls in other Office applications and it is important to understand these differences if you want to use these controls effectively.

If you are used to working with these controls in other applications, the most important difference is how you add items to and remove items from these controls. In other applications, these controls have AddItem and RemoveItem methods to add and remove items. These methods are not supported for Access list and combo box controls. Instead, you use combinations of RowSource and RowSourceType properties to specify the data that appears in a list box or combo box control. The relationship between the RowSource property setting and the RowSourceType property setting is illustrated in the following table.

For more information about setting the RowSourceType and RowSource properties to fill a list box or combo box control, search the Microsoft Access Visual Basic Reference Help index for "RowSource property" or "RowSourceType property."

If you are creating list box or combo box controls through the Access user interface, you can take advantage of the List Box Wizard and the Combo Box Wizard to set the various properties needed to display data in these controls. To use these wizards, make sure the Control Wizards tool in the toolbox is pressed in, then click the List Box or Combo Box tool in the toolbox, and then click the place on the form where you want the control to appear. Follow the instructions displayed by the wizard.

You can set the properties of a list box or combo box control without using the wizard by using the control's property sheet or VBA. You use the ControlSource property to bind a list box or combo box control to a field in the recordset specified in the form's RecordSource property. As mentioned earlier, you use the RowSource property in combination with the RowSourceType property to specify the source of data for the list box or combo box control.

The BoundColumn property specifies which column in the record source specified by the RowSource property will contain the value of the list box or combo box control. If a list box or combo box control does not have a ControlSource property setting, you can set the BoundColumn property to 0. When you do this, the Value property of the control will contain the row number of the selected row specified by the RowSource property. The row number of the selected row is the same as the value of the control's ListIndex property. The ColumnCount and ColumnWidths properties specify which columns are displayed in the control.

The following sample fills a combo box control with data from an SQL statement, specifies which column in the SQL statement specified by the RowSource property will contain the value for the control, and uses the ColumnWidths property to specify which columns are displayed in the control:

With Me!cboEmployees
   .RowSource = "SELECT EmployeeID, FirstName, " _
      & "LastName FROM Employees ORDER BY LastName"
   .RowSourceType = "Table/Query"
   .BoundColumn = 1
   .ColumnCount = 3
   .ColumnWidths = "0in;.5in;.5in"
   .ColumnHeads = False
   .ListRows = 5
End With

The preceding code fills a combo box with data from 3 fields (columns) from each record (row) in the Employees table, as specified by the RowSource property. The BoundColumn property is set to the first field in the Employees table; in this case, EmployeeID. When an item is selected from the combo box, the value of the EmployeeID field will be the control's value and is the value saved to the field specified by the ControlSource property. Note that the first column in the ColumnWidths property is set to 0 inches. This hides the bound column (EmployeeID) from the user when the combo box's drop-down list is displayed. The user sees only the FirstName and LastName fields, and these fields are displayed in .5-inch wide columns. Note also that the ColumnHeads property is set to False, meaning that the names of the FirstName and LastName fields are not shown in the control's drop-down list. And finally, the ListRows property is set to 5, specifying that the control's drop-down list will display only 5 records at a time.

Using a User-Defined Function to Fill a List Box or Combo Box Control

You can specify a user-defined function as the RowSourceType property setting for a list box or combo box control. The function you use for this property setting has to meet specific criteria in order to work correctly because the function is called repeatedly as Access fills the control with data. For more information about creating and using user-defined functions to fill a list box or combo box control, search the Microsoft Access Visual Basic Reference Help index for "RowSourceType property," then open the "RowSourceType, RowSource Properties" topic, and then use the See Also jump to open the "RowSourceType Property (User-Defined Function) — Code Argument Values" topic. To see a sample procedure used to fill a combo box control, see the AddAllToList procedure in the modSolutionsUtilities module in Solutions9.mdb in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

Adding New Values to a Combo Box Control

You use the LimitToList property to specify whether a user can add new values to a bound combo box from the user interface when the form is in Form view or Datasheet view. When this property is set to True (the default), the user can't add new items to the combo box. If the BoundColumn property is set to any column other than 1, Access will automatically set the LimitToList property to True. When this property is set to False, new values are added to the underlying record source specified by the RowSource property.

When the LimitToList property is set to True, any attempt to add a new item to a combo box control will cause the NotInList event to occur. You can add code to the NotInList event procedure to handle the attempt to add new data to the control. This event procedure uses the NewData and Response arguments to represent the new data the user has tried to enter and the response you want to provide to the attempt to add new data. Setting the Response argument to one of the following built-in constants specifies how you want to respond to the attempt to add data to the control: acDataErrAdded, acDataErrContinue, or acDataErrDisplay. For example, the following sample illustrates one way to add new data to a combo box control:

Private Sub CategoryID_NotInList(NewData As String, _
                               Response As Integer)

   If MsgBox("Do you want to add '" _
      & NewData & "' to the items in this control?", _
      vbOKCancel, "Add New Item?") = vbOK Then

      ' Remove new data from combo box so control can be requeried
      ' after the AddNewData form is closed.
      DoCmd.RunCommand acCmdUndo

      ' Display form to collect data needed for the new record.
      DoCmd.OpenForm "AddNewData", acNormal, , , acAdd, acDialog, NewData

      ' Continue without displaying default error message.
      Response = acDataErrAdded
   Else
      Response = acDataErrContinue
   End If
End Sub

The CategoryID control's NotInList event procedure is available in the Form_EnterOrEditProducts module in Solutions9.mdb in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.

For more information about how to use the NotInList event procedure, search the Microsoft Access Visual Basic Reference Help index for "NotInList event."

Enabling Multiple Selections in a List Box Control

To allow users to make multiple selections from a list box control, you set the MultiSelect property. When the MultiSelect property is set to Simple (2) or Extended (1), the Value property of the control is Null. You work with multiple selections in a list box control by using the Selected, ItemsSelected, and Column properties.