Introduction to Windows Controls

Controls Fundamentals

Introduction

A Windows control, also called a control, is an object positioned on a form or a report (or any container). It allows a user to interact with the computer or with an application such as a database. This means that, when designing the interface of a database, you add forms and reports, then populate them with the necessary objects.

Microsoft Access supports various types of controls. The most basic or the most regularly used controls are available in the Controls section of the Ribbon when the form or report is displaying in the Design View:

If you resize the Ribbon to a size that cannot show all controls, to show the hidden objects, you can click a down-pointing arrow:

Besides the objects in the Controls section of the Ribbon, Microsoft Access provides additional ones. These are referred to as ActiveX controls. To access an ActiveX control, in the Controls section of the Ribbon, click the More button and click ActiveX Controls. A dialog box will display:

Practical Learning: Introducing Forms

1. Start Microsoft Accecss
2. Click Blank Desktop Database
3. Set the file name to Brokerage Company1
4. Click Create
5. On the Ribbon, click Create
6. In the Tables section, click Table
7. On the table, double-click ID to put into edit mode
8. Press F2 and press Home
9. Type Transaction to get TransactionID and press Enter
10. In the list that appears under Click to Add, click Date & Time
11. Type TransactionDate and press Enter
12. Press Esc to dismiss the list
13. Close the table
14. When asked whether you want to save, click Yes
15. Set the table name to Transactions and press Enter
16. On the Ribbon, click Create
17. In the Forms section, click Form Design
18. From what we learned in the previous lesson, do this.
    Right-click the body of the form and click Form Header/Footer
19. On the form, double-click the Form Header bar
20. On the Property Sheet, click the All tab and change the following characteristics:
    Height: 0.4
    Back Color: Blue, Accent 5, Darker 25% (Theme Colors, 9th column, 5th row)
21. On the form, click the Detail bar
22. In the All tab of the Property Sheet, change the Back Color field to Background Form
23. On the form, click the Form Footer bar
24. In the All tab of the Property Sheet, change the following characteristics:
    Height: .42
    Back Color: More Colors - Custom - Red: 25
    Green: 55
    Blue: 95 and click OK
25. Right-click the title bar of the form (Form1) and click Close
26. When asked whether you want to save, click Yes
27. Change the name to Agents and click OK
28. On the Ribbon, click Create
29. In the Queries section, click Query Design
30. On the Show Table dialog box, click Close
31. Right-click somewhere in the body of the window and click SQL View
32. Type the following code:

    CREATE TABLE Agents
    (
        AgentCode char(12),
        FirstName text(20),
        LastName text(20),
        Title string(50),
        PayFrequency  varchar(25)
    );

33. To create the table, on the Ribbon, click Design if necessary and click the Run button
34. Select the whole contents of the Query window and type:

    CREATE TABLE Companies
    (
        CompanyCode text(10),
        CompanyName varchar(60)
    );

35. To create the table, on the Ribbon, click the Run button
36. Select the whole contents of the Query window and type:

    ALTER TABLE Transactions
    ADD COLUMN AgentCode char(12);

37. To modify the table, on the Ribbon, click the Run button
38. Change the code as follows:

    ALTER TABLE Transactions
    ADD COLUMN CompanyCode char(10);

39. To modify the table, on the Ribbon, click the Run button
40. Change the code as follows:

    ALTER TABLE Transactions
    ADD COLUMN NumberOfShares Long;

41. To modify the table, on the Ribbon, click the Run button
42. Change the code as follows:

    ALTER TABLE Transactions
    ADD COLUMN PricePerShare double;

43. To modify the table, on the Ribbon, click the Run button
44. Change the code as follows:

    ALTER TABLE Transactions
    ADD COLUMN PaymentStatus string(25);

45. To modify the table, on the Ribbon, click the Run button
46. Close the Query window
47. When asked whether you want to save, click No
48. In the Navigation Pane, right-click the Agents form and click Design View

Visually Adding Controls to a Form or a Report

To visually select a control, click it in the Controls section of the Ribbon. Then, on the form or report, click an area of your choice:

To visually add the same control many times to a form or report, in the Controls section of the Ribbon, right-click the control and click Drop Multiple controls:

On the form or report, click where you want to (temporarily) position the control. To dismiss the multiple choice, in the Design tab of the Ribbon, click the Select button.

Programmatically Adding a Control to a Form or Report

