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







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



These statements can be associated with any database object as:


Data1.Database.Execute "DROP TABLE PData”


Type 2: Selecting Records from Tables







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







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 FROM tables WHERE criteria;"

This allows performing delete operation on database.

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



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.




Figure 1: Properties of first command (NameDetails)




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.


Figure 3: Data Report Format


Private Sub cmdGo_Click()

      Unload DataEnvironment1

      DataEnvironment1.NameDetails (txtSearch.Text)


      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.




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.





Figure 5: New Project Dialog Box





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


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


End If

If Val(txtMM.Text) > 12 Or Val(txtMM.Text) < 1 Then


End If


mm = Val(txtMM.Text)

dd = Val(txtDD.Text)

If mm = 2 And dd > 29 Then


End If


If mm = 4 Or mm = 6 Or mm = 9 Or mm = 11 Then

    If dd > 30 Then


    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


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


    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.




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





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




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


    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.


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



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.