|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 11
  - Displaying Your Data with Graphs
    - The Advantages of Graphing Your Data
    - Loading and Using the Graph Control
      - Loading the Graph Control into the Visual Basic Toolbox
        - Figure 11.1.
      - Adding the Graph Control to Your Form
        - Figure 11.2.
      - Setting the Graph Type
        - Figure 11.3.
      - How the Graph Control Organizes Your Data
      - Adding Data in Design Mode
        - Figure 11.4.
      - Adding Data at Runtime
    - Listing 11.1. Adding code to the cmdSales_Click event.
      - Figure 11.5.
    - Listing 11.2. Adding code to the CmdAutoSales_Click event.
      - Adding Data Using the QuickData Property
    - Listing 11.3. Adding code to the cmdQuickSales_Click event.
      - Figure 11.6.
    - Listing 11.4. Reading a disk file and filling a graph.
      - Adding Titles, Labels, and Legends
        - Figure 11.7.
    - Listing 11.5. Adding code to the cmdTitles_Click event.
      - Figure 11.8.
    - Display Options
    - Listing 11.6. Adding code to the cmdWrite_Click event.
      - Figure 11.9.
    - Creating Your Data Graph OLE Server
      - Building the Data Graph Form
        - Figure 11.10.
    - Listing 11.7. Coding the Form_Load event.
    - Listing 11.8. Adding code to the Form_Resize event.
    - Listing 11.9. Coding the mnuFileItem_Click event.
    - Listing 11.10. Coding the mnuViewItem_Click event.
    - Listing 11.11. This code copies the graph to the Windows Clipboard.
    - Listing 11.12. Coding the GraphSave method.
      - Building the DataGraph Class Object
    - Listing 11.13. Setting up local storage for the class object.
    - Listing 11.14. Coding the Public properties of the DataGraph.
    - Listing 11.15. Coding the Class_Initialize event.
    - Listing 11.16. Coding the ShowGraph method.
    - Listing 11.17. Coding the OpenDB method.
    - Listing 11.18. Coding the InitGraph method.
    - Listing 11.19. Coding the LoadGraphData method.
      - Testing the Graph OLE Server
    - Listing 11.20. Adding code to the cmdRepSales_Click event.
      - Figure 11.11.
    - Adding More Properties to the DataGraph Object
    - Listing 11.21. Adding local storage variables for the new properties.
    - Listing 11.22. Code for the new properties.
    - Listing 11.23. Updating the Class_Initialize event.
    - Listing 11.24. Coding the AddLegends method.
    - Listing 11.25. Coding the AddLabels method.
    - Listing 11.26. Adding the left and bottom titles to the graph.
    - Listing 11.27. The updated ShowGraph method uses the new properties.
    - Listing 11.28. Updating the cmdSalesRep_Click event.
      - Figure 11.12.
    - Adding Graphs to the CompanyMaster Project
      - Adding the Graph Menu Option
        - Figure 11.13.
    - Listing 11.29. Adding the code to the mnuGraphsItem_Click event.
      - Figure 11.14.
    - Listing 11.30. Adding the code for the mnuGraphsMonthSales_Click event.
      - Figure 11.15.
    - Listing 11.31. Modifying the mnuGraphsItem_click event.
      - Figure 11.16.
    - Summary
    - Quiz
    - Exercises

## Day 11

## Displaying Your Data with Graphs

Today you learn how to add graph displays of your data to your database programs. By creating a simple graphing OLE Server that uses the graph control that ships with Visual Basic Professional Edition, you can easily create visual displays of your database.

You also learn how to use SQL `SELECT` statements for creating datasets to use as the basis for your graphs. These SQL statements can be built into your code or stored as QueryDef objects in your database.

You also learn how to save the generated graphs to disk as bitmap files, how to share your graphs with other programs by placing them on the Windows Clipboard, and how to send the completed graphs to the printer.

Finally, when you complete this chapter you'll have a graphing OLE Server that you can use in all your future Visual Basic projects. As an example, you add a set of default graphs to the CompanyMaster project you started last week.

> WARNING:: The OCX control used for this chapter is `GRAPH32.OCX`. This control ships with the professional version of Visual Basic 5, but is not installed during the normal installation process. If you have already installed Visual Basic 4, you have the `GRAPH32.OCX` control installed on your machine. However, if your machine only has Visual Basic 5 installed, you need to load the `GRAPH32.OCX` control from the `Tools\Controls` folder on the CD. Also, if you experience errors loading the control on your form, you need to re-register the `GRAPH32.OCX` control using the `vbctrl.reg` file that is found in the same folder. Just double-click the file to re-register your `GRAPH32.OCX` control.

### The Advantages of Graphing Your Data

Although generating data graphs is not, strictly speaking, a database function, almost all good database programs provide graphing capabilities. Visual representations of data are much easier to understand than tables or lists. Providing graphs in your database programs also gives users the chance to look at data in more than one way. Often, users discover important information in their data simply by looking at it from another angle.

Providing graphs also gives your programs an added polish that users appreciate. Quite often, users want more than a simple data entry program with a few list reports. Many times, users take data created with a Visual Basic program and export it to another Windows application in order to develop graphs and charts. Using the techniques you learn today, you can provide your users with all the graphing tools they need to develop graphs and charts without using other programs!

### Loading and Using the Graph Control

The graph control has a multitude of properties that you can manipulate in order to customize the graph display. Only the most commonly used options are covered here. (You can review the Visual Basic documentation for detailed information on all the properties of the graph control.) In this lesson, you learn how to use graph control properties to manipulate the way graphs appear on your forms by:

- Setting the graph type

- Adding graph data using the NumPoints, NumSets, AutoInc, GraphData, and QuickData properties

- Adding titles and legends

You also learn how to use the DrawMode property to send the completed graph to a printer, save it as a file, or copy it to the Windows Clipboard.

#### Loading the Graph Control into the Visual Basic Toolbox

> NOTE: Visual Basic 5 does not install the `GRAPH32.OCX` control by default. If you cannot locate the `GRAPH32.OCX` control in your version of Visual Basic 5, you need to copy it from the `Tools\Controls` folder of the Visual Basic 5 installation CD. You may also need to register the control using the `vbctrls.reg` file found in that same folder.

Before you can begin to use the graph control tool, you have to make sure it is loaded into the Visual Basic toolbox. To do this, load Visual Basic and select Project | Components from the Visual Basic main menu. In the list of available controls, locate Pinnacle-BPS Graph Control (see Figure 11.1). Click the checkbox to add the control to your toolbox and then click the OK button to exit the form.
Figure 11.1. Adding the graph control to your toolbox.

####
Adding the Graph Control to Your Form

It's very easy to create a good-looking graph using the graph tool. All you need to do is add the control to your form and fill it with data; the graph control does the rest. Let's create a simple graph to illustrate this point.

> NOTE: If you have not already loaded Visual Basic and added the graph control to the current project, review the preceding section and perform the required steps.

