Microsoft Access Database Development With VBA

Introduction to Fields and Windows Controls

 

Introduction to Fields

 

Overview

A typical database application is made of forms and reports. These objects are equipped with Windows controls that allow a user to interact with the computer. In fact, the most common way people use the values of a database is through the Windows controls on forms and reports. This means that, when creating the application, you add forms and reports, then populate them with the necessary objects. You then use the operations of the DML to make values available to users.

Practical LearningPractical Learning: Introduction to Fields

  1. Start Microsoft Access
  2. Open the KoloBank1 database from the previous lesson
  3. In the Navigation Pane, right-click the Customers form and click Design View
  4. On the right side of the Account Status combo box, add a Button
  5. Using the Properties window, change its characteristics as follows:
    Name: cmdMoveRecord
    Caption: Move Account to Previous Customers

     
    Kolo Bank: Customers

Control Creation

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 Attachment
acBoundObjectFrame Bound Object Frame Bound Object Frame
acCheckBox Check Box Check box
acComboBox List Box Combo box
acCommandButton Button Command button
acCustomControl   ActiveX control
acImage Image
acLabel Label Label
acLine Line Line
acListBox List Box List box
acNavigationControl List Box  Navigation Control 
acObjectFrame Unbound Object Frame  Unbound Object Frame
acOptionButton Option Button Option button
acOptionGroup Option group Option group
acPage   Page
acPageBreak Page Break Page break
acRectangle Rectangle Rectangle
acSubform Sub-Form Subform 
acTabCtl Tab control Tab control
acTextBox Text Box Text box
acToggleButton  Toggle button Toggle button
acWebBrowser Web Browser Control  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:

AcSection Member Section Name
acDetail Detail
acHeader Header 
acFooter Footer
acPageHeader Page Header
acPageFooter Page Footer
acGroupLevel1Header  Group Level 1 Header on a Report
acGroupLevel1Footer  Group Level 1 Footer on a Report 
acGroupLevel2Header  Group Level 2 Header on a Report
acGroupLevel2Footer  Group Level 2 Footer on a Report

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

Common Events of Windows Controls

 

Click

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.

Practical LearningPractical Learning: Using the Click Event

  1. On the form, right-click the Transfer Account to Previous Customers 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

The Double-Click Event

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.

Introduction Keyboard Events

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.

Pressing a Key

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

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 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

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 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.

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 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

Primary Characteristics of Controls

 

Introduction

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.

Control Location

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.

A Control's Size

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

The Visibility of a Control

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.

Practical LearningPractical Learning: Using the Click Event

  1. On the form, click the Transfer Account to Previous Customers button.
    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 Ellipsis
  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. Close the form
  11. When asked whether you want to save the form, click Yes

The Availability of a Control

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.

Deleting a Control

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.

The Control Source

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

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 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 of a Control

 

Getting Focus

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.

Setting Focus

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.

Losing Focus

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
 
 
 

The Text and Font of a Control

 

TThe Text Alignment of a Control

By default, text-based controls align their strings to the left. To support the allignment of text, controls are equipped with a property named TextAlign. Its values can be:

Name Value Description
General 0 This is the default value. It means you let Microsoft Access decides, based on the type of value of the control.
If the control is configured to display text, its string would be aligned to the left. If the control is configured to display numbers, dates, or times, its value would be aligned to the right.
Left 1 The values would be aligned to the left
Center 2 The values would be aligned to the center
Right 3 The values would be aligned to the right
Distribute 4 The content would be evenly distributed

Practical LearningPractical Learning: Introducing Control's Text

  1. From the resources that accompany these lessons, open the Georgetown Dry Cleaning Services2 database
  2. In the Navigation Pane, double-click the CleaningOrders form to open it
    Georgetown Dry Cleaning Services
    
    
  3. Navigate to the third record
    Georgetown Dry Cleaning Services
    
    
  4. Navigate to the fourth record
    Georgetown Dry Cleaning Services
    
    
  5. Right-click the form's title bar and click Design View

The Font Name of a Control

The font is the most common object that controls how the words on an object appear. To support fonts, each control is equipped with various properties. For example, to specify the font used on a control, access its FontName property, which represents the name of the font you want to use. Then assign the desired font to it. Here is an example:

Private Sub cmdChangeProperty_Click()
    txtLastName.FontName = "Rockwell"
End Sub

The Font Style Applied to a Control

Besides the font name, you can specify the style. This controls whether the characters appear in bold, in italic, or underlined. The properties used to control these aspects are FontItalic and FontUnderline, respectively. Each of these properties is Boolean. If you set the property to True, its corresponding style applies. Here are examples:

