1. Start Microsoft Access
2. From the resources that accompany these lessons, open the KoloBank1 database
3. In the Navigation Pane, right-click the Customers form and click Design View

As mentioned already, to design a form or report, you typically click the desired controls in the Controls section of the Ribbon and click the form or report. To support the ability to 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 would 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		Subform
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

This code asks Microsoft Access to create a text box and position it on a form named Central.

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 available values are:

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.

The fourth argument to this method 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 "".

If you are creating a form or report that is not tied to a table or query, you can pass the fifth argument as an empty string. If you are creating a form or report whose controls would be linked to columns on a table or query, pass the fifth argument as the column that will be linked to the control you are creating. For example, suppose you had created a table named Customers that has a column named Last Name and suppose you are programmatically creating a text box whose values would come from the Last Name column of the Customers table. To tie the control to that column, you would pass the fifth argument as a string whose value is Last Name. This would be done as follows:

Private Sub cmdCreateControl_Click()
     CreateControl "Customers", acTextBox, acDetail, "Customers", "Last Name"
End Sub

The sixth and the seventh arguments specify the location of the control. The values of these arguments are integers. The eighth and the ninth arguments specify the dimensions of the new control. If you omit these two arguments, their default values would be used. Here is an example:

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

After creating the control, you may want to do something else on it, such as assigning a new value to it or involving it in a calculation. To do this, you must get a reference to the control when creating it. To do this, first declare a variable of type Control. Use the Set operator to assign the return value of the Application.CreateObject() method. This time, call the method as a function member using parentheses. Once you have created the control, you can use it. 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 of how you would do it:

Private Sub cmdCreateControl_Click()
    Dim ctlLastName As Control

    Set ctlLastName = CreateControl("Customers1", _
                                    AcControlType.acTextBox, _
                                    acDetail, _
                                    "Customers1", _
                                    "Last Name", _
                                    840, 300)

    . . . Use the control here

    Set ctlLastName = Nothing
End Sub

An application is usually made of various and different objects. Still, just like properties, there are some events that many types of objects can fire. For example, many objects accept that you use the mouse with them. Some other objects allow the keyboard.

To interact with the computer, one of the most usually performed actions is to click. The mouse is equipped with two buttons. The most clicked button is the left one. Because the action simply consists of clicking, when the user presses this button, a simple event, called On Click is sent or fired. When the user presses the (left) button on the mouse, the mouse pointer is usually on a Windows control. Based on this, the control that is clicked "owns" the event and must manage it. Therefore, no detailed information is provided as part of the event. The operating system believes that the control that fired the event knows what to do and should take care of everything. For this reason, whenever you decide to code an On Click event, you should make sure you know what control sent or fired the event. This is (one of) the most common events of Windows controls.

1. On the form, right-click the Move Record button and click Build Event...
2. In the Choose Builder dialog box, double-click Code Builder
3. Implement the event as follows:

   Private Sub cmdMoveRecord_Click()
       If MsgBox("It appears that this account has been closed." & vbCrLf & _
                 "Do you want to move it to the table of previous customers?", _
                 vbInformation Or vbYesNo, _
                 "Kolo Bank") = vbYes Then
           MsgBox "The record has been moved to the table of previous customers.", _
                  vbInformation Or vbYesNo, "Kolo Bank"
       End If
   End Sub

4. Return to Microsoft Access
5. Switch the form to Form View
6. Change the value of the Account Status field to Closed
7. Click the Move Record button
8. Click Yes on the message box
9. Click Yes again
10. Change the value of the Account Status field to Active
11. Right-click the title bar of the form and click Design View

To make the mouse more useful and to verify its role, another action can be performed on it. This consists of pressing the left button very fast twice. This action initiates an event known as On Dbl Click.

By default, the On Dbl Click event provides a simple structure to the programmer. You must know what control has been double-clicked and what you want to do. This means that the operating system relies on the control that "owns" the event.

Besides allowing the user to enter text into objects made for it, the keyboard is also used to perform various other actions such as accepting what a dialog box displays or dismissing it. When the user presses the keys on a keyboard, the object on which the characters are being 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 is so, the control fires an event named On Key Press. The syntax of this event is:

Private Sub txtFullName_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 use a conditional statement to find out what key the user had clicked.

To help you identify the key, check the value of the KeyAscii argument. Here is an example:

Private Sub txtFullName_KeyPress(KeyAscii As Integer)
    If KeyAscii = 50 Then
        MsgBox "You pressed 2"
    End If
End Sub

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 txtFullName_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 use a logical conjunction to find out what combination of keys the user pressed. Here is an example:

Private Sub txtFullName_KeyDown(KeyCode As Integer, Shift As Integer)    
    If KeyCode = 82 And Shift = 2 Then
        MsgBox "You pressed Ctrl + R"
    End If
End Sub

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

Private Sub txtFullName_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.

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 txtFirstName_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. In reality, 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. In reality, 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 txtFirstName_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