To visually add a Windows to a form or report, in the Controls section of the Ribbon, click the desired control and click the desired area on the form or report. To add an ActiveX controls. To add an ActiveX control to a form or report, in the Insert ActiveX Controls dialog box, click the desired control and click OK.

To let you programmatically create a control, the Application class is equipped with a method named CreateControl. Its syntax is:

Function CreateControl(ByVal FormName/ReportName As String, _
		       ByVal Controltype As AcControlType, 
	               ByVal Optional Section As AcSection = AcSection.acDetail, _
		       ByVal Optional Parent As String, _
		       ByVal Optional ColumnName As String, 
	               ByVal Optional Left As Integer, _
		       ByVal Optional Top As Integer, _
		       ByVal Optional Width As Integer, _
		       ByVal Optional Height As Integer) As Control

The first argument is the name of the form or report on which the control will be positioned. For this method to work, the form or report must be opened in Design View. The second argument is a member of the AcControlType enumeration. The controls available are:

AcControlType Member	Ribbon Control	Control Name
acAttachment		Attachment
acBoundObjectFrame		Bound Object Frame
acCheckBox		Check box
acComboBox		Combo Box
acCommandButton		Command button
acCustomControl		ActiveX control
acImage		Image
acLabel		Label
acLine		Line
acListBox		List box
acNavigationControl		Navigation Control
acObjectFrame		Unbound Object Frame
acOptionButton		Option button
acOptionGroup		Option group
acPage		Page
acPageBreak		Page break
acRectangle		Rectangle
acSubform		Sub-Form
acTabCtl		Tab control
acTextBox		Text box
acToggleButton		Toggle button
acWebBrowser		Web Browser

Only the first and the second arguments are required. Here is an example of calling the Application.CreateObject method:

Private Sub cmdCreateControl_Click()
    Application.CreateControl("Central", AcControlType.acTextBox)
End Sub

Because the Application object is automatically available when you start Microsoft Access, you can omit Application in your code. In the same way, you can omit the AcControlType enumeration.

The third argument specifies in what section of the form or report the new control would be positioned. This argument is a member the AcSection enumeration. The default value of this argument is acDetail. This means that if you omit the third argument as in the above code, the control would be created in the Detail section. If you want the control to be positioned in another section, pass a third argument and specify the desired member of the AcSection enumeration. Here is an example of passing this argument:

Private Sub cmdCreateControl_Click()
     CreateControl "Central", AcControlType.acTextBox, acDetail
End Sub

The fourth argument is the name of the control that would serve as the parent of the new control. It can be the name of the form or report on which the control will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
     CreateControl "Central", AcControlType.acTextBox, acDetail, "Central"
End Sub

You can pass this argument as an empty string "".

Invisible Controls

Besides the Controls you can see, Microsoft Windows provides some controls that a user cannot see but can feel its effects. An example is the timer. As a programmer, you create and configure such a control and it will simply act while the user is using your database.

Using the Section of a Form or Report

When adding a control to a form or report, you must specify in what section the control will be positioned. If you are adding the controls manually, you can click the control in the Controls section of the Ribbon and click the desired section on the form or report. If you are programmatically creating the control, pass the third argument of the Application.CreateControl() method as a member the AcSection enumeration. The default value of this argument is acDetail. This means that if you omit the third argument, the control would be positioned in the Detail section.

The fourth argument is the name of the control that would serve as the parent of the new control. It can be the name of the form or report on which the control will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
     CreateControl "Central", AcControlType.acTextBox, acDetail, "Central"
End Sub

You can pass this argument as an empty string "".

Practical Learning: Introduction to Fields

1. In the Controls section of the Design tab of the Ribbon, click the Text Box
2. Click somewhere in the top-center side below the Detail bar of the form (no precision required)
3. In the Controls section of the Ribbon, right-click the Text Box and click Drop Multiple Controls
4. On the form, click somewhere below the previously added text box
5. Click below the previously added text box
6. Click below the previously added text box
7. Click below the previously added text box
8. In the Controls section of the Ribbon, click the Select button

The Collection of Controls of a Form or Report

A form or a control is mostly used as a container because it hosts some of the controls of a database. The controls that a form or a report hosts are members of a collection named Controls.

The Properties of Windows Controls

Introduction to Controls Characteristics

