Example Vb6 Database Program Using Excel by pwq12627


Example Vb6 Database Program Using Excel document sample

More Info

executiVe daShboard
in ViSual baSic
                                                                                                                                      sani the

   Veeranna V.K. and JanaKideVi S.J.

           igital dashboards are common
           in cars. These display a mul-
           titude of information to keep
the car running, such as the speed of
the car, fuel level, and battery-low and
hot-engine warning.
    How about an executive dashboard
that displays their company-specific
information on a desktop/laptop in the
form of graphics? Information like cur-
rent sales, revenue income, quarterly ex-
penses, and strength and daily reports
of the employees could be updated
regularly and seen on the dashboard.
    Just like car dashboards, executive
dashboards may vary in appearance,
depending on the needs of the execu-
tives for which they are designed. The
executive dashboards may provide:
    1. Access through a Web browser
    2. Graphs to present maximum
    3. More details upon clicking the
relevant graph or part of graph                           Screenshot 1: Four arbitary graphs to display the information
    4. Automatic updation with the lat-
est available data                                        to view the information in various                   ties as shown in the table.
    5. Print, save and sharing facility                   forms of graphical displays (such as                     Normally, ADO and MSChart con-
for the information displayed on the                      bar graphs, pie charts and line graphs)              trols are not available in the general
dashboard                                                 in two and three dimensions. Save,                   control toolbox. To add these controls
    Generally, dashboards are designed                    print and Web connection functions                   to the project, go to project → com-
using MS Excel or MS Word. Here we                        can be implemented. The information                  ponents and check the boxes against
describe a simple executive dashboard                     appears on the executive dashboard                   Microsoft ADO Data Control 6.0
designed in Visual Basic 6 (VB6) with-                    as shown in Screenshot 2. Note that                  (OLEDB) and Microsoft Chart Control
out using Excel or Word. The program                      the scale and unit are not mentioned                 6.0 (OLEDB) options. The symbols of
is tested in VB6 and Windows XP en-                       in these graphs. The User can include                these two controls appear in the gen-
vironments.                                               them while designing for specific ap-                eral control toolbox on the left-hand
    We’ve used the MS Chart ActiveX                       plication.                                           side of the screen. Place the controls on
control available in Visual Basic that                                                                         the form. By default, the SeriesColumn
has many of the features of graphs                        executive dashboard                                  property of chart control is set to ‘1.’
available in Excel. We selected four                      development                                          Don’t change this value, as it selects
arbitrary graphs to display the infor-                    Form design. Run Visual Basic and                    the first column for the plot in X-axis.
mation about an organisation (EFY)                        open a new project and form. Position
as shown in Screenshot 1. Below                           the form appropriately on the screen,                Program coding
each chart is a set of option buttons                     resize the controls and set their proper-            The program is simple and self-ex-