Add the graph control to a blank form by double-clicking the graph control icon in the toolbox. You see that the graph control automatically displays a two-dimensional bar graph with some data. Stretch the control so that your form looks like the one shown in Figure 11.2.
Figure 11.2. Adding the graph control to your form.
This is random data that the control automatically generates to help you get an idea of how the graph will look in your finished program. When you add your real data to the graph control, this random data disappears.

#### Setting the Graph Type

You determine the type of graph Visual Basic displays by setting the GraphType property. You can do this using the properties window during design time or through Visual Basic code at runtime. Because you already have the graph up on your form, move to the Properties window and locate the GraphType property. Set the property to display a three-dimensional pie chart by clicking the property in the window and then pulling down the list box. Find and select the 3D Pie option. Your screen should look like the one shown in Figure 11.3.
Figure 11.3. Changing the GraphType property at design time.
The graph control can display 11 different types of graphs including bar charts, pie charts, line and area graphs, Log/Lin graphs, Gantt charts, scatter graphs, polar graphs, and high/low/close graphs. The three most commonly used formats are covered in this lesson: bar, pie, and line graphs.

#### How the Graph Control Organizes Your Data

Before you can display data, you have to load it into the graph control. But, before you load it, you need to know how the graph control expects to see the data. The graph control requires that all the data be organized in sets and points. The graph control needs to know how many points of data are in each set you want to graph. Usually, you have a single set of data with multiple points. For example, if you want to graph company sales figures for the last 12 months, you would have a single dataset (company sales figures) with 12 points (one for each month). If you want to create a graph that compares the actual monthly sales figures with the budgeted figures for the last 12 months, you would have two sets of data (actual and budget figures), each with 12 points (one for each month).

You can use the NumSets and NumPoints properties to inform the graph control how the data is to be organized. Create a graph like the one just described. In design mode, use the Property box to set the NumPoints property to 12 and the NumSets property to 1. You have just told the graph control that it should prepare for one set of data containing 12 individual points.

#### Adding Data in Design Mode

Now add 12 data items at design time so that you can see how the graph looks. Locate the GraphData property in the Property box. It should be set to 0. Now type `1` and press the Enter key. You have just added one of the expected 12 data points for the set. Continue to add data by entering `2`, `3`, and so on until you have entered values up to 12.

Save the form as `FRMGRAPHDESIGN.FRM`, and save the project as `GRAPHDESIGN.VBP`. When you run the project, your graph should look something like the one shown in Figure 11.4.
Figure 11.4. Adding data in design mode.

####
Adding Data at Runtime

You can perform the same task at runtime using Visual Basic code. Add a command button to this form, setting its Name property to cmdSales and its Caption property to Sales. Then add the code in Listing 11.1 in the `cmdSales_Click` event:

### Listing 11.1. Adding code to the cmdSales_Click event.

    Private Sub cmdSales_Click()
        `
        ` add data using VB code
        `
        Dim intLoop As Integer
        `
        Graph1.DataReset = gphAllData   ` clear out all properties
        Graph1.GraphType = gphBar3D     ` use 3d bar chart
        `
        Graph1.NumSets = 1  ` only one set of data
        Graph1.NumPoints = 12 ` one for each month
        `
        ` now add the data
        `
        For intLoop = 1 To 12
            Graph1.ThisPoint = intLoop  ` point to fill
            Graph1.GraphData = intLoop  ` data to load
        Next
        `
        Graph1.DrawMode = gphDraw ` show results
        `
    End Sub

In Listing 11.1, you do a few things. First, you clear out any data that might already be stored in the graph control. Next, you set the GraphType property to show a three-dimensional bar graph. Also, you set the NumSets and NumPoints properties to 1 and 12, respectively, and then add the data points. Notice that the graph control must be told which set you want filled (using the ThisSet property). Next, you go through a loop--first, setting the ThisPoint property and then adding the data item. Finally, you set the DrawMode property to gphDraw to force Visual Basic to redraw the graph with the new data.

Save and run the project. When you click the Sales button, your form looks similar to the one shown in Figure 11.5.

Although this method works well, there is a faster method. By setting the AutoInc property of the graph control to `1`, the graph control automatically increments the NumPoints property. This can simplify and speed up your code. Add a new button to the form, setting its Name property to cmdAutoSales and its Caption property to &AutoSales. Then add the code in Listing 11.2 in the `cmdAutoSales_Click` event.
Figure 11.5. Creating a graph using Visual Basic code.

> WARNING:: If you are using Visual Basic 5's new "code-complete" option, notice that the editor suggests the properties "gphOff" or "gphOn" as valid values for the AutoInc property. In our version of Visual Basic 5, these values returned an error. Be sure to enter a value of `1` for the AutoInc property of the `GRAPH32.OCX` control.

### Listing 11.2. Adding code to the CmdAutoSales_Click event.

    Private Sub cmdAutoSales_Click()
        `
        ` add data using VB code
        `
        Dim intLoop As Integer
        `
        Graph1.DataReset = gphAllData   ` clear out all properties
        Graph1.GraphType = gphBar3D     ` use 3d bar chart
        `
        Graph1.NumSets = 1  ` only one set of data
        Graph1.NumPoints = 12 ` one for each month
        `
        ` now add the data
        `
        Graph1.AutoInc = 1  ` turn on auto incrementing
        For intLoop = 1 To 12
            Graph1.GraphData = intLoop  ` data to load
        Next
        `
        Graph1.DrawMode = gphDraw ` show results
        `
    End Sub

Save and run the project. When you press the AutoSales button, you see the same graph that was generated with the Sales button. Notice that, in this example, you left out the lines of code that set the ThisSet and ThisPoint properties. These values were handled by the graph control using the AutoInc property. This might not seem like a code savings, but it really is. Single-set data is relatively easy to graph. Multiple sets get pretty confusing. It's much easier to use the AutoInc property because it automatically updates the ThisSet property, too. Not only is this approach a bit easier, it is also faster. The fewer lines of code you need to execute within the `For...Next` loop, the faster your program runs.

There is yet another way to add data to a graph control: by using the QuickData property.

#### Adding Data Using the QuickData Property

You can use the QuickData property to add graph data in a single command at runtime. The QuickData property accepts a single character string that contains all the datasets and points. Each dataset must be separated by a carriage return/line feed pair. Each data point must be separated by a tab character. This is known as tab-delimited data. When you use the QuickData property to load graph data, you do not have to set any of the properties that deal with points or sets. You also do not have to force your Visual Basic code to process any `For...Next` loops.

Add another command button to the form, setting its Name property to cmdQuickSales and its Caption property to &QuickSales. Then add the code in Listing 11.3 in the `cmdQuickSales_Click` event window.

> NOTE: If you're using "code-complete," you notice that the QuickData property is missing from the list of valid selections in the pop-up window. Don't let Visual Basic 5 fool you! The QuickData property is a valid property for the `GRAPH32.OCX` control. It's just missing from the list.

### Listing 11.3. Adding code to the cmdQuickSales_Click event.

    Private Sub cmdQuickData_Click()
        `
        ` show quickdata method for building graphs
        `
        Dim strData As String
        `
        ` create three sets of data, each with four points
        `
        strData = "1" & vbTab & "2" & vbTab & "3" & vbTab & "4" & vbCrLf
        strData = strData & "5" & vbTab & "4" & vbTab & "3" & vbTab & "2" & vbCrLf
        strData = strData & "6" & vbTab & "8" & vbTab & "10" & vbTab & "4" & vbCrLf
        `
        Graph1.GraphType = gphLine
        Graph1.DataReset = gphAllData
        Graph1.QuickData = strData
        `
        Graph1.DrawMode = gphDraw
        `
    End Sub

Notice that you created a list of data that contained three sets of four points each. When you use the QuickData property, the graph control is able to determine the total number of sets and the number of points in each set without using the NumSets and NumPoints properties. Save and run this project. Your screen should look like the one shown in Figure 11.6.
Figure 11.6. Adding graph data using the QuickData property.
The real advantage of using the QuickData property is that it can accept data from most spreadsheets through Windows cut and paste operations. By placing tab-delimited data on the Windows Clipboard, you can use that data as the input for the QuickData property.

> NOTE: Because you are working with data tables, you won't use the QuickData property to transfer datasets to the graph control. You can refer to the Visual Basic documentation for more information on using the Windows Clipboard and QuickData.

One of the handier uses of the QuickData property is the ability to read a tab-delimited text file and produce a quick graph of the data. You can use Microsoft Notepad, WordPad, or any other text editor to put tab-delimited values into a disk file and then read that file from your Visual Basic program. You can even program Microsoft Word to read data from a Word table and produce a tabbed list of numbers for input into the graph control.

To demonstrate this ability, add a new button to the form. Set its Name property to cmdLoadData and its Caption property to &Load Data. Now add the code from Listing 11.4 to the `Click` event of the cmdLoadData button.

### Listing 11.4. Reading a disk file and filling a graph.

    Private Sub cmdAutoSales_Click()
        `
        ` add data using VB code
        `
        Dim intLoop As Integer
        `
        Graph1.DataReset = gphAllData   ` clear out all properties
        Graph1.GraphType = gphBar3D     ` use 3d bar chart
        `
        Graph1.NumSets = 1  ` only one set of data
        Graph1.NumPoints = 12 ` one for each month
        `
        ` now add the data
        `
        Graph1.AutoInc = 1  ` turn on auto incrementing
        For intLoop = 1 To 12
            Graph1.GraphData = intLoop  ` data to load
        Next
        `
        Graph1.DrawMode = gphDraw ` show results
        `
    End Sub

    Private Sub cmdLoadData_Click()
        `
        ` load tabbed data from a file
        `
        Dim strRdLine As String
        Dim strData As String
        Dim strFileName As String
        Dim intFileHandle As Integer
        `
        ` load tab-delimited text file
        strFileName = App.Path & "\..\..\data\graphs\tabdata.txt"
        intFileHandle = FreeFile
        `
        Open strFileName For Input As intFileHandle
            Do While Not EOF(intFileHandle)
                Line Input #1, strRdLine
                strData = strData & strRdLine & vbCrLf
            Loop
        Close #intFileHandle
        `
        ` setup and display graph
        Graph1.GraphType = gphLine
        Graph1.DataReset = gphAllData
        Graph1.QuickData = strData
        `
        Graph1.DrawMode = gphDraw
        `
    End Sub

When you save and run the project, press the Load Data button. Your screen should look something like the one in Figure 11.7.

Notice that the only difference between the code in Listing 11.3 and Listing 11.4 is that the `strData` variable is loaded from the disk file in Listing 11.4.

#### Adding Titles, Labels, and Legends

In addition to loading data and setting the graph type, you can also set graph titles, labels for the data points, and legends for the graph.

Now add another button to the project to illustrate these features of the graph control. Set the button's Name property to cmdTitles and its Caption property to &Titles. Add Listing 11.5 to the `cmdTitles_Click` event window.
Figure 11.7. Loading data from a disk file.