Every Windows control is managed through a class. We know that a typical class is equipped with properties that define its characteristics. In the same way, the class of a Windows control has properties. As mentioned in Lesson 6, the most fundamental class for Windows controls is named Control. This class is equipped with the characteristics and methods that all or most controls share. Other than that, some controls are treated as categories where they share some characteristics that the controls in other groups don't have or don't need. Below that, some controls have some characteristics that only they need.

The Property Sheet

After adding a control to a form or report, whether you manually positioned the control or you created it programmatically, as seen for forms and repots, you can use the Property Sheet and/or the Properties window to manage the properties of the Windows control.

In Microsoft Access, to get the Property Sheet of the properties associated with a control, while the form or report is in Design View:

• Right-click the control and click Properties
• Double-click the control

This would display the Property Sheet of the control.

The Properties Window

In the Microsoft Visual Basic environment, the Properties window displays the characteristics of the control selected in Microsoft Access or in the Object combo box of the Code Editor in Microsoft Visual Basic.

Selecting a Control

To select the control whose properties you want to access:

• If you are working in Microsoft Access, click the control on the form or report
• In both the Property Sheet or the Properties window, click the arrow of the top combo box to display the list of controls and select the desired object:

Common Events of Windows Controls

Introduction

There are some events that many types of controls, including forms or reports, can fire. For example, many controls accept that you use the mouse with them. Some other controls allow the keyboard.

Click

When the user clicks an object, the control fires a simple event named Click (On Click in the Property Sheet, Click in the Procedure combo box, and OnClick in the Properties window). When this happens, no detailed information is provided.

The Double-Click Event

When the user double-clicks something, the control fires an event named DblClick (On Dbl Click in the Property Sheet, DblClick in the Procedure combo box, and OnClick in the Properties window).

Pressing a Key

When the user presses a key on a keyboard, the object on which the characters are typed sends one or more messages to the operating system. There are three main events that Microsoft Windows associates to the keyboard.

If the user presses a key on the keyboard, the operating system finds out if that key produces a character. If that's the case, the object fires an event named On Key Press. The formula of this event is:

Private Sub Something_KeyPress(KeyAscii As Integer)

End Sub

The message of this event carries the ASCII code of the character corresponding to the key. This means that every character key (a, b, c, d, etc, or #, /, %, etc) has an equivalent ASCII code. You can find out what key the user had pressed.

To help you identify the key, check the value of the KeyAscii argument.

Keying Down

Besides the alphanumeric and symbol keys, the keyboard has other keys that do not display a character. Examples of these keys are Shift or Ctrl. To identify when the user presses one of these keys or in combination with a character key, you can use the On Key Down event. Its formula is:

Private Sub Something_KeyDown(KeyCode As Integer, Shift As Integer)

End Sub

The first argument represents the character or symbol key that was pressed. The second argument represents the Shift, Ctrl, or Alt key if it was pressed also. You can find out what combination of keys the user pressed.

Releasing a Key

When the user releases a key that was pressed, the control fires an even named On Key Up. Its formula is:

Private Sub Something_KeyUp(KeyCode As Integer, Shift As Integer)

End Sub

This event takes the same arguments as keying down. To identify the key or combination of keys that were pressed, you use the same approach as the On Key Down event.

Mouse Events

A mouse is equipped with buttons, usually two, that the user presses to request an action. Compared to the keyboard, the mouse claims many more events that are directly or indirectly related to pressing one of its buttons.

When the user presses one of the buttons on the mouse, an event called On Mouse Down fires. This event carries enough information through three parameters. It appears as follows:

Private Sub Something_MouseDown(Button As Integer, Shift As Integer,
      X As Single, Y As Single)

End Sub

• The operating system needs to know what button was pressed; this is represented as the left or the right button. The left button is known as vbLeftButton. The right button is referenced as vbRightButton. If the mouse is equipped with a middle button, it would be recognized as vbMiddleButton. These buttons have (constant) numeric values of 0, 1, and 2 respectively
• Secondly, the operating system needs to know whether a special key, Shift, Ctrl, or Alt, was pressed. These buttons are called vbShiftMask, vbCtrlMask, and vbAltMask respectively. They are represented with 1, 2, and 4 respectively
• Lastly, the operating system needs to know the screen coordinates of the mouse pointer, that is, the coordinates of the point where the mouse landed. X represents the distance from the left border of the screen to the point where the mouse landed. Y represents the distance from the top border of the screen to the point where the mouse landed

When the user releases a button that was pressed on the mouse, the On Mouse Up event fires. It provides the same types of information as the MouseDown event:

Private Sub Something_MouseUp(Button As Integer, Shift As Integer,
      X As Single, Y As Single)

End Sub

The On Mouse Move event fires while the user is moving the mouse on an object. It provides the same pieces of information as the On Mouse Down and the On Mouse Up events:

Private Sub txtFirstName_MouseMove(Button As Integer, Shift As Integer,
      X As Single, Y As Single)

End Sub

Primary Characteristics of Controls

The Name of a Control

After adding a control to a form, the control must have a name. Microsoft Access assigns a default name to every newly added control. To specify or change the name of a control:

• Access the Property Sheet of the control. In the Other or the All tab, click Name and type the desired name
• In the Properties window, click (Name) and edit it

If you are programmatically creating a control, declare a variable of type Control and give it a name. Use the Set operator to assign the return value of the Application.CreateObject() method. Call the method as a function using parentheses. Once you have created the control, you can use the name of the variable as the name of the control. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlLastName As Control

    Set ctlLastName = CreateControl("Customers1", _
                                    AcControlType.acTextBox, _
                                    acDetail, _
                                    "Customers1")

    . . . Use the control here

End Sub

After using the control in the code, you should free the memory it was using. To do this, assign Nothing to it. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlLastName As Control

    Set ctlLastName = CreateControl("Customers1", _
                                    AcControlType.acTextBox, _
                                    acDetail, _
                                    "Customers1")

    . . . Use the control here

    Set ctlLastName = Nothing
End Sub

Practical Learning: Naming a Control

1. On the form, click the top text box
2. In the Property Sheet, click the Other tab
3. Click Name and type Agent Identification

The Text of a Control

Depending on the type of control, for most of them, the primary characteristic is the text it displays. Also depending on the control, you can control the text visually or programmatically. In fact, the property of this text depends on the type of control.

For some controls, the text is represented by a property named Caption. Here is an example of using it:

Private Sub cmdProperties_Click()
    Something.Caption = "Stella Productions"
End Sub

For some other controls, the text is represented by a property named Text. To specify the text of such controls, use only the name of the control and assign the desired text to it. Here is an example that specifies the text of a control:

Private Sub cmdProperties_Click()
    ctlSomething = "Jonathan"
End Sub

In both cases, if the value is a number, assign it.

Practical Learning: Specifying the text of a Control

1. On the form, click the first label (the label on the left of the first text box)
2. In the Property Sheet, click the Format tab, click Caption and type Agent Code:
3. On the form, click the second label
4. In the Format tab of the Property Sheet, click Caption and type First Name:
5. On the form, click the third label
6. Click the third label again to put it into edit mode
7. Edit it to display Last Name: and press Enter
8. On the form, click the fourth label
9. Click the fourth label again and edit it to get Title:
10. On the form, click the last label and click it again to put it into edit mode
11. Select its text and type Pay Frequency:
12. Click the Properties button of the form
13. In the All tab of the Property Sheet, click Caption
14. Type Brokerage Company - Agent
15. In the Controls section of the Ribbon, click the Label and click somewhere in the top-left side of the Form Header bar (no need for precision)
16. Type Brokerage Company and press Enter
17. While the label is still selected, in the Property Sheet, click the All tab. Click Name and type lblMainTitle
18. In the Controls section of the Ribbon, click the Label and click below the label you previously added in the Form Header section (no need for precision)
19. Type Agent Details and press Enter
20. While the label is still selected, in the Property Sheet, click Name and type lblAgentTitle

The Record Source of a Form or Report

The record source is a list that posseses the values that will be displayed by/in the controls of a form or report. The primiary type of record source of a form or report is a table. Therefore, after creating a table and saving it, you can use it as the source of data for a form or report. To do this visually, in the Properties window of the form or report, specify the Record Source property. To programmatically support this, the Form class is equipped with a property named RecordSource. To programmatically specify a record source, assign the name of a table, as a string, to this property. Here is an example:

Private Sub cmdSetRecordSource_Click()
    Me.RecordSource = "Employees"
End Sub

If you had programmatically created the form and you plan to use it for data, access its RecordSource property and assign the name of the desired table to it. Here is an example:

Private Sub cmdCreateForm_Click()
    Dim frmEmployees As Form
    
    Rem Create the form
    Set frmEmployees = CreateForm
    
    frmEmployees.RecordSource = "Employees"
End Sub

Practical Learning: Specifying the Record Source of a Form

1. Click the Properties button of the form
2. In the All tab of the Property Sheet, click Record Source, then click the arrow of its combo box and select Agents

The Control Source

Many controls are made to request or display a value. In some other controls, the user must make a selection. A control source is a characteristics that specifies whether the values of a control are tied to a column of a table. Normally, after creating a table and setting it as the record source of a form (or report), you can use one of the fields of the table to provide the value(s) of the control. This characteristic is controlled by a property named Control Source.

To manually set the control source of a control, access its Property Sheet. In the All or the Data tab, click the arrow of the Control Source combo box and select the desired name of the column from the table.

If you are programmatically creating a new control using the Application.CreateObject() method, to specify the table column that holds the values of the control, pass a fifth argument as the name of the field. Here is an example:

Private Sub cmdCreateControl_Click()
     CreateControl "Customers", _
                   AcControlType.acTextBox, _
                   acDetail, _
                   "Customers", _
                   "Last Name", _
                   840, 300
End Sub

To programmatically specify a control source, assign the name of the field to the control. Here is an example:

Private Sub cmdRecordSource_Click()
    Me.RecordSource = "Cars"
    
    Me.txtTagNumber.ControlSource = "TagNumber"
End Sub

Practical Learning: Specifying a Control Source

1. On the form, click the first or top text box
2. In the All tab of the Property Sheet, click Control Source, then click the arrow of its combo box and select AgentCode
3. On the form, click the second text box
4. In the Property Sheet, click Control Source and type f. Notice that FirstName was selected
5. On the form, click the third text box
6. In the Property Sheet, keep double-clicking Control Source until LastName is selected
7. On the form, click the fourth text box
8. In the Property Sheet, click Control Source and type t
9. On the form, click the last text box
10. In the Property Sheet, click Control Source and type p

The Names of Columns and Controls

If you generate a form or report that is bound to a table or query, each control added to the container has a name that is the same as the column to which it is bound. If you add an unbound text box or control to a form or report, it receives a default name. Whether a control on a form or report is bound or not to a column of a table, the name of that control does not have anything to do with that of a column. This means that you can easily set or change the name of a control without changing its Control Source.

To specify the name of a control on a form or report:

• Access its Property Sheet. In the Other or the All tab, click Name and edit its value
• In its Properties window, click (Name) and edit its value

To do this programmatically, assign the name of the column to the name of the control. Here are two examples:

Private Sub cmdRecordSource_Click()
    Me.RecordSource = "CleaningOrders"
    Me.txtPhoneNumber.ControlSource = "CustomerPhone"
    Me.txtCustomerName.ControlSource = "CustomerName"
End Sub

Practical Learning: Changing the name of a Control

1. On the form, click the second text box
2. In the All tab of the Property Sheet, click Name and type txtFirstName
3. Close the form
4. When asked whether you want to save, click Yes

The Field List

Microsoft Access provides various tools to add controls to a form or report. One technique is to use the Field List.

Practical Learning: Using the Field List

1. On the Ribbon, click Create and, in the Forms section, click Form Design
2. Click the Properties button of the form
3. In the Property Sheet, click Record Source and type t (Transation should be selected)
4. On the Ribbon, click Design if necessary.
   In the Tools section, click Add Existing Fields and make sure it is highlighted
5. In the Field List, click TransactionID
6. Press and hold Shift
7. Click PaymentStatus
8. Release Shift
9. Drag the selection and drop it on the form (no need for precision)
10. Right-click an unoccupied area of the form and click Form Header/Footer
11. Close the form
12. When asked whether you want to save, click Yes
13. Set the form name as Transactions and click OK

Tab Ordering the Controls

When navigating among the controls on a form, the user can press Tab to move from one control to another. To let you control the sequence of controls navigation, Microsoft Access provides the Tab Order dialog box. To access it, first display the form in the Design View:

• Right-click the form and click Tab Order...
• On the Ribbon, click Design. In the Tools section, click the Tab Order button

This would open the Tab Order dialog box:

To arrange the order of items, you can click the Auto Order button. To manually arrange the items, click a row header. Then click it again and hold the mouse down. Drag up or down as you need.

Practical Learning: Ending the Lesson

• Close Microsoft Access