Visual Basic Demystified - Reports and ActiveX Controls

When we store data, we sometimes like to have its report in printed format, with the layout we desire. This can be achieved using Data Environment. At times, some modules made for one application fits well into other applications also. In such instances, we can copy the code from one application to another or create special controls known as ActiveX Controls.

SQL (Structured Query Language)

We have been using SQL since we understood the concepts of database management. SQL (pronounced as sequel) is the standard language of Relational Database Management Systems (RDBMS), as decided by the American National Standards Institute (ANSI). 

Let us quickly review the different commands of SQL language.

Type 1: Creating and deleting tables

These statements can be associated with any database object as,

Data1.Database.Execute "DROP TABLE PData”

Type 2: Selecting records from tables

This command can be linked with database object with either RecordSource Property or ResultSet Property as,

Data1.RecordSource = "SELECT * FROM PData”

Type 3: Executing commands related to tables

These statements can be associated with any database object as,

Data1.Database.Execute "DELETE FROM PData WHERE Name = ‘” & txtName.text & “’;”

Data Environment and Reports

Data Environment is a platform on the basis of which the report can be made. It provides the information to be displayed in the report. In other words, Data Environment is the backbone to form Data Report. Thus, we first need to configure Data Environment as per the information required by us and thereafter work on the Data Report.

Data Environment

We first target at adding a Data Environment and Data Report in our project. To do this, we click the right button in the Project Explorer Window and add them.

Before creating a report, we need to link a Data Environment (that provides a link to the database) with a Data Report. Consider that we are linking a personal database that has Name, Mobile and Address as fields.

In Data Environment, we first add a connection (Right button Click) and then connect a database as we did in ADO. Thereafter, we create a new command in that connection and provide the settings as shown in figure 1.

            Fig. 1: Properties of first command (NameDetails)




In figure 1, we were provided with SQL Statement

SELECT * FROM PData WHERE (Name = ?)

Here, PData is the Table Name and Name is one of the fields. The “?” is an external parameter that will be searched for in the Table Records and only those records matching with Name. The properties of the parameters are set from the Parameter Tab, as shown in figure 2.

Data Report and Linking

After this, we open Data Report and link the fields of Database defined in Connection1, as shown in figure 3. To bring the fields on Data Report, we first cascade the windows and then Drag (From Data Environment) and Drop them on Data Report. 

We can add different text labels in the Report Header, Page Header, Page Footer and Report Footer. Resizing these sections as per space desired makes the result compact and appear professional.

When this report is executed, it does not display any record though the database may have it. This is because the value of the parameter defined in NameDetails is undefined. Let us now see how to tackle this situation.

We create a new form that allows the user to enter the name, which is passed as an argument to Data Environment. This argument is accepted as a parameter for NameDetails and, accordingly, the report is generated. See figure 4.

Private Sub cmdGo_Click()
      Unload DataEnvironment1
      DataEnvironment1.NameDetails (txtSearch.Text)
      DataReport1.Show
      Unload Me
End Sub

Here, value of txtSearch is the name, whose records we wish to see. In case we wish to see all the records, we either change the SQL statement to

Select * from PData

Or select Table as Database Object and PData as Object Name.

We can provide advanced functions like Addition, Average and Multiplication of the fields (Of the Integer or Long type) by using (RptFunction) from the Tool Box.


ActiveX Controls

ActiveX originated with the aim of designing components for Internet applications. These controls, like other Visual Basic controls, can support properties, methods and events. An ActiveX control can be built as either a stand-alone control or with a dependence on other existing modules (or ActiveX Controls). The ActiveX Controls when compiled have the extension (.ocx).

These controls can be re-used in other programs directly by adding the controls through Project Menu à Components.

Programming ActiveX Control

Select File à New Project à  ActiveX Control as shown in figure 5. We change the name of the project to DateControl1. The new form that appears in front of us is actually a control file with .ctl extension. 

Fig. 5: New Project Dialog Box

We place three TextBoxes with their names as txtDD, txtMM, and txtYY as shown in figure 6.



Let us code DateControl1. Refer code 1.

 Code 1

Dim mm, dd, yy As Integer
Private Sub txtMM_GotFocus()
If Val(txtDD.Text) > 31 Or Val(txtDD.Text) < 1 Then
txtDD.SetFocus
End If
End Sub

Code 1 validates the date entered by the user in the first TextBox (txtDD), while code 2 checks the validity of the month entered by the user in the second TextBox (txtMM).

Code 2