1 0 6 • M a r c h 2 0 1 0 • e l e c t ro n i c s f o r yo u                                                                       w w w. e f y M ag . co M
                                                                                               Private Sub updatefields(p As
                                                                                               Integer, q As Integer, r As Integer)
                                                                                               Dim k As Integer
                                                                                               While Adodc1.Recordset.EOF = False
                                                                                               k = (Adodc1.Recordset.Fields(p).
                                                                                               Value - Adodc1.Recordset.Fields(q)
                                                                                               If Sgn(k) = -1 Then
                                                                                               Adodc1.Recordset.Fields(r).Value =
                                                                                               Adodc1.Recordset.Fields(r + 1).
                                                                                               Value = 0
                                                                                               Adodc1.Recordset.Fields(r).Value = 0
                                                                                               Adodc1.Recordset.Fields(r + 1).Value
                                                                                               = Abs(k)
                                                                                               End If
                                                                                               End Sub

                                                                                                   The ‘prstaff’ field in ‘dash4’ table
                                                                                               is incremented to reach the total exist-
Screenshot 2: Program output of executive dashboard with arbitrary data                        ing staff, then it is decremented until
                                                                                               it reaches zero. Thus the graph cor-
planatory. Here, it is designed to                  Adodc1.Refresh                             responding to prstaff moves up and
display sales department, accounts                  updatefields 1, 2, 3                       down between totstaff and exstaff
department, editorial section and the               Set MSChart1(1).DataSource = Adodc1        graphs and updated every second to
organisation’s staff statistics. There              MSChart1(1).Stacking = False               show the effect of real-time process-
are four database tables: dash 1, dash                                                         ing. The real-time updated data of the
2, dash 3 and dash 4 containing the                 ‘INITILIZE EDITORIAL CHART                 present staff (prstaff) is indicated in the
records for sales, accounts, editorial              Set MSChart1(1).DataSource = Nothing       textbox control using the prstaff_up-
and staff statistics respectively. All              Adodc1.RecordSource = “DASH3”              date( ) subroutine:
these tables are in ‘efyexecutive.mdb’              Adodc1.Refresh
file created in MS Access application.              updatefields 1, 2, 3                       Private Sub PRSTAFF_UPDATE()
The MSChart controls can be con-                    Set MSChart1(2).DataSource = Adodc1        Adodc1.Refresh
nected to the database tables using                 MSChart1(2).Stacking = False               Select Case MaxBool
the code in the subroutine init_chart(                                                         Case False:
) given below:                                      ‘INITILIZE ORGANIZATION CHART              While Adodc1.Recordset.EOF = False
                                                    Set MSChart1(2).DataSource = Nothing       If Adodc1.Recordset.Fields(3).
Private Sub INIT_CHART()                            Adodc1.RecordSource = “DASH4”              Value < Adodc1.Recordset.Fields(2).
On Error Resume Next                                Adodc1.Refresh                             Value Then
‘ INITILIZE SALES CHART                             Set MSChart1(3).DataSource = Adodc1        Adodc1.Recordset.Fields(3).Value =
Set MSChart1(3).DataSource = Nothing                MSChart1(3).Stacking = False               Adodc1.Recordset.Fields(3).Value + 1
Adodc1.RecordSource = “DASH1”                       PRSTAFF_UPDATE                             M = M + 1
Adodc1.Refresh                                      End Sub                                    Text2.Text = M
Set MSChart1(0).DataSource = Adodc1                                                            End If
MSChart1(0).Stacking = False                            A few fields in tables ‘dash2’ and     Adodc1.Recordset.Update
                                                    ‘dash3’ require calculation. These are     Adodc1.Recordset.MoveNext
‘INITILIZE ACCOUNTS CHART                           calculated in the subroutine update-       Wend
Set MSChart1(0).DataSource = Nothing                fields( ):                                 ‘check on full staff
Adodc1.RecordSource = “DASH2”                                                                  If M = N Then

w w w. e f y M ag . co M                                                                  e l e c t ro n i c s f o r yo u • M a r c h 2 0 1 0 • 1 0 7
                                                                                                            End Sub
                             Property Settings for Controls
Control                           Property                    Settings                                          The chart control events are used
Form                              Caption                     EFY Executive Dash Board                      together to display the value of any
                                  BackColor                   As shown                                      part of the chart/graph in the textbox
                                  BorderStyle                 FixedDialog                                   control when you click that part. The
                                  Name                        Dash                                          textbox is moved to that place by set-
                                  StartUpPosition             Center of the owner
                                                                                                            ting its top and left properties. The
Shape (4 Nos)                     Style                       Rectangle
                                                                                                            required code follows:
                                  Borderwidth                 2
                                  Bordercolor                 As shown or as you wish
                                  Fillcolor                   As shown or as you wish                       Private Sub MSChart1_MouseDown(Index

Label                             Alignment                   Center                                        As Integer, Button As Integer, Shift
                                  Caption                     Executive dash board                          As Integer, X As Single, Y As
                                  Font                        Font: Script MT Bold, Style: Bold, Size: 20   Single)
                                  Backcolor                   Parrot green                                  Timer1.Enabled = False
                                  Forecolor                   Red                                           TY = Y + MSChart1(Index).Top
MSChart (4 Nos                    AllowSeriesSelection        False                                         TX = X + MSChart1(Index).Left
by copy-and-paste                 Border style                VtBorderStyleFixedSingle
                                                                                                            End Sub
method)                           ShowLegend                  True
                                  ChartType                   (i) VtChChartType2Dbar
                                                                                                            Private Sub MSChart1_
                                                              (ii) VtChChartType3Dbar
                                                              (iii) VtChChartType2DPie                      PointSelected(Index As Integer,

                                                              (iv) VtChChartType2DLine                      Series As Integer, DataPoint As
                                  TitleText                   (i) Sales department                          Integer, MouseFlags As Integer,
                                                              (ii) Accounts department                      Cancel As Integer)
                                                              (iii) Editorial section                       Text1.Text = MSChart1(Index).
                                                              (iv) Organisation staff details               ChartData(DataPoint, Series)
TextBox (2 Nos)                   Alignment                   Center                                        Cancel = 1
                                  BackColor                   As shown for both
                                                                                                            If Text1.Text <> “” Then
                                  Enabled                     (i) False (ii) True
                                                                                                            Text1.Top = TY
                                  Text                        (i) ““ i.e. Blank (ii) 0000
                                                                                                            Text1.Left = TX
                                  ForeColor                   As shown for both
                                                                                                            Text1.Visible = True
                                  Font                        Style: Bold, (i) Size: 12 (ii) Size: 18
Frame (4 Nos)                     Caption                     ““ i.e. Blank                                 End If
Option buttons: A group           Caption of 5 buttons        2DBar, 2DBar, 2DLine, 2DLine, 2DPie           End Sub
of 5 buttons placed in            in each group
each of the four frames by                                                                                  Private Sub MSChart1_MouseUp(Index
copy-and-paste method                                                                                       As Integer, Button As Integer, Shift
                                  ForeColor                   White for each group                          As Integer, X As Single, Y As
                                  Font                        Style: Bold, Size: 8
                                  BackColor                   As shown for each group
                                                                                                            Text1.Visible = False
Timer                             Interval                    2000
                                                                                                            Text1.Text = “”
Adodcl                            ConnectionString            As explained in the previous VB articles
                                                                                                            Timer1.Enabled = True
                                                              based on MS Access
                                                                                                            End Sub
Note: Numbers (i), (ii), (iii) and (iv) shown under ‘setting’ are control numbers

                                                                                                                You can also select a particular
MaxBool = True                                            Text2.Text = M                                    information in the chart for details.
End If                                                    End If                                            For example, if you click the box cor-
                                                          Adodc1.Recordset.Update                           responding to EFY, you can see EFY
Case True:                                                Adodc1.Recordset.MoveNext                         details highlighted in the chart.
While Adodc1.Recordset.EOF = False                        Wend                                                  EFY note. All the relevant source
If Adodc1.Recordset.Fields(3).Value                       ‘check on no staff                                codes of this article have been included
> 0 Then                                                  If M = 0 Then                                     in this month’s EFY-CD. 
Adodc1.Recordset.Fields(3).Value =                        MaxBool = False
Adodc1.Recordset.Fields(3).Value - 1                      End If                                            Both the authors are lecturers at Government
M = M - 1                                                 End Select                                        Polytechnic, Bidar, Karnataka

1 0 8 • M a r c h 2 0 1 0 • e l e c t ro n i c s f o r yo u                                                                      w w w. e f y M ag . co M

To top