A Windows is primarily a class. It is equipped with properties that describe it, methods that allow it to perform actions, and events that allow it to take actions at appropriate times and/or to communicate with the other controls, the database, and the operating system. While Windows controls are different, they still share some characteristics, behaviors, and events. At this time, we will review what characteristics and behaviors are common to all or most controls.

While or after creating a control, you can either specify its location or change it. This is easily done when designing a form or report. The distance from the left border of the container to the left border of a control is represented by its Left property. Based on this, to programmatically move a control horizontally, assign a numeric value to its Left property. Here is an example:

Private Sub cmdMove_Click()
    cmdSubmit.Left = 450
End Sub

In the same way, the distance from the top border of a host to the top border of a control is its Top property.

The size of a control or a section of a container is a combination of the distance from its left to its right borders and from its top to its bottom borders. These dimensions are represented by the Height and the Width properties respectively.

To programmatically change the height of a control, assign a numeric value to its Height property. In the same way, to heighten a control, assign the desired numeric value to its Width property. Here is an example:

Private Sub cmdResize_Click()
    cmdSubmit.Width = 2450
End Sub

Normally, a control is meant to be displayed to the user. At times, you will want to hide a control. If you create a form but specify that it should display in Datasheet View, its controls in the Form Header and the Form Footer sections are automatically hidden. In the other cases, to hide a control, access its Visible property and set it to False. Here is an example:

Private Sub cmdChangeProperty_Click()
    txtUserPassword.Visible = False
End Sub

To display a control, set its Visible property to True.

1. On the form, click the Move Record button and, using the Properties window, set its Visible property to No
2. On the form, click the Account Status combo box
3. In the Properties window, click Event and double-click After Update
4. Click the ellipsis button
5. Implement the event as follows:

   Private Sub AccountStatus_AfterUpdate()
       If AccountStatus = "Closed" Then
           cmdMoveRecord.Visible = True
       Else
           cmdMoveRecord.Visible = False
       End If
   End Sub

6. In the Object combo box, select Form
7. In the Procedure combo box, select Current
8. Implement the event as follows:

   Private Sub Form_Current()
       AccountStatus_AfterUpdate
   End Sub

9. Return to Microsoft Access
10. Switch the form to Form View
11. Change the value of the Account Status field to Closed and notice that the button appears
12. Navigate to the next record and notice that the button is hidden
13. Navigate to the next record and notice that the button appears
14. Close the form
15. When asked whether you want to save the form, click Yes

Most controls are meant to let the user select, specify, or change their value. To make this happen, the control is said to be enabled. If you want the control to only display its value without the user being able to change it, access its Enabled property, which is Boolean and whose default value is True. If you set this property to False, the user cannot do anything on this control. Here is an example:

Private Sub cmdChangeProperty_Click()
    txtEmployeeID.Enabled = False
End Sub

To enable a control, set its Enabled property to True.

When a control is on a form or report, you can delete it. You can also delete a group of controls in one step. To allow you to programmatically delete a control, the Application class is equipped with a method named DeleteControl. Its syntax is:

Sub Application.DeleteControl(ByVal FormName/ReportName As String, ByVal ControlName As String)

The first argument is the form or report on which the control is positioned. The second argument is the name of the control you want to delete.

After creating a table (or query) and setting it as the record source of a form (or report), or after creating a SQL statement to be used as a record source, you can use one of the fields of the table (or query) or statement to provide the value(s) of the control. This characteristic is controlled by property named ControlSource. To do this, 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

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 it is bound to. If you add an unbound text box or control to a form or report, it receives a default name that is cumulative. Whether a control on a form or report is bound or not to a column of a table or query, 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.

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

In this case, the CustomerPhone column of a table named CleaningOrders would be bound to a text box named txtPhoneNumber of the current table.

The focus is the visual aspect that indicates the control that would receive a change if the user types a new value. This also means that a control must receive focus before it can receive a value. A user usually gives focus to a control by clicking it or pressing Tab continuously until the control indicates that it has focus.

When the user clicks a control or attempts to give it focus using the keyboard or other means, just before the control receives focus, it fires an event named On Enter:

Private Sub Control_Enter()
    
End Sub

When the control actually receives focus, it fires an event named On Got Focus:

Private Sub Control_GotFocus()
    
End Sub

As you can see, these events don't carry much information, except to let you know that they are currently getting focus so you can take appropriate action.

To explicitly give focus to a control, you can call its SetFocus() method. Here is an example:

Private Sub Form_Load()
    NavigationButtons = False
    [Company Name].SetFocus
End Sub

Alternatively, to support this operation, the DoCmd object is equipped with a method named GoToControl. Its syntax is:

DoCmd.GoToControl(ByVal ControlName As String)

This method takes the name of a control as argument. When this method is called, the database engine gives focus to the control passed as argument.

After using a control, the user can click another control or use the keyboard to move to another control. When this happens, just before the control loses focus, it fires an event named On Exit. It appears as follows:

Private Sub Control_Exit(Cancel As Integer)

End Sub

This event carries one piece of information. The Cancel argument specifies whether the event should be kept (if set to False) or canceled (if set to True). When the control actually loses focus, it fires an event named On Lost Focus:

Private Sub Control_LostFocus()

End Sub