Private Sub txtYY_GotFocus()
If Val(txtDD.Text) > 31 Or Val(txtDD.Text) < 1 Then
    txtDD.SetFocus
End If
If Val(txtMM.Text) > 12 Or Val(txtMM.Text) < 1 Then
    txtMM.SetFocus
End If

mm = Val(txtMM.Text)
dd = Val(txtDD.Text)
If mm = 2 And dd > 29 Then
    txtDD.SetFocus
End If

If mm = 4 Or mm = 6 Or mm = 9 Or mm = 11 Then
    If dd > 30 Then
        txtMM.SetFocus
    End If
End If
End Sub

However, there is one more condition that needs to be checked, which is whether the year entered is a leap year and the validity of date entered. Code 3 does this.

Code 3

Private Sub txtYY_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
    txtYY_LostFocus
End If
End Sub

Private Sub txtYY_LostFocus()
yy = Val(txtYY.Text)
If yy Mod 4 <> 0 Then    'not leap year
    If mm = 2 And dd > 28 Then
        txtDD.SetFocus
    End If
End If
End Sub

Just as in forms, Form_Load event executes at the beginning of any form, in this case UserControl_Initialize is executed.

Private Sub UserControl_Initialize()
txtDD.Text = Day(Date$)
txtMM.Text = Month(Date$)
txtYY.Text = Year(Date$)
End Sub

Compiling and Registering the Control

Before compiling, let us test our control. To test, we just execute the control as we run the program. A Dialog Box asks for the Start Component and as we click on OK, the control opens in Internet Explorer as shown in figure 7.

Let us compile it so that we can use this in other projects.

Select File -> Make DateControl1.ocx and save it in C: drive.

To use this ActiveX control in Windows, we need to register it with Windows, which is done by regsvr32.exe utility provided with Microsoft Windows.

C:\>regsvr32 c:\datecontrol1.exe

Since, we have registered this DateControl1, a CLSID is assigned to this control and now this control shall be listed in the Project -> Components

Using Control in Other Projects

Let us remove this project and start a new project afresh. We include DateControl1 ActiveX Control through Project -> Components. Refer figure 8.



An icon like appears in the ToolBox. We paste this control on the form the way we used other controls (ADODC and RDO).

Setting Properties

A control is not much useful unless we are able to change its properties and access its values. The values that can be accessed can be defined as Properties. Let us understand the procedure to do the same.

Select Tools à  Add Procedure and do as shown in figure 9.

Thereafter, we provide two different pieces of code. Check out code 4.

Code 4
Dim minvalue as Integer
Public Property Get MinYearValue() As Variant
MinYearValue = minvalue ‘This retrieves the current value of                                    ‘MinYearValue
End Property

Public Property Let MinYearValue(ByVal vNewValue As Variant)
minvalue = vNewValue      ‘This assign the new value to the property
PropertyChanged "MinYearValue" ‘This makes the property persistent
End Property

ActiveX stores the properties in PropertyBag Object, so we need to store the values in the PropertyBag. Refer code 5.

Code 5

Private Sub UserControl_ReadProperties(PropBag As PropertyBag)
minvalue = PropBag.ReadProperty("MinYearValue")
‘To read store properties.
End Sub

Private Sub UserControl_WriteProperties(PropBag As PropertyBag)
PropBag.WriteProperty "MinYearValue", minvalue, 1950
‘To write properties.
End Sub

Private Sub txtYY_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
    txtYY_LostFocus
    yy = Val(txtYY.Text)
    If yy < minvalue Then
            ‘Here minvalue is value of MinYearValue
        MsgBox "Year less than that allowed!"
    End If
End If
End Sub

Similarly we visible in figure 9, we can add Events, Functions and can have their access.


Adding Events 

Let us add an event to check on the validity of the date. We keep the event’s name as OnClick. 

There are two concepts to be used in programming this event.

First: 
Suppose we wish to allow the flexibility of calling this Event when the user clicks on the control, then we write the piece of code as,

Private Sub UserControl_Click()
RaiseEvent OnClick
End Sub

Second: 
The code for this event needs to be written in the project in which this ActiveX is used. As a sample, we code it as,

Private Sub DateControl1_OnClick()
MsgBox "Event has Triggered!"
End Sub

This provides flexibility to the programmer to code the ActiveX Control dynamically through the project in which it is called.

With this, we hope that the readers would have received a larger overview of Programming in Visual Basic. In our next articles, we shall be covering API and Multimedia Programming as well as Application Designing.