Visual Basic - Reports and ActiveX Controls
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 |
(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 |
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 |
This allows performing delete operation on database. |
DELETE from PData where Name = ‘Mr. Abc’; |
|
|
Insert |
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)
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.
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 
(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
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.
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 
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
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.