###
Listing 11.5. Adding code to the cmdTitles_Click event.

    Private Sub cmdTitles_Click()
        `
        ` add titles to existing graph
        `
        Dim intLoop As Integer
        `
        ` add the titles
        Graph1.GraphTitle = "Graph Title"
        Graph1.BottomTitle = "Bottom Title"
        Graph1.LeftTitle = "Left Title"
        `
        ` add legends
        Graph1.AutoInc = 1
        For intLoop = 1 To 12
            Graph1.LegendText = "L" & CStr(intLoop)
        Next
        `
        ` add labels
        Graph1.AutoInc = 1
        For intLoop = 1 To 12
            Graph1.LabelText = "X" & CStr(intLoop)
        Next
        `
        Graph1.DrawMode = gphDraw
        `
    End Sub

In Listing 11.5, you inhìialize the three titles and then add legends and labels for the data points. Notice that you used the AutoInc property when adding the legends and labels. Notice, too, that you did not add legends and labels within the same `For...Next` loop. If you use the AutoInc property, you can only update one element type at a time. When you have more than one element array to update (data, legends, and labels), you must use separate loops for each element array.

> NOTE: It is very unlikely that you would use both a legend and data point labels in the same graph. You did this here to illustrate the unique behavior of the AutoInc property.

Save and run the project. You can apply the text features of the graph control to any graph. After clicking a button to produce a graph, click the Titles button to add the text to the graph. Your screen
should look like the one shown in Figure 11.8.
Figure 11.8. Adding titles, labels, and legends to a graph.

> TIP: If your graph does not show the legends on the right, you may need to enlarge your graph display image. The graph control determines the exact placement and sizing of all titles, legends, and labels.

#### Display Options

You can also send the completed graph to a file, to the Windows Clipboard, or to your printer. All those options are covered in the next section. For now, you add a button that writes the completed graph to a disk file as a bitmap image.

Add one more command button to the form. Set its Name property to cmdWrite and its Caption property to &Write. Add Listing 11.6 to the `cmdWrite_Click` event window.

### Listing 11.6. Adding code to the cmdWrite_Click event.

    Private Sub cmdWrite_Click()
        `
        Graph1.ImageFile = App.Path + "\GraphDesign.BMP" ` set file name
        Graph1.DrawMode = gphBlit   ` set for bitmap mode
        Graph1.DrawMode = gphWrite  ` force to file
        Graph1.DrawMode = gphDraw   ` redraw control
        `
    End Sub

In Listing 11.6, you first set the name of the file to be created. Then you set the drawing mode to bitmap. You then force the creation of the graph file, and, finally, redraw the graph on-screen.

Save and run the project. You see the screen flicker during the redrawing. If you check your disk drive, you find the data file you created. You can load this file using Microsoft Paint or any other program that can read bitmap images. Figure 11.9 shows both the Visual Basic 5 form and Microsoft Paint running at the same time.
Figure 11.9. Displaying the graph in both Visual Basic and Microsoft Paint.

###
Creating Your Data Graph OLE Server

Now that you have learned the basic techniques of using the graph control, you are ready to build your database graph OLE Server. This OLE Server consists of a single form that contains a graph control and a menu of graphing options. It also has methods that load the form, set the graphing values using your dataset, and display the results. You can pass any valid Visual Basic Recordset object to the data graph OLE Server and display any single-set, multipoint dataset without any further modification of the code.

#### Building the Data Graph Form

First, start a new ActiveX DLL Visual Basic project. Set the name of the project to prjDataGraph and the name of the default class module to DataGraph. Add a form to the project and set its Name property to frmGraph. You also need to make sure you load the Pinnacle-BPS graph control (`GRAPH32.OCX`) and the Microsoft common dialog control to the project.

> NOTE: Make sure the Pinnacle-BPS graph control is in your Visual Basic toolbox. If not, refer back to the "Loading the Graph Control into the Visual Basic Toolbox" section for instructions on how to add it to your project's toolbox.

Add the graph control to your form. Also add the CommonDialog control to the form. You use this control to add file and print capabilities to the graphing library. You also need to add a menu to the form. Refer to Figure 11.10 and Tables 11.1 and 11.2 as guides for laying out this form.
Figure 11.10. Laying out the graph library form.
Table 11.1. The control table for the graph library form.

Control | Property | Setting
Form | Name | frmGraph
| Caption | Graph Data
| Height | 3375
| Left | 2145
| Top | 1710
| Width | 5280
Graph | Name | Graph1
| Height | 2415
| Left | 120
| Top | 120
| Width | 4935
| BorderStyle | 1-Fixed Single
CommonDialog | Name | CommonDialog1

Table 11.2. The menu table for the graph library form.

Caption | Menu
&File | mnuFile
&Save | mnuFileItem(0)
&Copy | mnuFileItem(1)
- | mnuFileItem(2)
&Print | mnuFileItem(3)
Print Set&Up | mnuFileItem(4)
- | mnuFileItem(5)
E&xit | mnuFileItem(6)
&View | mnuView
&Pie Chart | mnuViewItem(0)
&Bar Graph | mnuViewItem(1)
&Line Chart | mnuViewItem(2)
&Area Graph | mnuViewItem(3)

Note that you are building menu arrays with the form. Menu arrays, like other control arrays, can speed the processing of your program and simplify the coding and maintenance of your forms.

You need to add some code to this form. But first, save it (`FRMGRAPH.FRM`) and save the project (`PRJDATAGRAPH.VBP`). Then add the code in Listing 11.7 to the `Form_Load` event of the project. This sets the default size of the form to fill 75% of the screen.

### Listing 11.7. Coding the Form_Load event.

    Private Sub Form_Load()
        `
        ` set initial form size
        `
        If Me.WindowState = vbNormal Then
            Me.Width = Screen.Width * 0.75
            Me.Height = Screen.Height * 0.75
        End If
        `
    End Sub

Now, add the code from Listing 11.8 to the `Form_Resize` event. This code allows users to resize the graph by resizing the form.

### Listing 11.8. Adding code to the Form_Resize event.

    Private Sub Form_Resize()
        `
        ` make graph fill the form
        `
        Graph1.Left = 1
        Graph1.Top = 1
        Graph1.Width = Me.ScaleWidth
        Graph1.Height = Me.ScaleHeight
        `
    End Sub

The code in Listing 11.9 goes in the `mnuFileItem_Click` event. This code is executed each time the user selects one of the File menu items. The Index value returns the item number selected by the user. You code the `CopyGraph` and `SaveGraph` methods in just a moment.

### Listing 11.9. Coding the mnuFileItem_Click event.

    Private Sub mnuFileItem_Click(Index As Integer)
        `
        ` handle file menu selections
        `
        Select Case Index
            Case 0 ` save
                GraphSave
            Case 1 ` copy
                GraphCopy
            Case 2 ` separator
                ` no action
            Case 3 ` print
                Me.PrintForm
            Case 4 ` printer setup
                CommonDialog1.ShowPrinter
            Case 5 ` separator
                ` no action
            Case 6 ` exit
                Unload Me
        End Select
        `
    End Sub

Now add the code in Listing 11.10 to the `mnuViewItem_Click` event. This allows users to select the type of graph they view.

### Listing 11.10. Coding the mnuViewItem_Click event.

    Private Sub mnuViewItem_Click(Index As Integer)
        `
        ` handle view selections
        `
        Dim intGraphType As Integer
        `
        Select Case Index
            Case 0 ` pie chart
                intGraphType = gphPie3D
            Case 1 ` bar graph
                intGraphType = gphBar3D
            Case 2 ` line chart
                intGraphType = gphLine
            Case 3 ` area graph
                intGraphType = gphArea
        End Select
        `
        Graph1.GraphType = intGraphType
        Graph1.DrawMode = gphDraw
        `
    End Sub

Now you're ready to code the `GraphCopy` method. The code in Listing 11.11 is all you need to copy the graph image to the Windows Clipboard. You can then paste this image of the graph from the Clipboard to any other Windows program that allows image cut and paste operations (Microsoft Write, for example).

### Listing 11.11. This code copies the graph to the Windows Clipboard.

    Public Sub GraphCopy()
        `
        ` copy graph to clipboard
        `
        Graph1.DrawMode = gphBlit
        Graph1.DrawMode = gphCopy
        Graph1.DrawMode = gphDraw
        `
        MsgBox "Graph has been copied to the clipboard", _
            vbInformation, _
            "Copy Graph"
        `
    End Sub

Next, add the `GraphSave` method code from Listing 11.12 to the project. This code prompts the user for a filename and saves the current graph under that filename.

### Listing 11.12. Coding the GraphSave method.

    Public Sub GraphSave()
        `
        ` save graph to disk file
        `
        Dim strFileName As String
        `
        CommonDialog1.DefaultExt = ".bmp"
        CommonDialog1.DialogTitle = "Save Graph"
        CommonDialog1.Filter = "Bitmap Files | *.bmp"
        CommonDialog1.ShowSave
        strFileName = CommonDialog1.filename
        `
        If Trim(strFileName) <> "" Then
            Graph1.DrawMode = gphBlit
            Graph1.ImageFile = strFileName
            Graph1.DrawMode = gphWrite
            Graph1.DrawMode = gphDraw
        End If
        `
    End Sub

That's all the code you need for the form. Save this form now. Next, you create the routine that calls this form.

#### Building the DataGraph Class Object

In order to display the form you just created, you need to create a class object that allows users to set some properties and executes a `ShowGraph` method. The basic properties of the DataGraph object are

- GraphType: The initial graph type for the display.

- DatabaseName: The official name of the database that contains the records to graph.

- SQLSelect: The SQL `SELECT` statement that creates the Recordset to display.

- GraphField: The name of the column in the Recordset that contains the values you load into the GraphData property of the graph control.

- GraphTitle: The title of the graph.

This is a simple graph tool that is capable of displaying a single-set, multipoint dataset in the most commonly used graph types. Modifications can be made to this routine to add additional labeling, legends, and text. You could also add options in order to graph more than one set of data per graph. For now, just keep the project simple. When you complete this project, you can add your own modifications.

First, you need to add some local storage variables to the class. These contain the passed properties along with a couple of local variables needed for Private methods. Add the code from Listing 11.13 to the class.

### Listing 11.13. Setting up local storage for the class object.

    Option Explicit

    `
    ` enumerated graph types
    `
    Enum dgType
        dgPie3d = gphPie3D
        dgBar3d = gphBar3D
        dgLine = gphLine
        dgArea = gphArea
    End Enum
    `
    ` local property storage
    `
    Private intGraphType As Integer
    Private strDBName As String
    Private strSQLSelect As String
    Private strFieldPoint As String
    Private strTitle As String
    `
    ` for internal use only
    Private ws As Workspace
    Private db As Database
    Private rs As Recordset
    Private lngNumPoints As Long
    Private lngLoop As Long
    `