Private Sub cmdChangeProperty_Click()
    txtLastName.FontName = "Rockwell"
    txtLastName.FontItalic = True
    txtLastName.FontUnderline = True
End Sub

The Font Weight of a Control

To make the characters of a control appear thick, you can apply a weight to it. The primary property you can use is called FontBold. This is a Boolean property. If you assign the True value to it, the characters of the value of a control appear bold. Here is an example:

Private Sub cmdChangeProperty_Click()
    txtLastName.FontName = "Rockwell"
    txtLastName.FontBold = True
End Sub

If you want, you can make the boldness appear with more style. To do this, access the FontWeight property of the control and assign one of the following values:

Constant Description
100 Thin
200 Extra Light
300 Light
400 (Default) Normal
500 Medium
600 Semi-bold
700 Bold
800 Extra Bold
900 Heavy
 

The Font Size Applied to a Control

Beside the name and the style, you may want to control the size of characters of a control. To support this, their classes are equipped with a property named FontSize. After accessing this property, assign the desired integer to it. Here is an example:

Private Sub cmdChangeProperty_Click()
    txtLastName.FontName = "Rockwell"
    txtLastName.FontBold = True
    txtLastName.FontItalic = True
    txtLastName.FontUnderline = True
    txtLastName.FontSize = 12
End Sub

The Font Color of a Control

To enhance the appearance of characters on a control, you can use a particular color of your choice. To support this, the controls are equipped with a property named ForeColor. To specify the color for the characters, assign either a long constant or a color to the RGB() function to this property. Here is an example:

Private Sub cmdChangeProperty_Click()
    ' Use a red color
    txtLastName.ForeColor = RGB(255, 0, 0)
End Sub

Practical LearningPractical Learning: Using the Font Color of a Control

  1. Double-click the button at the intersection of the rulers.
    In the Properties window, click Event
  2. Double-click On Current
  3. Click the ellipsis button Ellipsis
  4. Implement the event as follows:
    Private Sub Form_Current()
        If OrderStatus = "Processing Cleaning Order" Then
            OrderStatus.ForeColor = RGB(255, 255, 0)
        ElseIf OrderStatus = "Ready For Pick Up" Then
            OrderStatus.ForeColor = RGB(250, 250, 10)
        Else
            Rem If OrderStatus = "Finalized (Picked Up)" Then
            OrderStatus.ForeColor = vbBlack
        End If
    End Sub
    

Aesthetic Characteristics of Database Objects

 

The Background Color of a Control

By default, the background of text-based controls appears white. If you want, you can change it. To do this, access the BackColor property of a control and assign either a constant integer or a call to the RGB() function to it. Here is an example:

Private Sub cmdChangeProperty_Click()
    txtLastName.BackColor = 795734
End Sub

Practical LearningPractical Learning: Using the Background Color of a Control

  1. Change the event as follows:
    Private Sub Form_Current()
        If OrderStatus = "Processing Cleaning Order" Then
            OrderStatus.BackColor = RGB(128, 0, 0)
            OrderStatus.ForeColor = RGB(255, 255, 0)
        ElseIf OrderStatus = "Ready For Pick Up" Then
            OrderStatus.BackColor = RGB(50, 150, 100)
            OrderStatus.ForeColor = RGB(250, 250, 10)
        Else
            Rem If OrderStatus = "Finalized (Picked Up)" Then
            OrderStatus.BackColor = vbWhite
            OrderStatus.ForeColor = vbBlack
        End If
    End Sub
  2. Return to Microsoft Access
  3. Switch the form to Form View
  4. Display the first record
    Georgetown Dry Cleaning Services
    
    
  5. Navigate to the third record
    Georgetown Dry Cleaning Services
    
    
  6. Navigate to the fourth record
    Georgetown Dry Cleaning Services
    
    
  7. Close the form
  8. When asked whether you want to save, click Yes

The Border Color of a Control

During design, Microsoft Access applies a default border to the controls you add. If you want, you can specify a specific color to a control or to the controls on a form or report. To support this, each control is equipped with a property named BorderColor. To control it, assign the desired color to this property. Here is an example:

Private Sub cmdChangeProperty_Click()
    txtLastName.BorderColor = 795734
End Sub

Special Effects

You can control the borders of a control to look a certain way. This the job of special effects:

Special Effects

To apply special effects to a control, access its SpecialEffects property and assign one of the following values:

Name Value Constant Description
Flat acEffectNormal 0 The control is flat. The borders use either a default color specified by the operating system or a color specified in Design view
Raised acEffectRaised  1 The control's borders appear raised. The left and top borders are highlighted. The right and bottom borders appear as its shadow
Sunken acEffectSunken  2 The control's borders appear sunken. The left and top borders appear as its shadow. The right and bottom borders are highlighted
Etched acEffectEtched 3 The control is flat. It uses a sunken line on its borders
Shadowed
acEffectShadow
4 The control displays a shadow on its right and bottom borders
Chiseled acEffectChisel 5 The control displays a sunken line on its bottom border

