Visual Basic - Reports and ActiveX Controls

10 minute read

When we store data, we sometimes like to have its report in printed format with the layout as desired by us. This can be achieved by using Data Environment.  And sometimes, some modules made for one applications fit well into other applications.  This task can be done be copying the same piece of code from one application to another or by creating special controls known as ActiveX Controls.

SQL (Structured Query Language)

 

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

 

Let’s quickly review the different commands of SQL Language.

Type 1: Creating and Deleting Tables

 

Query

Forms

Interpretation

Example

Create
Table

CREATE TABLE tablename

(Field1Name Field1Type, Field2Name Field2Type, etc.);

This command creates a new table with tablename

CREATE TABLE PData (Name TEXT (32), Mobile LONG, Address TEXT(40));

Delete Table

DROP TABLE tablename;"

This command deletes the entire table

DROP TABLE PData

 

These statements can be associated with any database object as:

 

Data1.Database.Execute "DROP TABLE PData”

 

Type 2: Selecting Records from Tables

 

Query

Forms

Interpretation

Example

Select

SELECT fields FROM tables WHERE criteria;

Fields is a comma-delimited list of fields to return, tables is list of tables where to find the fields and criteria is an expression that records must satisfy.

Select Name, Mobile from PData where Name = ‘Mr.ABC’;

This selects only those records where the Amount lies between 100 and 200.

Select * from PData where amount between 100 and 200;

This selects only those records in which Code Starts with H, and thereafter arrange the records in ascending order of Name

Select * from PData where code like ‘H*’ order by Name asc;

 

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

 

Query

Forms

Interpretation

Example

Update

Update table SET field=newvalue WHERE criteria;

This allows performing update operation on database.

Update PData SET Name = ‘Mr. XYZ’, Mobile=’981112222’ where Name = ‘Mr. Abc’;

Delete

DELETE FROM tables WHERE criteria;"

This allows performing delete operation on database.

DELETE from PData where Name = ‘Mr. Abc’;

Insert

INSERT INTO

table (Field1Name, Field2Name, etc)

VALUES (Field1Value, Field2Value, etc);

This allows adding records in database.

Insert INTO PData (Name, Mobile) VALUES (‘Mr. DEF’, ‘9888198822’);

 

These statements can be associated with any database object as:

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

Data Environment & Reports

A 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 a back bone to form Data Report.  This, 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.

 

clip_image002

 

Figure 1: Properties of first command (NameDetails)

 

clip_image004

 

Figure 2: Setting properties of Parameter.

 

In Figure 1, we 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 the 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 records though the database may have it. This is because the value of the parameter defined in NameDetails is undefined.  Let us 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 report is generated.

clip_image006

Figure 3: Data Report Format

 

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.

 

clip_image008

 

Figure 4: Data Report at the time of Execution

 

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

 

ActiveX  Controls

 

 

ActiveX originated with an 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 a stand-alone control, or can be built 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 these 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.

 

We place three Text Boxes with their names as txtDD, txtMM, and txtYY as shown in Figure 6.

 

 

clip_image012

 

Figure 5: New Project Dialog Box

 

 

clip_image014

 

Figure 6: DateControl

 

Let’s code this DateControl1.

 

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

 

The above code checks for the validity of date entered by the user in the first TextBox (txtDD), while the code below checks the validity of month entered by the user in the second TextBox (txtMM).

 

 

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 that is whether the year entered is a leap year and the validity of date entered. The code written below does the same.

 

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 & Registering the Control

 

Before compiling, let’s 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.

 

clip_image016

 

Figure 7: DateControl Testing

 

Let’s 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’s remove this project and start a new project afresh.  We include DateControl1 ActiveX Control through Project > Components

 

 

clip_image018

 

Figure 8: Adding DateControl1 ActiveX Control

 

An icon like clip_image020  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 accessing its values.  The values that can be accessed can be defined as Properties. Let’s 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 as:

 

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

 

clip_image022

 

Figure 9: Adding Property

 

The ActiveX stores the properties in PropertyBag Object, so we need to store the values in the PropertyBag.

 

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’s add an event to check validity of date. We keep event’s name as OnClick. 

 

There are two concepts to be used in programming of 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.

Note: This series was first published in DeveloperIQ and was co-authored by Puneet Ghanshani with Pranjali Bakeri in 2005-2006.

Updated: