Working with Controls | Beginning Access 2007 VBA

Prior to reading this book, you probably worked with a variety of controls on Access forms, such as labels, text boxes, command buttons, and combo boxes. As you are already aware, controls can be added to a form using the Controls from the Design ribbon on the toolbar, as shown in Figure 6-6. Now turn your attention to a related topic: working with controls.

image from book
Figure 6-6

In this section, you’ll explore a few ways to work with these controls through VBA code. You learned in Chapter 3 that objects have properties and methods that can be displayed in the Properties window in design view. You also learned that the properties, methods, and events of an object can be managed using VBA code. Controls are examples of objects that can be managed in these ways.

For example, you learned from the ADO examples in Chapter 5 that you can display data in a text box. An example of code that displays a value in a text box is shown here:

  Me.txtLastName = rsContacts!txtLastName

Although text boxes are great for simple data entry and the displaying of a single and small value, at times you will require more sophisticated ways to display data. In the next few sections, you will look at some examples of more sophisticated controls that you can use to build more robust applications.

Combo Boxes versus List Boxes

Combo boxes and list boxes enable you to display various choices in a list. They also allow you to display multiple columns of data in the single listing.

Combo boxes and list boxes have a lot in common, but they also differ in some ways, as is illustrated in the comparison table that follows.

Open table as spreadsheet

Feature	Combo Box	List Box
Values accepted from user	Allows the user to select a value from the list or to add a new value that is not on the list, depending on the setting for the Limit to List property.	Allows the user to select only a value in the list.
Locating values as user types	Matches the pattern as you type so that the value in the list that matches the letters you have typed appears as the selection.	Takes you to the first item in the list that starts with the letter you type.
Selection of values in a list	Allows a user to select only a single value.	Allows a user to select one or more values in the list.

You can programmatically add and remove items to and from combo boxes and list boxes by using the AddItem and RemoveItem methods. The below example uses the AddItem method to add values in a recordset to a combo box named cboPlan.

  Dim rsPlans As New ADODB.Recordset 'populate the list of plans from the database Set rsPlans = ExecuteSPRetrieveRS("spRetrievePlans", 0) cboPlan.RowSource = "" cboPlan.LimitToList = True cboPlan.ColumnCount = 2 cboPlan.RowSourceType = "Value List" cboPlan.BoundColumn = 0 Do While Not rsPlans.EOF     'populate the priority combo box     cboPlan.AddItem rsPlans!PlanId & ";" & rsPlans!PlanName     rsPlans.MoveNext Loop

In this example, for each entry in the combo box, the PlanId and PlanName are displayed because the AddItem method received values separated a by semicolon (;). The semicolon is used to designate that multiple columns should be created. For example, the following code adds values for both last and first name columns to the list:

  cboName.AddItem "Doe;John"

Try It Out-Populating Combo and List Boxes

You will now populate a combo box and a list box with the same values and illustrate how to retrieve the selection from the user.

Open the frmAccounts form of the current Ch6CodeExamples database.
Add a combo box and a list box control to the form, using the ToolBox.
Set the Name property of the combo box to cboAccounts and the Name property of the list box control lstAccounts. Set each of the labels to Accounts. The form should look similar to the one shown in Figure 6-7.

Figure 6-7

Add the following code to the Load event for the form:

  Private Sub Form_Load()     'populate the combo box with values from a     'specified list     With cboAccounts         .RowSource = ""         .ColumnCount = 2         .RowSourceType = "Value List"         .LimitToList = True         .BoundColumn = 0         .AddItem "Old National Bank;Car Payment"         .AddItem "Chase Manhattan;MasterCard"         .AddItem "Countrywide Home Loans;House Payment"     End With     'populate the list box with values from a     'specified list     With lstAccounts         .RowSource = ""         .ColumnCount = 2         .RowSourceType = "Value List"         .BoundColumn = 0         .AddItem "Old National Bank;Car Payment"         .AddItem "Chase Manhattan;MasterCard"         .AddItem "Countrywide Home Loans;House Payment"     End With End Sub