You can assign the value or the corresponding constant integer. Here is an example:

Private Sub cmdChangeProperty_Click()
    txtLastName.SpecialEffect = acEffectEtched
End Sub

The Border Style

By default, the border of a control appears as a line. You can make it appear with a style. To do this, access its BorderStyle property and assign one of the following values:

Name Constant Description
Transparent 0 The control shows no border
Solid 1 The control's borders display a solid line
Dashes 2 The control's borders display dashed lines
Short dashes 3 The control's borders display short dashed lines
Dots 4 The control's borders display dotted lines
Sparse dots 5 The control's borders display spaced dots on its lines
Dash dot 6 The control's borders display with a dash-dot combination
Dash dot dot 7 The control's borders display with a dash-dot-dot combination
Double solid 8 The control's borders display a double solid line
     

Text-Based Controls: The Label

 

Introduction

A label is a static control that displays fixed text to the user. The user cannot change the text of a label but can read it. A label can be used by itself to display text. In many other cases, a label is used to display text about anything appropriate.

To add a label to a form or report, display the form or report in Design View. In the Controls section of the Design tab of the Ribbon, click Label Label and click the form or report. You must also type the title of the label (if you don't and click somewhere else, the label would disappear). The label control is an object of type Label.

To programmatically create a label, call the CreateConotrol() function and pass the ControlType as acLabel. The first argument is the name of the form or report on which the label will be positioned. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlFirstName As Control
    
    Set ctlFirstName = CreateControl("Exercise", _
                                      AcControlType.acLabel)

    Set ctlFirstName = Nothing
End Sub

Of course, the third argument is the section of the form or report where the label will be positioned. You can pass the fourth argument as the name of the form or report on which the label will be positioned. That is, the first and the fourth argument can be the same. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlFirstName As Control
    
    Set ctlFirstName = CreateControl("Exercise", _
                                     AcControlType.acLabel, _
                                     AcSection.acDetail, _
                                     "Exercise")

    Set ctlFirstName = Nothing
End Sub

Properties of a Label

A label cannot be used directly for data entry. That is, you cannot link a label to a column of a table. A label is only used to display static text that cannot be changed. As a consequence, if you are visually creating a label, you cannot specify its Control Source in the Properties window. If you are programmatically creating the label, pass the fifth argument as an empty string.

Probably the most important and the most obvious characteristic of a label is the text it displays. The text is the label's Caption. If you click the Label on the Ribbon and click on the form or report, you must first define its caption. If a label already has a caption, there are various ways you can edit it. For example, can click it and click again to put it into edit mode, then edit its string. You can also double-click it to access its Properties window and change its Caption property.

If you are programmatically creating the label, to specify its caption, access the control's Caption property and assign the desired string to it. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim ctlFirstName As Control
    
    Set ctlFirstName = CreateControl("Exercise", _
                                     AcControlType.acLabel, _
                                     AcSection.acDetail, _
                                     "Exercise")
    
    ctlFirstName.Caption = "First Name:"

    Set ctlFirstName = Nothing
End Sub

The appearance of a label is visibly controlled by its font characteristics. The Font name, also called its face, is the name of the font as defined by the operating system. There are various ways you can define the font characteristics of a control like the label. After selecting it while the form or report is in Design View, on the Ribbon, you can click Home or Design, then use the buttons in the Font section to specify the font characteristics. You can also access the Properties window of the label and modify the desired font properties.

The position of a label is controlled by its Top and Left properties. The Top property defines the measure from the top left corner of the section where the label is positioned, to the top left corner of the label itself. There are two main ways you can set the position of a label. On the Properties window, you can change the values of the Top and Left properties. On the other hand, you can place your mouse on the top left corner of the label until the mouse pointer turns into a pointing finger. Then click and drag in the desired direction.

If you are programmatically creating the label, to specify its size, pass a sixth argument as its left position and/or a seventh argument as its top position. Here are examples:

Private Sub cmdCreateControl_Click()
    Dim ctlFirstName As Control
    
    Set ctlFirstName = CreateControl("Fundamentals", _
                                     AcControlType.acLabel, _
                                     AcSection.acDetail, _
                                     "Exercise", _
                                     "", _
                                     320, 260)
    
    ctlFirstName.Caption = "First Name:"

    Set ctlFirstName = Nothing
End Sub

The size of a label is controlled by its Width and Height properties. Although the dimensions are closely related to the font characteristics, they can be independently defined. There are two main ways you can resize a label, which is equivalent to changing its dimensions. To set your own dimensions, in the Format tab of the Properties window of the label, change the values of the Width and Height properties. Unless you plan to show the background color of a label, probably the best way to resize a label is to make it adjust to the dimensions depending on the font size and the total characters width. To do this, position the mouse on one of the label's handle and double-click. The label's width and height would be set to accommodate its caption.

If you are programmatically creating the label, to specify its size, pass an 8th argument as its width and/or a ninth argument as its height. Here are examples:

Private Sub cmdCreateControl_Click()
    Dim ctlFirstName As Control
    
    Set ctlFirstName = CreateControl("Fundamentals", _
                                    AcControlType.acLabel, _
                                    AcSection.acDetail, _
                                    "Exercise", _
                                    "", _
                                    320, 260, 1200, 260)

    ctlFirstName.Caption = "First Name:"

    Set ctlFirstName = Nothing
End Sub

Text-Based Controls: The Text Box

 

Introduction

A text box is a Windows control used to get or display text. At its most regular use, a text box serves as a place to fill out and provide information. You can also use it only to display text without allowing the user to change its content.

Like most other controls, the role of an edit box is not obvious at first glance; that is why it should be accompanied by a label that defines its purpose. From the user's standpoint, a text box is named after the label closer to it. Such a label is usually on the left or the top side of the corresponding edit box.

Creating a Text Box

To add a text box to your project, from the Controls section of the Ribbon, you can click the Text Box control Text Box and click a section of the form or report. Unless you have a good alternate reason, most of your text boxes will be placed in the Detail section. Some text boxes used in expressions can be placed in another section. By default, placing a text box on the form or report also adds a corresponding label to its left.

To programmatically create a text box, call the CreateConotrol() function and pass the ControlType as acTextBox. The first argument is the name of the form or report on which the text box will be positioned. The third argument specifies the section where to position the text box. Here is an example:

Private Sub cmdCreateControl_Click()
    Dim txtFirstName As Control
    
    Set txtFirstName = CreateControl("Exercise", _
                                     AcControlType.acTextBox, _
                                     AcSection.acDetail)

    Set txtFirstName = Nothing
End Sub

Properties of a Text Box

As mentioned already, a text box can be used to display text, just like a normal control in a Microsoft Windows application. In a database, a text box is typically used to display the value of a field of a table. To make this possible, after specifying the Record Source of its form or report and after visually adding a text box, set its Control Source to the desired field of the table. If you are programmatically creating the text box, to specify the field it must link to, pass the name of the table as the fourth argument and pass the name of the field as the fifth argument. This could be done as follows:

Private Sub cmdCreateControl_Click()
    Dim txtFirstName As Control
    
    Set txtFirstName = CreateControl("Exercise", _
                                     AcControlType.acTextBox, _
                                     AcSection.acDetail, _
                                     "Students", "FirstName")

    Set txtFirstName = Nothing
End Sub

If you are creating a text box that will not link to a field of a table, you can pass the fourth argument as either the same as the first argument or an empty string, and then pass the fifth argument also as an empty string.

Like every control on a form or report, the dimensions of the text box are controlled by its Width and Height properties. You can change these in the Properties window of the text box. You can also specify them if you are programmatically creating the control. Here are examples:

Private Sub cmdCreateControl_Click()
    Dim txtFirstName As Control
    
    Set txtFirstName = CreateControl("Exercise", _
                                     AcControlType.acTextBox, _
                                     AcSection.acDetail, _
                                     "Exercise", _
                                     "", 1600, 260)

    Set txtFirstName = Nothing
End Sub

The position of a text box is controlled by its Top and Left properties. You can change them in the Properties window of the text box. You can also specify them when programmatically creating the control. Here are examples:

Private Sub cmdCreateControl_Click()
    Dim txtFirstName As Control
    
    Set txtFirstName = CreateControl("Fundamentals", _
                                     AcControlType.acTextBox, _
                                     AcSection.acDetail, _
                                     "Fundamentals", _
                                     "", 1600, 260, 1760, 260)

    Set txtFirstName = Nothing
End Sub

To make a text box read-only, that is, if you don't want the user to enter text in an edit box, there are various alternatives. If you change the Enabled property from Yes to No, the text box would have a gray background and cannot receive focus. If you set the Locked property from No to Yes, the control would appear with a normal (white) background.

The Special Effects property of the text box is expanded as compared to that available on a label. Besides the ability to raise or sink a text box, you can give it a thick, etched, or shadow border.

 
 
   
 

Previous Copyright © 2002-2013 FunctionX, Inc. Next