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.
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!
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:
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.
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 |
| Caption | Menu |
| &File | mnuFile |
| &Save | mnuFileItem(0) |
| &Copy | mnuFileItem(1) |
| - | mnuFileItem(2) |
| 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) |
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.
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.
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.
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.
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).
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.
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.
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
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
For the last project of the day, you add three graphs to the CompanyMaster project:
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 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) |
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.
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.
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.
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.
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.
Graph1.GraphType = graphBar3d
Graph1.GraphTitle = "Sales for October"
gphBlit gphCopy gphDraw
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:
| 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 |