Notice the use of the `Enum..End Enum` construct in the declarations section. This is a special type of constant declaration that combines a user-defined type (dgType) with a set of predefined values (dgPie3D, dgBar3D, dgLine, dgArea). When you use this class object in your programs, you can see the enumerated types in the code-complete windows that appear as you enter the source code.

Now you need to declare Public properties of the class. These properties allow users to manipulate the local storage variables of the class. Note that all five properties are included in Listing 11.14. You need to add each property individually using the Tools | Add Procedure | Property options from the main menu.

### Listing 11.14. Coding the Public properties of the DataGraph.

    Public Property Get GraphType() As Integer
        GraphType = intGraphType
    End Property

    Public Property Let GraphType(ByVal vNewValue As Integer)
        If IsNumeric(vNewValue) Then
            intGraphType = Int(vNewValue)
        End If
        `
        If intGraphType < 1 Or intGraphType > 11 Then
            Err.Raise 380 ` invalid property
            intGraphType = 0
        End If
        `
    End Property

    Public Property Get DatabaseName() As String
        DatabaseName = strDBName
    End Property

    Public Property Let DatabaseName(ByVal vNewValue As String)
        strDBName = vNewValue
    End Property

    Public Property Get SQLSelect() As String
        SQLSelect = strSQLSelect
    End Property

    Public Property Let SQLSelect(ByVal vNewValue As String)
        strSQLSelect = vNewValue
    End Property

    Public Property Get GraphField() As String
        GraphField = strFieldPoint
    End Property

    Public Property Let GraphField(ByVal vNewValue As String)
        strFieldPoint = vNewValue
    End Property

    Public Property Get GraphTitle() As String
        GraphTitle = strTitle
    End Property

    Public Property Let GraphTitle(ByVal vNewValue As String)
        strTitle = vNewValue
    End Property

Notice some error-checking code in the GraphType property `Let` method. This checks for a valid graph type and reports an error if a valid value is not found.

Now add the code from Listing 11.15 to the `Class_Initialize` event. This sets the default values for the properties.

### Listing 11.15. Coding the Class_Initialize event.

    Private Sub Class_Initialize()
        `
        ` set startup values
        `
        strDBName = ""
        strSQLSelect = ""
        strFieldPoint = ""
        strTitle = "Data Graph"
        intGraphType = gphBar3D
        `
    End Sub

Now you're ready to code the `ShowGraph` method. This one method collects all the property values, creates a dataset, and builds a graph based on the data. Create a `Public Sub` called `ShowGraph` in the class and add the code from Listing 11.16.

### Listing 11.16. Coding the ShowGraph method.

    Public Sub ShowGraph()
        `
        ` display graph
        `
        On Error GoTo LocalErr
        `
        Screen.MousePointer = vbHourglass
        OpenDB
        InitGraph
        LoadGraphData
        Screen.MousePointer = vbNormal
        `
        frmGraph.Graph1.DrawMode = gphDraw
        frmGraph.Show vbModal
        `
        Exit Sub
        `
    LocalErr:
        Err.Raise vbObjectError + 4, App.EXEName, "Error displaying graph"
        `
    End Sub

This method calls three other Private methods. Create the `Private Sub OpenDB` and add the code from Listing 11.17.

### Listing 11.17. Coding the OpenDB method.

    Private Sub OpenDB()
        `
        ` open database/recordset
        `
        On Error GoTo LocalErr
        `
        Set ws = dbengine.Workspaces(0)
        Set db = ws.OpenDatabase(strDBName)
        Set rs = db.OpenRecordset(strSQLSelect, dbOpenSnapshot)
        `
        Exit Sub
        `
    LocalErr:
        Err.Raise vbObjectError + 1, App.EXEName, "Error creating data set"
        `
    End Sub

Next, add the `Private Sub InitGraph` and enter the code from Listing 11.18. This code loads the frmGraph form and sets the initial values of the display.

### Listing 11.18. Coding the InitGraph method.

    Private Sub InitGraph()
        `
        ` initialize the graph form
        `
        On Error GoTo LocalErr
        `
        rs.MoveLast
        lngNumPoints = rs.RecordCount
        `
        Load frmGraph
        frmGraph.Graph1.GraphType = intGraphType
        frmGraph.Graph1.GraphTitle = strTitle
        frmGraph.Graph1.NumSets = 1
        frmGraph.Graph1.NumPoints = lngNumPoints
        frmGraph.Graph1.AutoInc = 1
        `
        Exit Sub
        `
    LocalErr:
        Err.Raise vbObjectError + 2, App.EXEName, "Error intializing graph form"
        `
    End Sub

Finally, add the `Private Sub LoadGraphData` to the class. This is the code that moves the data from the Recordset into the graph control (see Listing 11.19).

### Listing 11.19. Coding the LoadGraphData method.

    Private Sub LoadGraphData()
        `
        ` fill graph with data
        `
        On Error GoTo LocalErr
        `
        rs.MoveFirst
        For lngLoop = 1 To lngNumPoints
            frmGraph.Graph1.GraphData = rs.Fields(strFieldPoint)
            rs.MoveNext
        Next
        `
        Exit Sub
        `
    LocalErr:
        Err.Raise vbObjectError + 3, App.EXEName, "Error loading graph data"
        `
    End Sub

That's all there is to it. You now have a reusable data graphing OLE Server. All you need to do now is save the project and compile the DLL. You can do this by selecting File | Make prjDataGraph.DLL from the main menu. In the next section, you test this library with a simple example.

#### Testing the Graph OLE Server

You need to build a short program to test your new library. Suppose you have just been told that the marketing department needs a tool to display the year-to-date book sales by sales representative. The data already exists in a database, but there is no easy way to turn that data into a visual display that upper-level management can access on a regular basis. You have been asked to quickly put together a graphing front-end for the sales data.

In order to complete the job, you need to initialize a copy of the DataGraph object; set the DatabaseName and SQLSelect properties; set the GraphType, GraphField, and GraphTitle properties; and then execute the `ShowGraph` method. From there, users can select various graph styles and, if they wish, save the graph to disk, send it to the printer, or copy it to the Clipboard to paste in other documents.

Because you already have the completed graph library, you can complete your assignment with as few as 10 lines of Visual Basic code.

First, if you don't have it running right now, start Visual Basic and create a new Standard EXE project. If you still have Visual Basic up from the last section of this chapter, select File | Add Project... to add a new Standard EXE project. Make this new project the Default startup project, too.

Now, select Project | References and locate and select the prjDataGraph library. This links your new project with the OLE Server DLL that you built earlier in this chapter.

Add a single button to a blank form. Set its Name property to cmdRepSales and its Caption property to &RepSales. Add the code from Listing 11.20 to support the button.

### Listing 11.20. Adding code to the cmdRepSales_Click event.

    Private Sub cmdRepSales_Click()
        `
        ` test graph libaray
        `
        Dim objDG As Object
        `
        Set objDG = New DataGraph
        `
        objDG.DatabaseName = App.Path & "\..\..\data\books5.mdb"
        objDG.sqlselect = "SELECT SalesRep, SUM(Units) AS UnitsSold FROM BookSales GROUP BY SalesRep"
        objDG.graphfield = "UnitsSold"
        objDG.graphTitle = "Units Sold by Sales Rep"
        `
        objDG.showgraph
        `
    End Sub

