Visual Basic is oriented to creating forms, not printing reports. Visual Basic never has been noted for built-in printing prowess. The methods associated with the Printer object are drawn from Microsoft's original QuickBASIC and have not been enhanced
significantly since the release of Visual Basic 1.0. Visual Basic 4.0 lacks the integrated report generator available to Access developers. The Professional Edition of Visual Basic 4.0 includes the Crystal Reports custom control, Crystl16.ocx and
Crystl32.ocx (called the Report control in this chapter), and a stand-alone report designer, Crw32.exe (for 32-bit development) and Crw.exe (for 16-bit development), that Microsoft licenses from Crystal Computer Services, Inc., of Vancouver, BC, a Seagate
Software Company.
The latter part of this chapter is devoted to using Crw32.exe and Crystl32.ocx in conjunction with decision-support applications. The Crystal Reports custom control is limited to creating reports from persistent tables in physical databases. The Report
control is not a fully-bound control; you can bind the Report control to a Data control, but you cannot use the Report control to prepare reports directly from Dynaset- or Snapshot-type Recordset objects generated within your application. The graphics
capabilities of the Report control also are quite limited. Report files you create with Crw32.exe only accommodate bitmapped graphics, so printing graphs and charts is a slow process, especially with early PostScript printers such as the Apple LaserWriter
II/NTX. Therefore, this chapter begins by explaining the techniques for printing graphs and charts as Windows metafiles and printing grids using Visual Basic 4.0 code.
One of the purposes of decision-support applications is to reduce the amount of paper flow within an organization. A single decision-support application, such as the Graphs sample application in the preceding chapter, can eliminate the necessity of
printing and distributing paper reports to members of management. Reducing the flow of printed reports lowers operating costs and saves trees. Many managers and executives, however, are addicted to printed reports, so you need to provide the option of
printing the graph and the content of the graph's associated Grid control.
If your client uses fax modems and has Windows 95 or a fax application, such as WinFax PRO, that includes a fax printer driver, your application can print the graph as a fax and then let the user annotate the graph before transmitting the fax. Using
OLE 2.x features, you can make it possible to attach an annotated graph to a Microsoft Mail message. Using OLE 2.x is the subject of Chapter 14, "Integrating Database Front-Ends with OLE 2.1."
Figure 11.1 is an example of printing the graph and grid titled Sales by Product Category for 1994 from the Graphs application of the preceding chapter. When you print a graph with the Graph control, a standard set of alternating patterns substitute
for display colors. The sections that follow describe how to print the graphs, charts, and grids of your decision-support applications with Printing.vbp, an extension of Chapter 10's Graphs.vbp project.
The Printing.vbp project and its associated .frm and .bas files are located in your C:\DDG_VB4\32_bit\Chaptr11 folder. Printing.vbp includes the form module files of Graphs.vbp, plus additional Mdi_prnt.frm and Printing.bas files. Printing.vbp, like Graphs.vbp, expects to find its database file, Graphs.mdb, in your C:\DDG_VB4\32_bit\Chaptr10 folder. If you did not install Graphs.mdb in its default directory, you'll need to change the first line in Sub Main of Graphs2.bas that specifies the location of Graphs.mdb.
The Grid control is one of the most common objects found in decision-support applications. The properties of the Grid control are well-suited for defining a Windows metafile that could be copied into a picture box and then printed. Unfortunately, there
is no command in Visual Basic 4.0 that enables you to copy the Grid control to the Clipboard during run mode or to print the contents of the Clipboard without resorting to declaring and calling functions in the Windows graphic device interface library,
GDI.EXE. (See Figure 11.1.) Therefore, you need to write code to recreate an image of the Grid control as a combination of printed lines, shaded boxes, and type. Creating this printed image is a substantial programming task.
Figure 11.1. Images of a graph and grid printed on a single page.
You can bind an Excel worksheet to a Visual Basic form and use Excel's methods (accessed through Visual Basic for Applications commands) to format and print the worksheet. Refer to Chapter 14 "Integrating Database Front-Ends with OLE 2.1" and to Chapter 15 "Using OLE Automation with Productivity Applications" for more information on using OLE objects in Visual Basic. You also can use Visual Basic 4.0's new support for Visual Basic for Applications to print report definitions stored in an Access 95 .mdb file.
Listing 11.1 is an example of the code required to duplicate the appearance of a Grid control (grdControl) and a printed image at the location (in twips) specified by intTopMargin and intLeftMargin. The sngScale parameter is a scaling factor that
enables you to print the grid at a specified ratio of its dimensions on the display. Parts of the code in Listing 11.1 are specific to the design of the list box for level-2 graphs whose grids contain values derived from crosstab queries. The code for the
PrintShadeLegend subprocedure that adds the patterns to the first column of the grid (see Figure 11.1) appears in Listing 11.2.
All the variables that relate to printing are declared as module-level variables in Printing.bas. The FindWidth() and FindHeight() functions called by PrintGrid sum the values of the ColWidth and RowHeight properties of the grid to return the actual
value of the height and width of the grid. (The Height and Width properties of a Grid control determine the viewable area of the grid, not the total width and height of cells that compose the grid.) The FindFont() function enumerates the printer type
family names to determine if the typeface used in the grid display is available as a printer font. If not, PrintGrid uses the TrueType Arial font family. If you don't have Arial, the first font found in the enumeration is substituted.
Listing 11.1. Code to print a scaled image of a grid control at a specified location on the page.
Sub PrintGrid(grdControl As Control, _
intTopMargin As Integer, _
intLeftMargin As Integer, _
sngScale As Single)
'Purpose: Print grid in picture box
' Substitute shading lines for colors in grid legend
'Note: All variables are declared at the module level
intGridWidth = FindWidth(grdControl)
intGridHeight = FindHeight(grdControl)
strFontName = grdControl.FontName
With Printer
'Use the Printer font corresponding to the display font, if available
If FindFont(strFontName) Then
.FontName = grdControl.FontName
.FontBold = grdControl.FontBold
Else
'If TrueType Arial is available, use it
If FindFont("Arial") Then
.FontName = "Arial"
.FontBold = True
Else
'Otherwise use the first available Printer font
.FontName = Printer.Fonts(0)
.FontBold = True
End If
End If
End With
'Scale the font size
Printer.FontSize = grdControl.FontSize * sngScale
intTextHeight = Printer.TextHeight("0")
Do While intTextHeight > grdControl.RowHeight(0) * sngScale
'Reduce the font size if it exceeds the row height
Printer.FontSize = grdControl.FontSize * 3 \ 4
intTextHeight = Printer.TextHeight("0")
Loop
'Add a block light gray background to the rows, if fixed
If grdControl.FixedRows > 0 Then
'Print the grayed fixed row(s)
Printer.CurrentX = intLeftMargin
If fIsCrosstab Then
'Shading starts in column 2
Printer.CurrentX = intLeftMargin + grdControl.ColWidth(0) * sngScale
End If
Printer.CurrentY = intTopMargin
If fIsCrosstab Then
Printer.Line -Step((intGridWidth - grdControl.ColWidth(0)) * _
sngScale, (grdControl.RowHeight(0)) * _
sngScale), RGB(224, 224, 224), BF
Else
Printer.Line -Step(intGridWidth * sngScale, _
(grdControl.RowHeight(0)) * sngScale), _
RGB(224, 224, 224), BF
End If
End If
If grdControl.FixedCols > 0 Then
'Add a block light gray background to columns > 0, if fixed
Printer.CurrentX = intLeftMargin + (grdControl.ColWidth(0) * sngScale)
Printer.CurrentY = intTopMargin + (grdControl.RowHeight(0) * sngScale)
Printer.Line -Step((grdControl.ColWidth(1) * sngScale * 1.1), _
(intGridHeight - grdControl.RowHeight(0)) * sngScale), _
RGB(224, 224, 224), BF
End If
DoEvents
'Print the grid of the control
Printer.CurrentX = intLeftMargin
Printer.CurrentY = intTopMargin
Printer.Line -Step(intGridWidth * sngScale, intGridHeight * _
sngScale), , B
'Print the horizontal lines
For intRow = 0 To grdControl.Rows - 1
If intRow = 0 Then
intStartHeight = intTopMargin
Else
intStartHeight = intStartHeight + _
grdControl.RowHeight(intRow - 1) * sngScale
End If
Printer.CurrentX = intLeftMargin
Printer.CurrentY = intStartHeight
Printer.Line -Step(intGridWidth * sngScale, 0)
If intRow > 0 And intRow < grdControl.Rows - 1 Then
'Print the shading for the legend boxes in the appropriate rows
PrintHatchLegend grdControl, intTopMargin, intLeftMargin
End If
Next intRow
DoEvents
'Print the vertical lines
For intCol = 0 To grdControl.Cols - 1
If intCol = 0 Then
intStartWidth = intLeftMargin
ElseIf intCol = 2 Then
intStartWidth = intStartWidth + _
grdControl.ColWidth(1) * sngScale * 1.1
Else
intStartWidth = intStartWidth + _
grdControl.ColWidth(intCol - 1) * sngScale
End If
Printer.CurrentY = intTopMargin
Printer.CurrentX = intStartWidth
Printer.Line -Step(0, intGridHeight * sngScale)
Next intCol
DoEvents
'Print the text from the grid
For intCol = 0 To grdControl.Cols - 1
'Get the current width of grid
If intCol = 0 Then
intStartWidth = intLeftMargin
ElseIf intCol = 2 Then
intStartWidth = intStartWidth + _
grdControl.ColWidth(1) * sngScale * 1.1
Else
intStartWidth = intStartWidth + _
grdControl.ColWidth(intCol - 1) * sngScale
End If
grdControl.Col = intCol
For intRow = 0 To grdControl.Rows - 1
'Get the current height of grid
If intRow = 0 Then
intStartHeight = intTopMargin
Else
intStartHeight = intStartHeight + _
grdControl.RowHeight(intRow - 1) * sngScale
End If
grdControl.Row = intRow
'Save the text height and width of caption font
intTextWidth = Printer.TextWidth(grdControl.Text)
intTextHeight = Printer.TextHeight(grdControl.Text)
intCharWidth = Printer.TextWidth("0") / 2
'Set the Y-coordinate of the Printer object
Printer.CurrentY = intStartHeight + _
((grdControl.RowHeight(intRow) * _
sngScale) - intTextHeight) / 2
'Set the X-coordinate of the Printer object according to the
'Alignment property of the grid text
If (intCol < grdControl.FixedCols) Or _
(intRow < grdControl.FixedRows) Then
intAlignment = grdControl.FixedAlignment(intCol)
Printer.FontBold = True
Else
intAlignment = grdControl.ColAlignment(intCol)
If intRow = 0 Then
Printer.FontBold = False
Else
Printer.FontBold = False
End If
End If
With Printer
Select Case intAlignment
Case 0
'Text aligned left
.CurrentX = intStartWidth + intCharWidth
Case 1
'Text aligned right
.CurrentX = intStartWidth + ((grdControl.ColWidth(intCol) * _
sngScale) - intTextWidth) - intCharWidth
Case 2
'Text centered
.CurrentX = intStartWidth + (((grdControl.ColWidth(intCol) * _
sngScale) - intTextWidth) / 2)
End Select
End With
'Print the grid text
If intCol > 0 Or grdControl.FixedCols = 0 Then
Printer.Print grdControl.Text
End If
Next intRow
DoEvents
Next intCol
End Sub
The code in Printing.bas is sprinkled liberally with DoEvents commands. Using DoEvents ensures that the messages your code sends to the printer's device context are executed before the execution proceeds to the next drawing activity. Although the use of DoEvents in 32-bit applications is less critical than in 16-bit applications, if you omit a DoEvents command in a critical location, an unexpected result might occur, such as your application freezing.
If you use a fax printer driver supplied with your fax application, you may need to eliminate the shading of the fixed rows and columns. Some fax printer drivers, such as the driver supplied with WinFax PRO, obliterate shaded text by overprinting the text with solid black.
Listing 11.2 shows the code required to print hatched legends in the first column of the grid for level-2 crosstab graphs. The advantage of creating your own legends, instead of using the legends built into the chart control, is that you can position
the legend symbols and text where you want them. Printing horizontal and vertical hatches is a relatively straightforward process. The code to print diagonal lines is more complex. You can combine the code for northeast-southwest diagonals (as used in the
example) with code that creates northwest-southeast diagonals to create a cross-hatch pattern.
Listing 11.2. Code to print hatched legends for graphs.
Sub PrintHatchLegend(grdControl As Control, _
intTopMargin As Integer, _
intLeftMargin As Integer)
'Print the hatching for the legend boxes in the appropriate rows
Dim intCtr As Integer
'Pitch (twips) between horizontal and vertical shading lines
intPitch = 35
For intCtr = 1 To Int(grdControl.ColWidth(0) * sngScale / intPitch)
'Print horizontal, vertical, or diagonal lines in boxes
Select Case intRow
Case 1, 4, 7
'Horizontal shading lines
Printer.CurrentX = intLeftMargin
Printer.CurrentY = intStartHeight + (intCtr * intPitch)
Printer.Line -Step(grdControl.ColWidth(0) * sngScale, 0)
Case 2, 5, 8
'Vertical shading lines
Printer.CurrentX = intLeftMargin + (intCtr * intPitch)
Printer.CurrentY = intStartHeight
Printer.Line -Step(0, grdControl.RowHeight(intRow) * sngScale)
Case 3, 6, 9
'Print the diagonals across the top of the legend box
Printer.CurrentX = intLeftMargin + (intCtr * intPitch * 1.4)
Printer.CurrentY = intStartHeight
'Note: Adjust the pitch by Sqr(2) to maintain similar density
If (intCtr * intPitch * 1.4) < grdControl.ColWidth(0) Then
'Don't print past the edge of the legend box
Printer.Line -Step(grdControl.ColWidth(0) * _
sngScale - (intCtr * intPitch * 1.4), _
grdControl.RowHeight(intRow) * _
sngScale - (intCtr * intPitch * 1.4))
End If
'Print the corner-to-corner diagonal
If intCtr = 1 Then
Printer.CurrentX = intLeftMargin
Printer.CurrentY = intStartHeight
Printer.Line -Step(grdControl.ColWidth(0) * sngScale, _
grdControl.RowHeight(intRow) * sngScale)
End If
'Print the diagonals down the side of the legend box
Printer.CurrentX = intLeftMargin
Printer.CurrentY = intStartHeight + (intCtr * intPitch * 1.4)
If (intCtr * intPitch * 1.4) < grdControl.RowHeight(intCtr) Then
'Don't print past the edge of the legend box
Printer.Line -Step(grdControl.ColWidth(0) * _
sngScale - (intCtr * intPitch * 1.4), _
grdControl.RowHeight(intRow) * _
sngScale - (intCtr * intPitch * 1.4))
End If
End Select
Next intCtr
End Sub
The PrintGrid and PrintHatchLegend subprocedures do not actually print the lines and text to the printer. All the graphic and typographic objects are elements of the printer's device context (DC), a block of memory reserved for the printed image. The value of the Printer.hDC property is a Windows handle (h, similar to but not the same as a pointer) to the block of device context memory. The printer prints the data in the device context identified by Printer.hDC when you apply the Printer.EndDoc method; then, the DC memory block is released. The code that calls the PrintGrid procedure and actually prints the grid, along with a chart, appears in the following section.
The Extended Graph custom control is an enhanced version of the Graph control of the Professional Edition of Visual Basic 4.0. The Extended Graph control is part of the Graphics Server Graphing Toolkit for Windows. Pinnacle Publishing, Inc.,
distributes and supports Bits Per Second Ltd.'s Graphics Server product in North America. For Pinnacle Publishing's address and telephone number, see Appendix A, "Resources for Visual Basic Database Front-Ends." The Graphics Server adds the
following functions to the Graph32.ocx custom control:
Because Graphics Server's Extended Graph control has different properties than the standard Graph control provided with the Professional Edition of Visual Basic 4.0, the standard Graph control and the Extended Graph control are not compatible. To avoid problems with existing projects that use the standard Graph control, the Extended Graph control's services are provided through a completely different set of .ocx and .dll files. The Graph control of Visual Basic's Professional Edition is provided through Graph32.ocx, which is copied to your Windows\System folder when you install Visual Basic 4.0, Professional Edition. The Extended Graph control, however, is provided by Graphx32.ocx, which is copied to your Windows\System folder when you install Graphics Server.
To utilize the Graphics Server Extended Graph control, you must choose the Tools | Custom Controls menu command in Visual Basic and then click the Browse command button in the Custom Control dialog. Next, select the Graphx32.ocx file in the \Windows\System folder and then click OK. If your project currently has forms that contain Graph controls, Visual Basic will display a message that the Graph control is already in use; the Graphx32.ocx control is added to your project, however, replacing the Graph32.ocx previously bound to your project.
You cannot simply upgrade a project from the standard Graph control to the Extended Graph control by simply changing the Custom Controls reference from the standard Graph to the Extended Graph control. First, the Extended Graph control's properties are not identical to the standard Graph control's properties. Second, Visual Basic retains information about the format of control properties that is not automatically upgraded by simply changing the .ocx that supplies the control's functionality. The result is that you may receive odd compilation errors if you merely change the .ocx that supplies the Graph control—for example, Visual Basic may complain that you are attempting to assign to a read-only property when the property is, in fact, a read-write property.
To upgrade a project from the standard Graph control to the Extended graph control, you should delete all Graph control objects from all forms in your project. Next, use the Browse command button in the Custom Controls dialog to add Graphx32.ocx as a custom control in your project. Finally, re-create all of the graph controls on your project's forms with the Extended Graph control. Following this process will ensure the least frustrating and cleanest upgrade to the Extended Graph control.
If you are developing commercial database applications with Visual Basic 4.0 that require the capability to display and print graphs, you'll find Graphics Server—and the documentation that accompanies it—to be indispensable. Your alternative
to acquiring the Graphics Server's Extended Graph custom control is to use the Windows API to print the graph from a metafile created by the standard Graph control. The PrintGraphGrid subprocedure of Printing.bas includes code for printing graphs with the
Extended Graph control and with the GDI (Graphics Device Interface) functions.
When you print a graph from the metafile created by the graph control by setting DrawMode = 5, the metafile is copied to the printer's device context and a page eject (the equivalent of applying the EndDoc method) occurs. To print another element on
the same page, you need to print a chart or graph without causing a page eject. If you don't have the Graphics Server's Extended Graph control, you need to declare the following GDI functions and user-defined types in the declarations section of your
printing module to print the metafile without the page eject:
Type POINTAPI
x As Long
y As Long
End Type
Type Size
cx As Long
cy As Long
End Type
Declare Function SetMapMode Lib "gdi32" (ByVal hdc As Long, _
ByVal nMapMode As Long) As Long
Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, _
ByVal nIndex As Long) As Long
Declare Function SetViewportOrgEx Lib "gdi32" (ByVal hdc As Long, _
ByVal nX As Long, _
ByVal nY As Long, _
lpPoint As POINTAPI) As Long
Declare Function SetViewportExtEx Lib "gdi32" (ByVal hdc As Long, _
ByVal nX As Long, _
ByVal nY As Long, _
lpSize As Size) As Long
Declare Function PlayMetaFile Lib "gdi32" (ByVal hdc As Long, _
ByVal hMF As Long) As Long
Following is a brief description of each of the Windows functions of the preceding set of declarations (the user-defined type declarations are described with the functions that use them):
Each of the preceding functions is described in the documentation for the Win32 Software Development Kit (SDK) published by Microsoft. The function you need to obtain the hMF handle to the "raw" graph metafile (which is not the same as
Graph.hDC, the handle to the graph's device context) is not documented in the Visual Basic Language Reference, although it is included in the Gswdll32.dll provided with Visual Basic Professional Edition. You need the documentation that accompanies the
Pinnacle/BPS Graphics Server SDK to find the following function:
Declare Function GSGetMF Lib "GSWDLL32.DLL" (ByVal nMode&) As Long
The code in Listing 11.3 prints the graph or chart and the corresponding grid on a single page, in either portrait or landscape orientation, depending on the orientation setting of the default printer. The following list describes the principal
elements of the PrintGraphGrid subprocedure:
Listing 11.3. Code to print the graph and the grid on a laser printer in landscape or portrait orientation.
Sub PrintGraphGrid(fFromSetup As Integer)
'Purpose: Print the graph and grid to a single Printer hDC
Dim hWinMF As Integer 'Handle to the graph metafile
Dim hWinDC As Integer 'Handle to the Printer device context
Dim lpPoint As POINTAPI 'required var to hold original point
'values from GDI SetViewportOrgEx
Dim lpSize As Size 'required var to hold original size values
'from GDI SetViewportExtEx
Dim intResult As Integer 'Result of WinAPI function call
'Important: Following is required to obtain valid hDC
'for PostScript Printers
Printer.Print 'PostScript Printers need to be reset to return hDC
'Save the prior display dimensions
intWidth = chtPrint.Width
intHeight = chtPrint.Height
intLastType = 0
'Set the draw style and mousepointer
chtPrint.DrawStyle = 0
Screen.MousePointer = 11
DoEvents
If Printer.ScaleWidth > 13000 Then
'Landscape orientation in effect (8.5" = 12,240 twips)
sngTop = 0.5
If fIsCrosstab Then
'Dimensions for landscape version of level 2 graph
sngLeft = 0.75
sngWide = 10
sngHigh = 6
sngGridLeft = 0.8
sngGridTop = 6
sngScale = 1.1
Else
'Dimensions for landscape version of level 1 graph
sngLeft = 0
sngWide = 10
sngHigh = 6
sngGridLeft = 1.25
sngGridTop = 7
sngScale = 1.4
End If
Else
'Portrait orientation in effect
sngTop = 1
If fIsCrosstab Then
'Dimensions for portrait version of level 2 graph
sngLeft = 0
sngWide = 7.5
sngHigh = 5.5
sngGridLeft = 0.5
sngGridTop = 7.5
sngScale = 0.9
Else
'Dimensions for portrait version of level 1 graph
sngLeft = 0.25
sngWide = 8
sngHigh = 6
sngGridLeft = 0.65
sngGridTop = 7.5
sngScale = 1.2
End If
End If
'Convert grid units to twips
intGridLeft = Int(sngGridLeft * 1440)
intGridTop = Int(sngGridTop * 1440)
'Draw the grid on the Printer hDC
PrintGrid grdPrint, intGridTop, intGridLeft, sngScale
DoEvents
If fExtendedGraph Then
'Note: The following code applies to BPS Extended Graph only
'Convert all dimensions to twips
intLeft = Int(sngLeft * 1440)
intTop = Int(sngTop * 1440)
intWide = Int(sngWide * 1440)
intHigh = Int(sngHigh * 1440)
'Get the Printer device capabilities (DevCap)
chtPrint.PrintInfo(1) = Printer.hdc 'Get the Printer hDC
chtPrint.PrintInfo(6) = Printer.ScaleLeft 'Get the left margin
chtPrint.PrintInfo(7) = Printer.ScaleTop 'Get the right margin
chtPrint.PrintInfo(8) = Printer.ScaleWidth 'Get the height
chtPrint.PrintInfo(9) = Printer.ScaleHeight 'Get the width
'Set the location and size of the graph
chtPrint.PrintInfo(2) = intLeft 'Set the left margin
chtPrint.PrintInfo(3) = intTop 'Set the top margin
chtPrint.PrintInfo(4) = intWide 'Set the print width
chtPrint.PrintInfo(5) = intHigh 'Set the print height
DoEvents
'Draw the chart on the Printer hDC
chtPrint.DrawMode = 5
Else
'Re-scale the metafile
chtPrint.Width = sngWide * 1440
chtPrint.Height = sngHigh * 1440
chtPrint.DrawMode = 2
DoEvents
'Overscale the viewport extension to expand the graph
If Printer.ScaleWidth > 13000 Then
'Landscape scaling factors
If fIsCrosstab Then
sngWide = sngWide * 1.5
sngHigh = sngHigh * 1.5
Else
sngWide = sngWide * 1.7
sngHigh = sngHigh * 1.7
End If
Else
'Portrait scaling factors
If fIsCrosstab Then
sngWide = sngWide * 1.5
sngHigh = sngHigh * 1.5
Else
sngWide = sngWide * 1.3
sngHigh = sngHigh * 1.3
End If
End If
'Add the graph to the metafile
hWinMF = GSGetMF(0) 'Call Graphic Server DLL to get metafile handle
hWinDC = Printer.hdc 'Return handle of the device context of Printer
intResult = SetMapMode(hWinDC, 7) 'MM_ISOTROPIC mapping mode
intLeft = Int(GetDeviceCaps(hWinDC, 88) * sngLeft)
intTop = Int(GetDeviceCaps(hWinDC, 90) * sngTop)
intWide = Int(GetDeviceCaps(hWinDC, 88) * sngWide)
intHigh = Int(GetDeviceCaps(hWinDC, 90) * sngHigh)
'Set viewport origin (left and top margins)
intResult = SetViewportOrgEx(hWinDC, intLeft, intTop, lpPoint)
'Set viewport extent (width and height)
intResult = SetViewportExtEx(hWinDC, intWide, intHigh, lpSize)
intResult = PlayMetaFile(hWinDC, hWinMF)
'Printer.ScaleMode = 1
End If
DoEvents
'Important: Both of these instructions are required
Printer.NewPage
Printer.EndDoc
PrintCancel:
'Reset the chart back to normal display mode
ResetChartNormal
Screen.MousePointer = 0
DoEvents
End Sub
The values that set the margins for and dimensions of the graphs in Listing 11.3 are optimized for use with the GDI function calls. You may need to change these values to properly orient the graph or chart on the page when you use the Extended Graph control's PrintInfo() array. You also may need to set the FontSize property of the labels and graph title to different values when you use PrintInfo().
A landscape version of the Year-to-Date Sales for 1994 graph, printed using the GDI function calls, appears in Figure 11.2. Figure 11.1, an area chart with portrait orientation, also was printed using the GDI function calls.
Figure 11.2. The Year-to-Date Sales for 1994 graph printed in landscape mode with the GDI function calls.
You can print the graph or chart and the grid with specialty printer drivers, such as those drivers that print to a fax application or that create an image you can paste into a document. There are a variety of commercial fax and document management
applications for Windows 3.x and Windows 95 that include their own proprietary printer driver.
The Microsoft Fax printer driver saves the fax in the form of a .DCX file. The .DCX format is a variant of the CCITT Group 4 TIFF file format that enables you to store multiple-page faxes in a single file. CCITT Group 4 compression is much more efficient than CCITT Group 3 compression. When you are choosing a document-management or fax application, pick a product that supports both CCITT Group 4 and .DCX formats.
If you installed the Microsoft Fax printer driver supplied with Windows 95, you can send the printed output as a fax. If you use the printer drivers supplied with products such as Delrina's WinFax PRO or Keyfile's Doceo, you can annotate the fax or
document before you send it. Doceo enables you to embed the annotated chart or graph in a Microsoft Mail message as an OLE object.
You need to modify the structure of the PrintGraphGrid subprocedure when you use most specialty printer drivers. Normally, the Printing application closes the print preview window (frmMDIPrint, discussed in the next section) and returns to the graph display window (frmMDIGraph) after Print Manager creates the spooled print file. You need to halt execution of your application after the line that applies the EndDoc method so that the window of the fax- or document-management application remains on top of the print preview window. Substitute a Close command button for the Print button; the Click event of the Close button executes the ResetChartNormal subprocedure.
The DisplayPrintedGraph and DrawGrid subprocedures create an image of the graph or chart in a Graph control (chtGraph) and an image of the grid in a picture box (picGrid) on the print preview form, frmMDIPrint. The print preview window for the chart
and grid titled Sales by Category for 1994 appears in Figure 11.3. The code of DisplayPrintedGraph scales the graph to fit the available display space. DrawGrid, which is identical in all respects (except for the substitution of the picGrid control for the
Printer object) to the PrintGrid subprocedure, displays a thumbnail image of the grid.
Figure 11.3. The print preview window of the Printing sample application.
You can change the value of the FontSize property of chtGraph to reduce the size of the chart title and the labels. If you reduce the value of the FontSize property when you draw chtGraph, you need to change the value of FontSize back to the normal settings during execution of the PrintGraph subprocedure.
The frmMDIPrint form also contains a Print common dialog control (cdgPrint) that enables you to set the number of copies to print and provides access to the Print Setup common dialog. Choose the File | Print Setup menu command to display
cdgPrint. The PrintGraphGrid subprocedure does not use the Copies property of cdgPrint, but you can add a Do While intCopies <= cdgPrint.Copies. . .Loop structure to PrintGraphGrid if you want multiple-copy capability. Access to the Print Setup common
dialog is necessary for changing from portrait to landscape orientation and for choosing the default printer (if you use more than one printer driver). Visual Basic's Printer object always refers to the default printer.
If you want to print reports directly from the virtual tables of your application's Recordset objects, you need to write a substantial amount of code or use a third-party data-aware custom control that includes a printing method. The sections that
follow describe how to write Visual Basic code to print data from a Recordset.
Visual Basic 4.0 code that uses the Printer object to print data from the fields of Recordset objects is quite similar to the xBase code you write to print custom reports. The structure of the code to print data from a Recordset object is quite similar
to that used to print the text from the grid in the PrintGrid subprocedure of Printing.bas. The generalized Visual Basic metacode to print a report from a Recordset object is as follows:
Printer.Print ReportHeading 'Print the report heading
Do Until Recordset.EOF 'Loop through all of the _
records
Printer.CurrentX = LeftMargin 'Set the left margin
Printer.CurrentY = HeadTopPosition 'Set the top margin for the _
page heading
Printer.Print PageHeading 'Print the page heading
Printer.CurrentY = DataTopPosition 'Set the top margin for the _
data
LineCounter = 1
Do Until LineCounter => MaxLines 'Loop through a page of records
Printer.CurrentX = LeftMargin
'Print the fields of the recordset in sequence
For FieldNumber = 1 To Recordset.Fields.Count
Printer.CurrentX = Printer.CurrentX + PrintWidth(FieldNumber)
Printer.Print Format(Recordset.Field(FieldNumber).Value, _
Format(n))
'Carry totals forward
If FieldNumber = n And Not _
IsNull(Recordset.Field(FieldNumber(n)) Then
'Null values added to the sum give you a Null sum
SumFieldNumber(n) = SumFieldNumber(n) + _
Recordset.Field(FieldNumber(n)).Value
End If
Next FieldNumber
'Move to the next line
Printer.CurrentY = Printer.CurrentY + LineHeight
Recordset.MoveNext
LineCounter = LineCounter + 1
Loop
'Print the page number as a footer
Printer.Current.X = FooterLeftMargin
Printer.Current.Y = FooterTopMargin
Printer.Print PageCounter
'Issue a page eject
Printer.NewPage
PageCounter = PageCounter + 1
Loop
'Print one or more totals
Printer.Print.Format(SumFieldNumber(n), "FormatSum")
'Eject the last page and release Printer.hDC
Printer.EndDoc
Metacode is pseudo-code that describes how you write the actual source code, rather than the code itself. Metacode usually is written with statements that are independent of the language in which the source code is to be written. In this book, metacode examples use Visual Basic 4.0 reserved words and keywords. (Metacode is not assigned listing numbers in this book. Listing numbers are reserved for code examples that are included in sample Visual Basic 4.0 applications on the accompanying CD-ROM.)
If you use arrays to specify field-printing widths (PrintWidth) and formats (Format), you can write a standard subprocedure that prints almost any conventional report in the form of a listing of the content of the Recordset. Using an
array to set and get the values of the top and left margins required for printing report headers, report footers (grand totals), page headers, page footers, and the list of data also is a useful method for making your list-printing subprocedure more
universal.
Writing the code to generate group subtotals by data category is a bit more complex, but is not an overwhelming challenge. First, you sort the Recordset by data category. Then you add a secondary loop inside the Do Until
Recordset.EOF. . .Loop structure that compares the value of the data category for the current record with the saved value of the data category for the prior record. When the two values differ, you print the subtotal for the category.
You can use look-ahead techniques to count the number of records for a category to see if the entire category will fit on a single page. Save the record pointer as the value of a Bookmark variable before you execute the count loop. If the next category
listing does not fit on a single page, you can apply the Printer.NewPage method to start printing records in the new data category on the next page. After you count the records in the data category, you return the record pointer to its original
position with the saved Bookmark value and then print the data contained in the set of records for the data category. Reports that use look-ahead techniques (the KeepTogether property of groups in Access reports) are called two-pass reports.
Crystal Computer Services, Inc., is the publisher of Crystal Reports 4.0 and Crystal Reports Pro 4.5. Crystal Reports Pro is a highly regarded stand-alone report generator designed for use with any database application (not just applications developed
with Visual Basic 4.0). Crystal Reports for Visual Basic, included with the Professional Edition of Visual Basic 4.0, provides most of the features of Crystal Reports Pro 4.5. The principal benefit of upgrading from Crystal Reports for Visual Basic to
Crystal Reports Pro 4.5 is the capability of your Visual Basic 4.0 applications to control formatting and other properties of reports via function calls to the report engine DLL. Crystal Reports Pro 4.5 also enables you to "compile" report files
into an executable Windows application that you can launch from an icon.
The stand-alone nature of Crystl32.ocx is demonstrated by the report custom control's lack of a DataSource property. Instead, you specify the name of the report file you created as the value of the ReportFileName property. The purpose of the
Crystl32.ocx custom control is to enable your Visual Basic 4.0 application to determine when (and, to some extent, how) the report that is described in the report file is printed. Thus, printing a Visual Basic 4.0 report is a two-step process: first you
create a report (.rpt) file with the Crystal Reports for Visual Basic report writer application (Crw32.exe), and then you add the report custom control (Crystl32.ocx) to a form contained in your application. The sections that follow describe how to
integrate report-writing functions in your Visual Basic 4.0 database applications.
The version of Crystal Reports that shipped with Visual Basic 4.0 at the time this chapter was written (late 1995) is apparently a quick port of the previous Crystal Reports version to the Windows 95 32-bit environment. (In fact, report definitions printed from Crw32.exe bear the title "Crystal Reports for Visual Basic 3.0 - Report Definition.") The Crystal Report custom control provided with Visual Basic 4.0 does not support Jet 3.0 database file formats; you can only use the Crystal Report custom control to print reports based on data stored in Jet 2.5 (and earlier) database formats. When using Crystal Reports you must use the Jet 2.5/3.0 DAO Compatibility library instead of the Jet 3.0 DAO library. Unfortunately, Crystal Reports Pro 4.5 does not include support for Jet 3.0 databases, either.
Because the Crystal Reports custom control cannot utilize data stored in Jet 3.0 databases, the Printing.vbp project uses a database in Jet 2.0 format. The Printing.mdb database is in the \DDG_VB4\32_bit\Chaptr11 folder of the CD-ROM accompanying this book. If you installed the sample files for this chapter in another directory, you must change the directory path to the database in the Main procedure of Graphs2.bas.
Creating a report file requires that you take the following steps:
Figure 11.7. Creating a Group section in a report.
Once you've created the Group section(s) for your report, you need to add the fields for the Detail section. In this sample report, the ProductID, ProductName, QuantityPerUnit, and UnitPrice fields of the Products table provide the Detail section
column. You need to adjust the width of the fields to reduce the width of the report to eight inches or less. The Products and Categories tables are joined by their common field, CategoryID. To create the Detail section and join the Products table to the
Categories table, follow these steps:
Close Crystal Reports for Visual Basic. You use the Products.rpt file for the rptProducts report custom control in the Printing sample application described in the next section.
Figure 11.13. The final design of the Product List by Category report.
Figure 11.14. The Northwind Traders Product List by Category report.
Crystal Reports for Visual Basic provides limited control of the formatting of reports. You cannot, for example, change the DrawWidth property of the underline attribute of the column header labels. Spacing between rows of your report is limited to integer multiples of the line height for the font you select. (You can control spacing by adding a text box containing only spaces with the desired spacing height set by the font size of the text box.) To precisely control the spacing of the sections and rows of your report, you need Crystal Reports Pro 4.5. (Keep in mind, however, that Crystal Reports Pro 4.5 does not support Jet 3.0 databases.)
Crystal Computer Services, Inc., not Microsoft Corp., prepared the documentation for the report custom control that appears in the Professional Features book of the Visual Basic 4.0 documentation. The idea of the exogenous origin of the
documentation is supported by the fact that the Crystal Reports documentation is provided as a separate book, Crystal Reports for Visual Basic User's Manual, and that descriptions of the Crystal Reports custom control are inconsistent with the
styles used throughout the rest of the Visual Basic 4.0 documentation—both printed and online. The documentation for each of the OLE Controls that are supplied by third parties other than Crystal Computer Services, Inc., precisely follows the style of
the books Professional Features and Language Reference.
The Report control provides three basic capabilities, as follows:
| PrintFileType | File Type | Comments |
| 0 | Record (.ASC) | Fixed-width text file suitable for importing to client/server and mainframe databases such as DB2. |
| 1 | Tab-separated (.TSV) | Fields are separated by tab characters, and records are separated by new-line pairs. Character fields are enclosed within double-quote (Chr$(34)) characters. |
| 2 | Text (.TXT) | The equivalent of printing the report to the Generic/Text Only (TTY) printer driver to the FILE device. |
| 3 | DIF (.DIF) | Data Interchange Format for importation into spreadsheet applications that support DIF format. (Some do.) |
| 4 | Comma-separated (.CSV) | Character fields are enclosed within double-quote characters, and each field is separated by a comma. (xBase SDF format; use to export to Excel and Lotus 1-2-3.) |
| 5 | Reserved | This format is reserved for unknown reasons, perhaps for future printing in Excel BIFF format (which did not make it into the final version of Crystal Reports for Visual Basic that shipped with Visual Basic 4.0). |
| 6 | Tab-separated text (.TST) | Fields are separated by tab characters, and records are separated by new-line pairs. Quotation marks are not used to identify character fields. (Use to export to Word for Windows tables.) |
The Printing sample application includes a new menu choice, Reports, that enables you to select one of the three printing options in the list preceding Table 11.1. When you choose an option, a fly-out submenu lists the report
that you can print. (Only the Products by Category report is offered as a menu choice at this point in the book.) The sections that follow describe the programming techniques you need to employ to take full advantage of each of the three Destination
options for the Report control.
You may receive some rather cryptic error messages at runtime if the disk drive that holds your Windows temporary directory (usually C:\Windows\Temp) does not have enough free space for the Crystal Reports custom control to create its needed temporary files while it generates printer output to a file, the printer, or to a window. Error messages such as Unable to load printing resource and Missing or out-of-date export DLL that occur while using Crystal Reports custom control print features all point to insufficient room on the disk drive for temporary files. The amount of temporary space required depends on the amount and type of data included in the report. The Printing.vbp application of this chapter, for example, will produce the Missing or out-of-date export DLL error message when printing to a file and there is less than 1MB of free disk space available for temporary files.
To print one copy of a report to the default printer does not require that you display a form. However, you need to display the Print common dialog if you want to give the user the opportunity to print multiple copies or only specific pages of the
form. The code in Listing 11.4 displays the common Print dialog and then prints the number of copies the user specifies when the OK button is clicked. If the user clicks the Cancel button, the print operation is canceled with a message box.
Listing 11.4. The code required to print a report.
Private Sub mnuReportsPrintProdByCat_Click()
'Purpose: Print the Products by Category report
On Error GoTo PrintReportError
With frmMDIReports
'Display the print preview form without the window
.Show
.Cls
.Caption = "Printing Products by Category Report"
'Send report to printer
.rptReports.Destination = 1
'Display the Print common dialog to set up printer
.cdgPrint.CancelError = True
.cdgPrint.Copies = 1
.cdgPrint.FromPage = 1
.cdgPrint.ToPage = 1
.cdgPrint.Flags = cdlPDPrintSetup
.cdgPrint.ShowPrinter
DoEvents
'Verify that printer has been set as default printer
If .cdgPrint.PrinterDefault Then
'Print the report with the number of copies as specified
.rptReports.CopiesToPrinter = .cdgPrint.Copies
.rptReports.Action = 1
Else
MsgBox prompt:="The printer you use must be " & _
"selected as the default printer.", _
buttons:=vbCritical, Title:="Printer Error"
GoTo BailOut
End If
End With
DoEvents 'Let the print occur
GoTo BailOut
PrintReportError:
MsgBox prompt:=Err.Description, _
buttons:=vbCritical, _
Title:="Report Printing Error"
BailOut:
frmMDIGraph.Show
Unload frmMDIReports
DoEvents
End Sub
To create a file in a format that you can import into word processing and spreadsheet applications without writing a substantial amount of code, you can print a report to a file in one of the delimited formats supported by Crystal Reports for Visual
Basic. The sample application supplied with Crystal Reports for Visual Basic uses a combo box to select the file type of the saved report file. A more efficient method is to create a list of the available file types that appear as choices in the Save File
as Type combo box of the Save As common dialog. Listing 11.5 shows the Visual Basic code necessary to let the user choose a filename and file type from the Save As common dialog. Figure 11.15 illustrates the Save As common dialog generated by the code in
Listing 11.5.
Listing 11.5. The code to print a report to a file specified with the Save As common dialog.
Private Sub mnuReportsFileProdByCat_Click()
'Purpose: Print the Products by Category report to a file
' Specify the file type in the SaveAs common dialog
Dim strFilter As String
strFilter = "Record (*.asc)|*.asc|Tab-separated (*.tsv)|*.tsv|" & _
"Text (*.txt)|*.txt|Spreadsheet (*.dif)|*.dif|" & _
"Comma-separated (*.csv)|*.csv|(Reserved)|*.???|" & _
"Tabbed Text (*.tst)|*.tst"
On Error GoTo PrintFileError
With frmMDIReports
'Display the print preview form without the window
.Show
.Caption = "Printing Products by Category Report to File"
'Send report to printer
.rptReports.Destination = 2
'Display the FileSave common dialog to set up printer
.cdgPrint.CancelError = True
.cdgPrint.DialogTitle = "Save Report to File"
.cdgPrint.Filter = strFilter
.cdgPrint.FilterIndex = 5
.cdgPrint.ShowSave
DoEvents
'Determine the file type from the extension chosen
Select Case LCase(Right(.cdgPrint.filename, 3))
Case "asc"
.rptReports.PrintFileType = 0
Case "tsv"
.rptReports.PrintFileType = 1
Case "txt"
.rptReports.PrintFileType = 2
Case "dif"
.rptReports.PrintFileType = 3
Case "csv"
.rptReports.PrintFileType = 4
Case "tst"
.rptReports.PrintFileType = 6
Case Else
.rptReports.PrintFileType = 5
End Select
'Test to see if filename is long enough and
'if Reserved file type selected
If Len(.cdgPrint.filename) > 5 And _
(.rptReports.PrintFileType <> 5) Then
'Print the report with the number of copies as specified
.rptReports.PrintFileName = .cdgPrint.filename
.rptReports.Action = 2
Else
MsgBox prompt:="Invalid file name '" & _
.cdgPrint.filename & "' or type.", _
buttons:=vbCritical, Title:="Print to File Error"
GoTo BailOut
End If
End With
DoEvents 'Let the print occur
GoTo BailOut
PrintFileError:
MsgBox prompt:=Err.Description, _
buttons:=vbCritical, _
Title:="Report Printing Error"
BailOut:
frmMDIGraph.Show
Unload frmMDIReports
DoEvents
End Sub
Figure 11.15. The common Save As dialog with a filter added to choose the file type.
The output format of the text files created in any format by the Report control when you group records based on a join between tables is unorthodox at best. A list of all the field names precedes the field values in each row. You'll need to import the file into a spreadsheet application and delete the rows containing the field names before you can make use of the file in the majority of your applications.
Creating a print preview window on an MDI child form requires that you set the value of the WindowParentHandle property of the report control to the window handle (hWnd property) of your MDI child form. The code that appears in Listing 11.4 (shown
previously) sizes the Report control to occupy the entire area of the frmMDIReports form when you show the form. The Report control does not have a ScaleMode property; the scale of Report controls always is in pixels. The ScaleMode property of the forms
and controls of the Printing application is in twips. Therefore, you need to obtain a conversion factor between twips and pixels for the display mode in effect to set the height and width of the report control. In the case of a 640x480-pixel (VGA) display,
the ratio of twips to pixels (sngTPP) is 15.
The UnloadReportsForm subprocedure (in Printing.vbp) unloads a prior instance of the form and Report control if the form has been opened previously. Unloading the form is necessary to prevent an additional instance of the Report control from appearing each time you show the form. The multiple instances are only apparent if you open the frmMDIReports window with the frmMDIToolbar window in normal mode, maximize the frmMDIToolbar, and then open the frmMDIReports window again. If you open frmMDIReports a sufficient number of times without calling the UnloadReportsForm subprocedure, you eventually run out of GDI resources and incur an Out of memory error.
Listing 11.6 shows the Visual Basic code you need to make the Report control's print preview window appear as an MDI child window.
Listing 11.6. The code required to cause the Report control's window to emulate an MDI child window.
Sub ShowPreviewWindow(strCaption As String)
'Purpose: Set MDI child window and size Crystal Reports' window
Dim sngTPP As Single 'Twips per pixel (ratio)
Dim intWidthT As Integer 'Form width in twips
Dim intWidthP As Integer 'Form width in pixels
'Set the hourglass pointer
Screen.MousePointer = 11
'Unload a previous version of the form, if one exists
UnloadReportsForm
'Show the Crystal Reports control and set Destination to window
frmMDIReports.Show
frmMDIReports.rptReports.Destination = 0
'Set the hWnd property of the print preview window to
'that of frmMDIReports
frmMDIReports.rptReports.WindowParentHandle = frmMDIReports.hWnd
frmMDIReports.rptReports.WindowTop = 0
frmMDIReports.rptReports.WindowLeft = 0
If frmMDIReports.WindowState = 0 Then
'Size the normal MDI child window
frmMDIReports.Left = 0
frmMDIReports.Top = 0
frmMDIReports.Height = frmMDIToolbar.ScaleHeight
frmMDIReports.Width = frmMDIToolbar.ScaleWidth
End If
'Get the form width in twips
intWidthT = frmMDIReports.ScaleWidth
'Get the form width in pixels
frmMDIReports.ScaleMode = 3
intWidthP = frmMDIReports.ScaleWidth
'Reset the ScaleMode back to twips
frmMDIReports.ScaleMode = 1
'Calculate twips/pixel for the current display mode
sngTPP = intWidthT / intWidthP
'Set the width and height of the window to fit the client area
frmMDIReports.rptReports.WindowWidth = _
frmMDIReports.ScaleWidth / sngTPP
frmMDIReports.rptReports.WindowHeight = _
frmMDIReports.ScaleHeight / sngTPP
'Set the caption
frmMDIReports.Caption = "Preview " & strCaption & " Report"
frmMDIReports.rptReports.WindowTitle = "Print Preview"
'Now show the window
DoEvents
frmMDIReports.rptReports.Action = 1
Screen.MousePointer = 0
End Sub
Figures 11.16 and 11.17 show the result of executing the code in Listing 11.6 for the Products by Category report. The default display mode (see Figure 11.16) is an image of the report in the approximate scale at which it is printed. When you click the
Magnifying Glass button, a zoomed out image appears. Clicking the Magnifying Glass button once produces a view showing the entire page width, and clicking the Magnifying Glass button twice displays the entire sheet (see Figure 11.17); clicking the
Magnifying Glass button a third time cycles back to the original view. When you click the Print button (the one with the printer symbol), the Print common dialog is displayed before the report is printed. The VCR buttons set the page pointer when you
display multipage reports.
The Crystl32.ocx control does not recognize any events. When you double-click the report control icon in design mode, the code window for the form appears. Therefore, you cannot use the DblClick event handler to close the print preview form because rptReports does not recognize the double-click (or any other) user-initiated event. You need to click one of the toolbar buttons to close the form and display the corresponding graph. You could add a Close command button to frmMDIReports, but it then would be necessary to make the print preview window smaller so that the button is visible.
Figure 11.17. The print preview window with the view zoomed.
The lack of an integrated report generator in Visual Basic 4.0 requires that you write a substantial amount of code to print the graphs and grids. Graphs and grids are two of the most common components of decision-support applications, and both are
almost impossible to print with the Crystal Reports custom control of Visual Basic 4.0. The Printing application described in this chapter is intended to demonstrate the structure of the code you need to print graphs, charts, and grids. A demonstration of
the capabilities of the Crystal Reports stand-alone report generator and unbound control completed the chapter.
This chapter completes Part III, "An Introduction to Database Front-End Design," of this book. Up to this point, the subject matter of Database Developer's Guide with Visual Basic 4 has been introductory in nature. The chapters
in Section IV, "Advanced Programming with Data Access and OLE Objects," expand on the topics presented in Sections I through III and introduce some of the more complex issues of database-application design with Visual Basic 4.0, such as using OLE
for interprocess communication.