Add the following code to the Change event of the combo box:

  Private Sub cboAccounts_Change() 'display a message to the user of the selected valueMsgBox cboAccounts.Text End Sub

Add the following code to the Click event of the list box:

  Private Sub lstAccounts_Click() 'display a message to the user of the first value in 'the selected column MsgBox lstAccounts.Column(0) End Sub

Open the form in view mode to run the form. You should see a screen similar to Figure 6.8.

Figure 6-8
Type the letter C in the combo box. The text should jump to Chase Manhattan. Then, type an O in the combo box, and the text should jump to Countrywide Home Loans (because it starts with a CO). Repeat these steps for the list box. You should find that when you type C, Chase Manhattan is selected, and when you type O, Old National Bank is selected.
You should see message boxes displaying the value selected.

How It Works

Using the AddItem method, you added the same values to the combo box and the list box so that you could see the differences between the two controls side by side:

 Private Sub Form_Load()     'populate the combo box with values from a     'specified list     With cboAccounts         .RowSource = ""         .ColumnCount = 2         .RowSourceType = "Value List"         .LimitToList = True         .BoundColumn = 0         .AddItem "Old National Bank;Car Payment"         .AddItem "Chase Manhattan;MasterCard"         .AddItem "Countrywide Home Loans;House Payment"     End With     'populate the list box with values from a     'specified list    With lstAccounts         .RowSource = ""         .ColumnCount = 2         .RowSourceType = "Value List"         .BoundColumn = 0         .AddItem "Old National Bank;Car Payment"         .AddItem "Chase Manhattan;MasterCard"         .AddItem "Countrywide Home Loans;House Payment"     End With End Sub

The LimitToList property of the combo box was set to True, so only values in the list could be selected. Various properties such as RowSource and RowSource type were specified to indicate that the values would be coming from a value list, as opposed to a table or other source.

You also added code to display the selected value from the combo box and list box. You changed the value in the combo box so that a message box shows users the value displayed in the text field.

 Private Sub cboAccounts_Change() 'display a message to the user of the selected value MsgBox cboAccounts.Text End Sub

When you select a value from the list box, a message box shows users the value contained in the first selected column:

 Private Sub lstAccounts_Click() 'display a message to the user of the first value in 'the selected columnMsgBox lstAccounts.Column(0) End Sub

Tab Controls and Subforms

When you have a lot of data that needs to be displayed on a single form, tab controls provide one way or organizing data on a single form into separate components. A tab control allows you to place separate controls on each tab. For example, you may have one tab that contains checking account information and another tab that contains savings account information, as shown in Figure 6-9.

image from book
Figure 6-9

Tip

The case study in Chapter 13 illustrates using a tab control for different portions of the project record.

You can then refer to the pages of the tab control in VBA code, as shown here:

  'show the checking account register Page1.Visible = True 'hide the savings account register Page2.Visible = False

In the preceding code example, the checking account register is displayed to the user and the savings account is not. This may be useful in a scenario when one user is named on both accounts, but another user is named on only one of the accounts. You could, for example, control the visibility of the tabs based on the specified user ID.

Another way to handle forms that include complex amounts or types of data is to use a subform. A subform is a form within a form. To insert a subform, you simply drag and drop the Subform control from the ToolBox just as you would any other control. You then change the Source Object property of the subform to the name of the form you wish to display. An example of a subform is shown in Figure 6-10.

image from book
Figure 6-10

Figure 6-10 shows a transactions form displayed within an accounts form. Any form that you create can be displayed within another form. You then tie the subform to the parent form so that the data it displays relates to the data on the parent form (for example, for the same account). One way to tie a subform to a parent form is using the Link Child Fields and Link Master Fields properties.

Tip

Whenever you make changes to a subform you are changing the original form, so be careful.