This code example sets a few properties and then calls the `ShowGraph` method. That's all there is to it! Save this form as `FRMTEST.FRM` and the project as `PRJTEST.VBP`. Now run the project. After you click the single command button, you see the graph displayed on-screen. Your screen should look something like the one shown in Figure 11.11.

You have just completed your first database graphing project using the new OLE Server! Before you end your work on this OLE Server, let's add a few new properties to the class object. These give you (and our users) greater control over the graph display.
Figure 11.11. A graph of book sales data by sales rep.

####
Adding More Properties to the DataGraph Object

Now that the basic DataGraph object is working, let's add some of the bells and whistles that make this a versatile programming tool. Let's add four new properties to the class:

- LegendField: This is the Recordset column to use for setting the legends of the graph.

- LabelField: This is the Recordset column to use for setting the labels of the graph.

- LeftTitle: This is the string to display as the title on the left side of the graph.

- BottomTitle: This is the string to display as the title at the bottom of the graph.

In order to add these new features, all you need to do is add the property definitions to the DataGraph object and then add code to the `ShowGraph` method to incorporate these new properties into the displayed graph.

The code in Listing 11.21 shows the changes you need to make to the general declaration section of the DataGraph class. The four new variables are added at the end of the section.

### Listing 11.21. Adding local storage variables for the new properties.

    Option Explicit

    `
    ` enumerated graph types
    `
    Enum dgType
        dgPie3d = gphPie3D
        dgBar3d = gphBar3D
        dgLine = gphLine
        dgArea = gphArea
    End Enum
    `
    ` local property storage
    `
    Private intGraphType As Integer
    Private strDBName As String
    Private strSQLSelect As String
    Private strFieldPoint As String
    Private strTitle As String
    `
    ` for internal use only
    Private ws As Workspace
    Private db As Database
    Private rs As Recordset
    Private lngNumPoints As Long
    Private lngLoop As Long
    `
    ` added properties
    Private strLegendField As String
    Private strLabelField As String
    Private strLeftTitle As String
    Private strBottomTitle As String
    `

The next step is to use the Tools | Add Procedure | Property options from the main menu to add the four new property declarations to the class. Listing 11.22 shows the code for each of these new properties.

### Listing 11.22. Code for the new properties.

    Public Property Get LegendField() As Variant
        LegendField = strLegendField
    End Property

    Public Property Let LegendField(ByVal vNewValue As Variant)
        strLegendField = vNewValue
    End Property

    Public Property Get LabelField() As Variant
        LabelField = strLabelField
    End Property

    Public Property Let LabelField(ByVal vNewValue As Variant)
        strLabelField = vNewValue
    End Property

    Public Property Get LeftTitle() As Variant
        LeftTitle = strLeftTitle
    End Property

    Public Property Let LeftTitle(ByVal vNewValue As Variant)
        strLeftTitle = vNewValue
    End Property

    Public Property Get BottomTitle() As Variant
        BottomTitle = strBottomTitle
    End Property

    Public Property Let BottomTitle(ByVal vNewValue As Variant)
        strBottomTitle = vNewValue
    End Property

Because just about any string value could be valid for these properties, no error checking is done at the time the properties are set. In a production application, you might want to add error-checking to protect users.

After adding the new properties, you need to update the `Class_Initialize` event to set the new properties at the start of the object. Listing 11.23 shows the code you need to add to the `Class_Initialize` event.

### Listing 11.23. Updating the Class_Initialize event.

    Private Sub Class_Initialize()
        `
        ` set startup values
        `
        strDBName = ""
        strSQLSelect = ""
        strFieldPoint = ""
        strTitle = "Data Graph"
        intGraphType = dgBar3D
        `
        ` initialize added properties
        `
        strLegendField = ""
        strLabelField = ""
        strBottomTitle = ""
        strLeftTitle = ""
        `
    End Sub

Now you need to build routines to add the legends and labels to the graph. Create a `Private Sub` method called `AddLegends` and enter the code from Listing 11.24.

### Listing 11.24. Coding the AddLegends method.

    Private Sub AddLegends()
        `
        ` add legends to the existing graph
        `
        If Trim(strLegendField) = "" Then Exit Sub
        `
        frmGraph.Graph1.AutoInc = 1
        `
        rs.MoveFirst
        For lngLoop = 1 To lngNumPoints
            frmGraph.Graph1.LegendText = rs.Fields(strLegendField)
            rs.MoveNext
        Next
        `
    End Sub

Notice the first line of code in Listing 11.24. The line checks to see whether the `strLegendField` variable contains any printable data. If not, the `Exit Sub` is executed. No reason to set the legends if no LegendField has been set!

Now add the `Private Sub` called `AddLabels` to your class. The code in Listing 11.25 should be added to this new method.

### Listing 11.25. Coding the AddLabels method.

    Private Sub AddLabels()
        `
        ` add labels to the existing graph
        `
        If Trim(strLabelField) = "" Then Exit Sub
        `
        frmGraph.Graph1.AutoInc = 1
        `
        rs.MoveFirst
        For lngLoop = 1 To lngNumPoints
            frmGraph.Graph1.LabelText = rs.Fields(strLabelField)
            rs.MoveNext
        Next
        `
    End Sub

Next, you need to add the `Private Sub` called `AddTitles` to the class. This sets the left and bottom title properties of the graph. Listing 11.26 contains the code needed for this method. Note that the variables `strLeftTitle` and `strBottomTitle` are checked for valid data before the graph properties are actually set.

### Listing 11.26. Adding the left and bottom titles to the graph.

    Private Sub AddTitles()
        `
        ` add left and bottom titles
        `
        If Trim(strLeftTitle) <> "" Then
            frmGraph.Graph1.LeftTitle = strLeftTitle
        End If
        `
        If Trim(strBottomTitle) <> "" Then
            frmGraph.Graph1.BottomTitle = strBottomTitle
        End If
        `
    End Sub

Finally, it's time to modify the `ShowGraph` method to incorporate the new methods into your graph display. Listing 11.27 shows the updated `ShowGraph` method.

### Listing 11.27. The updated ShowGraph method uses the new properties.

    Public Sub ShowGraph()
        `
        ` display graph
        `
        On Error GoTo LocalErr
        `
        Screen.MousePointer = vbHourglass
        OpenDB
        InitGraph
        LoadGraphData
        `
        ` added property handling
        AddLegends
        AddLabels
        AddTitles
        `
        Screen.MousePointer = vbNormal
        `
        frmGraph.Graph1.DrawMode = gphDraw
        frmGraph.Show vbModal
        `
        Exit Sub
        `
    LocalErr:
        Err.Raise vbObjectError + 4, App.EXEName, "Error displaying graph"
        `
    End Sub

Now all you need to do is modify the `cmdSalesRep_Click` event of the frmTest form to test out these new properties. Switch to the prjTest project and add the code from Listing 11.28 to the `cmdSalesRep_Click` event.

### Listing 11.28. Updating the cmdSalesRep_Click event.

    Private Sub cmdRepSales_Click()
        `
        ` test graph libaray
        `
        Dim objDG As Object
        `
        Set objDG = New DataGraph
        `
        objDG.DatabaseName = App.Path & "\..\..\data\books5.mdb"
        objDG.sqlselect = "SELECT SalesRep, SUM(Units) AS UnitsSold FROM BookSales GROUP BY SalesRep"
        objDG.graphfield = "UnitsSold"
        objDG.graphTitle = "Units Sold by Sales Rep"
        `
        ` added properties
        objDG.labelfield = "SalesRep"
        objDG.lefttitle = "Units Sold"
        objDG.bottomtitle = "Sales Reps"
        objDG.legendfield = "SalesRep"
        `
        objDG.showgraph
        `
    End Sub

Save and run the `PRJTEST.VBP` project. When you click the SalesRep button, your screen should look like the one shown in Figure 11.12.
Figure 11.12. Viewing the updated graph object display.

As soon as you confirm that the modifications to the DataGraph object work without errors, recompile the ActiveX DLL. You can use this OLE Server DLL in all your future VBA projects. As an example, you can now add some graphs to the CompanyMaster project you started last week.

### Adding Graphs to the CompanyMaster Project

For the last project of the day, you add three graphs to the CompanyMaster project:

- A pie graph showing the actual year-to-date sales totals by region

- A line graph showing the year-to-date sales totals by month

- A bar graph showing the sales by customer

First you have to add a new menu item to the CompanyMaster form that calls the graphs. Then you need to construct SQL statements that select the desired data and feed it to the graph object.

#### Adding the Graph Menu Option

Adding the graph menu items is pretty easy. First, load the `MASTER.VBP` project from the `CHAP11\COMASTER` directory. Add the graph object to the project by selecting Project | References and locating and adding the Databound Graph object library (see Figure 11.13).
Figure 11.13. Adding the Databound Graph object library.
Use Table 11.3 as a guide for adding the following menu items to the CompanyMaster menu.
Table 11.3. Added menu items for the CompanyMaster main menu.

Caption | Menu
&Graphs | mnuGraphs
Sales by &Region | mnuGraphsItem(0)
Sales by &Month | mnuGraphsItem(1)
Sales by &Customer | mnuGraphsItem(2)

Now you need to add code to the form to make the calls to the DataGraph object. For your first graph, you want to create a pie chart showing the total year's sales by region. The fields you have to work with in the SalesData table are CompanyName, Year, Month, Amount, and Region. The database contains records for each month for each customer, along with budget values for the year. These budget records are stored with a CompanyName of Budget.

To get the total customer sales by region, you use the following SQL `SELECT` statement:

    SELECT Region, SUM(Amount) AS SalesTotal
       FROM SalesData
       WHERE CompanyName<>'Budget'
       GROUP BY Region

This is the SQL statement you use to generate the Snapshot object that is passed to the graph library. Place Listing 11.29 in the `mnuGraphsItem Click` event.

### Listing 11.29. Adding the code to the mnuGraphsItem_Click event.

    Private Sub mnuGraphsItem_Click(Index As Integer)
        `
        ` handle graph menu requests
        `
        Dim objDG As New DataGraph
        Dim strSQL As String
        `
        Select Case Index
            Case 0 ` sales by region
                `
                strSQL = "SELECT Region, SUM(Amount) AS SalesTotal FROM "
                strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY    	            ÂRegion"
                `
                objDG.DatabaseName = Data1.DatabaseName
                objDG.SQLSelect = strSQL
                objDG.GraphType = dgPie3d
                objDG.GraphField = "SalesTotal"
                objDG.GraphTitle = "Sales by Region"
                objDG.LegendField = "Region"
                `
            Case 1 ` sales by month
            Case 2 ` sales by customer
    End Select
        `
        objDG.ShowGraph
        `
    End Sub

Notice that you used the SQL statement you defined earlier as the SQLSelect property of the DataGraph object. The rest of the code should be familiar by now: You set several properties that are required for the graph object library and then you called the `ShowGraph` method.

> NOTE: It is important to keep in mind that you did not have to load the `GRAPH32.OCX` file into your project. Because this is part of the ActiveX DLL, you do not need to identify it in the Visual Basic project that uses the DLL. The OCX, and its supporting files, must be present on your machine, but you do not have to add it to your project.

Now, save and run the project. When you select Graph | Sales by Region from the main menu, you should see a graph like the one shown in Figure 11.14.
Figure 11.14. Displaying the Sales by Region pie graph.
You can resize the form and the graph resizes too. You can also use the menu on the graph to print, save, or copy the graph to the Clipboard.

Now add the Sales by Month graph to the project. This time, you want a line graph that shows the total sales by month. First, you need to construct the SQL statement. It should look like the following:

    SELECT Month, SUM(Amount) AS SalesTotal
       FROM SalesData
       WHERE CompanyName<>'Budget'
       GROUP BY Month;

Now open the `mnuGraphsItem_Click` event and add the code in Listing 11.30.

### Listing 11.30. Adding the code for the mnuGraphsMonthSales_Click event.

    Private Sub mnuGraphsItem_Click(Index As Integer)
        `
        ` handle graph menu requests
        `
        Dim objDG As New DataGraph
        Dim strSQL As String
        `
        Select Case Index
            Case 0 ` sales by region
                `
                strSQL = "SELECT Region, SUM(Amount) AS SalesTotal FROM "
                strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY 	            ÂRegion"
                `
                objDG.DatabaseName = Data1.DatabaseName
                objDG.SQLSelect = strSQL
                objDG.GraphType = dgPie3d
                objDG.GraphField = "SalesTotal"
                objDG.GraphTitle = "Sales by Region"
                objDG.LegendField = "Region"
                `
            Case 1 ` sales by month
                `
                strSQL = "SELECT Month, SUM(Amount) AS SalesTotal FROM "
                strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY   	            ÂMonth"
                `
                objDG.DatabaseName = Data1.DatabaseName
                objDG.SQLSelect = strSQL
                objDG.GraphType = dgLine
                objDG.GraphField = "SalesTotal"
                objDG.LabelField = "Month"
                objDG.GraphTitle = "Sales by Month"
                objDG.LeftTitle = "($)"
                objDG.BottomTitle = "Months"
                `
            Case 2 ` sales by customer
    End Select
        `
        objDG.ShowGraph
        `
    End Sub

The only real difference here is the new SQL statement and the settings for the titles, labels, and legends. Save and run this code. Check your resultant graph with the one shown in Fig-ure 11.15.
Figure 11.15. Displaying the Sales by Month line graph.
Finally, add the Sales by Company bar graph to the CompanyMaster project. Here is the SQL statement you need to produce a dataset that contains the year-to-date sales figures by company:

    SELECT CompanyName, SUM(Amount) AS SalesTotal
       FROM SalesData
       WHERE CompanyName<>'Budget'
       GROUP BY CompanyName;

Now modify the `mnuGraphsItem_Click` event to match the code in Listing 11.31.

### Listing 11.31. Modifying the mnuGraphsItem_click event.

    Private Sub mnuGraphsItem_Click(Index As Integer)
        `
        ` handle graph menu requests
        `
        Dim objDG As New DataGraph
        Dim strSQL As String
        `
        Select Case Index
            Case 0 ` sales by region
                `
                strSQL = "SELECT Region, SUM(Amount) AS SalesTotal FROM "
                strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY 			  ÂRegion"
                `
                objDG.DatabaseName = Data1.DatabaseName
                objDG.SQLSelect = strSQL
                objDG.GraphType = dgPie3d
                objDG.GraphField = "SalesTotal"
                objDG.GraphTitle = "Sales by Region"
                objDG.LegendField = "Region"
                `
            Case 1 ` sales by month
                `
                strSQL = "SELECT Month, SUM(Amount) AS SalesTotal FROM "
                strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY 			  ÂMonth"
                `
                objDG.DatabaseName = Data1.DatabaseName
                objDG.SQLSelect = strSQL
                objDG.GraphType = dgLine
                objDG.GraphField = "SalesTotal"
                objDG.LabelField = "Month"
                objDG.GraphTitle = "Sales by Month"
                objDG.LeftTitle = "($)"
                objDG.BottomTitle = "Months"
                `
            Case 2 ` sales by customer
                `
                strSQL = "SELECT CompanyName, SUM(Amount) AS SalesTotal FROM "
                strSQL = strSQL & "SalesData WHERE CompanyName<>'Budget' GROUP BY 			  ÂCompanyName"
                `
                objDG.DatabaseName = Data1.DatabaseName
                objDG.SQLSelect = strSQL
                objDG.GraphType = dgBar3d
                objDG.GraphField = "SalesTotal"
                objDG.LegendField = "CompanyName"
                objDG.LabelField = "SalesTotal"
                objDG.GraphTitle = "Sales by Company"
                objDG.LeftTitle = "($)"
                objDG.BottomTitle = "Companies"
                `
        End Select
        `
        objDG.ShowGraph
        `
    End Sub

Again, the only real difference is in the SQL statement and the titles, labels, and legends. Save and run the project. Your Sales by Company graph should look like the one in Figure 11.16.
Figure 11.16. Displaying the Sales by Company bar graph.

###
Summary

Today you learned how to use the graph control that ships with Visual Basic in order to create visual displays of your data tables. You learned how to add the control to your project and how to load the graph control with data points, titles, legends, and labels.

Also, you built a graph ActiveX DLL Object Library that you can use to display virtually any dataset in a variety of graph formats. This library lets you save the graph to disk, send the graph to the printer, or copy the graph to the Windows Clipboard for placement in other Windows programs by way of the Paste Special operation.

While building the graph library, you learned how to declare and use enumerated constants to improve the readability of your Visual Basic code.

Finally, you used the new graph library to add three graphs to the CompanyMaster project.

### Quiz

  1. List the advantages of including graphics in your Visual Basic database applications.
  2. Describe the purpose of the NumSets and NumPoints properties of the graph control.
  3. When you are using the predefined constants for graph types, is the following code correct?

>     Graph1.GraphType = graphBar3d

  4. What character separates data points in a series when the QuickData property is used? What character(s) separate a set of points?
  5. Is the following code correct?

>     Graph1.GraphTitle = "Sales for October"

  6. What do the following `DrawModes` constants do?

>     gphBlit
>     gphCopy
>     gphDraw

  7. What is an enumerated type declaration and why is it useful?
  8. Write code to get a count of records in a dataset used for graphing.

### Exercises

Assume that you are an analyst for your regional airport. The Manager of Operations wants information on passenger activity throughout the year. He is an extremely busy individual who does not understand database applications. In order to help him perform his job better, you have decided to create some graphs for him to review.

Perform the following steps in completing this project:

  1. Build a database using Visdata or Data Manager. Name this database `12ABCEX.MDB`.
  2. Build a table in this database and name it Activity. Include three fields: Airline (TEXT 10), Month (INTEGER), and Passengers (INTEGER).
  3. Insert the following records into your table:

  Airline | Month | Passengers
  ABC | 1 | 2562
  ABC | 2 | 4859
  ABC | 3 | 4235
  ABC | 4 | 4897
  ABC | 5 | 5623
  ABC | 6 | 4565
  ABC | 7 | 5466
  ABC | 8 | 2155
  ABC | 10 | 5454
  ABC | 11 | 5488
  ABC | 12 | 5456
  ABC | 9 | 5468
  LMN | 1 | 1956
  LMN | 2 | 2135
  LMN | 3 | 5221
  LMN | 4 | 2153
  LMN | 5 | 2154
  LMN | 6 | 5125
  LMN | 7 | 2135
  LMN | 8 | 5465
  LMN | 9 | 5555
  LMN | 10 | 2536
  LMN | 11 | 2153
  LMN | 12 | 2168
  XYZ | 1 | 10251
  XYZ | 2 | 12123
  XYZ | 3 | 10258
  XYZ | 4 | 12000
  XYZ | 5 | 21564
  XYZ | 6 | 21321
  XYZ | 7 | 14564
  XYZ | 8 | 12365
  XYZ | 9 | 21356
  XYZ | 10 | 21357
  XYZ | 11 | 21321
  XYZ | 12 | 12365

    4. Start a new Visual Basic project that uses the `LIBGRAPH.BAS` module you created today. Build a form and add three command buttons: cmdPie, cmdLine, and cmdBar.
    5. Display the following graphs when each button is pressed:
    cmdPie: Displays a 3D pie chart that shows comparative activity for the first month.
    cmdLine: Displays a line graph that shows total passenger activity by month. Include Passengers as the title on the vertical axis and Month as the title for the horizontal axis.
    cmdBar: Displays a 3D bar graph for the activity of ABC Airlines for the entire year.
    6. Examine the charts you built. Notice how much easier it is to ascertain trends from these graphs than it is from the data entry table in Exercise 3.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.