It's likely that your first production-database application that uses Visual Basic 4.0 will be used for decision-support purposes. Industry sources estimate that decision-support applications constitute 75 percent or more of all of the database
applications in use today. When you create a decision-support application for use with an existing relational database, you don't need to be concerned with database design, maintaining referential and domain integrity, or concurrency problems. (You do,
however, need to take consistency issues into account if you are summarizing data.)
The purpose of a decision-support application is to transform raw data into useful information. Your primary task is to provide the users of your application with a simple, straightforward method of obtaining the data they need. This chapter begins by
discussing how to organize the data you're converting to useful information with decision-support applications. The chapter also discusses designing the user interface to make your application easy to understand, together with examples of forms that
display information in graphical and tabular format. Finally, the chapter reviews examples of the Visual Basic 4.0 code needed to create the graph, chart, and grid objects that display the selected information to the user.
The objective of most of today's decision-support applications is to replace printed reports with on-screen presentations of information. A successful decision-support application supplies "Information at Your Fingertips" (a
Microsoft-trademarked corporate slogan). For mid-level managers and below, the video display unit (VDU) of a PC is the most common presentation platform. At the vice-presidential level and higher rungs in the corporate ladder, the information often is
displayed on large-screen or projection video systems acting as a VDU for one PC in a conference setting.
Typical relationships of data sources and information systems for a typical manufacturing company appear in the hierarchical structure shown in Figure 8.1. Data-entry and transaction-processing activities primarily are confined to the lowest level of
the hierarchy: operational databases. (The operational database level of the hierarchy often is called "the trenches.") The levels above the operational databases involve little or no data entry; these upper levels in the hierarchy are referred
to as information systems (IS) or management information systems (MIS). The diagram shown in Figure 8.1 divides the information systems category into functional information systems at the directorate and vice-presidential level, and planning and
forecasting information systems that are used by top management and corporate staff.
Figure 8.1. The hierarchy of information systems and databases for a manufacturing firm.
Depending on the size of the company and the type of computer hardware the firm uses, the operational databases may be located in a centralized mainframe system or distributed across several database servers in a client/server RDBMS environment.
Combinations of mainframe and client/server environments are common for firms that are in the process of downsizing and distributing their operational databases. A small manufacturing firm might have all its operational databases in the form of multiple
.DBF files that reside on a single file server.
If you're developing database applications for a firm with $10 million or more in annual sales, be prepared to deal with the connectivity issues raised by a wide variety of network operating systems and database management systems, including legacy (a
synonym for obsolete among proponents of client/server RDBMSs) network and hierarchical DBMs. It is not uncommon for developers of database front-ends to spend more time solving connectivity problems, both DBM- and network-related, than they spend
designing, coding, and testing the entire front-end application.
You also may need to integrate data from online data sources into your database front-end application. Credit information from Dun and Bradstreet and TRW, stock prices from the Dow Jones News Service, and real estate transaction data from Damar are
just a few of the uses for the data communication features of Visual Basic's Mscomm32.ocx customer control. Another data source that you may need to incorporate in your applications is the CD-ROM. Virtually all the 1990 census data is now available from
the U.S. Bureau of the Census in .DBF format on CD-ROMs, and many companies publish phone, business, and other directories on CD-ROMs.
Before the advent of the RDBMS and client/server computing technology, the principal source of functional information, as well as planning and forecasting information, was a multitude of printed reports. Each report was the product of a batch operation
that required a program, usually written in COBOL, to execute the embedded SQL or other instructions that create a formatted report. In many cases, reports were created with more than the optimal level of detail because of a lack of programming resources
to write, test, and deploy production programs to summarize the data. The capability for users of client applications to create their own ad hoc queries with whatever degree of detail they desire is the driving force behind the front-end application
generator market.
Unless you're dealing with data that has been rolled up (the subject of the next section), your decision-support, front-end application accesses tables in operational databases. The level of detail you provide in a decision-support application usually
varies inversely with the position of the users in the organizational hierarchy. As you progress upward in the corporate "food chain," tabular data gives way to graphs and charts for trend analysis, and the frequency of reporting slows from daily
to monthly. The list that follows describes the three basic categories of decision-support applications:
Figure 8.2 is a diagram that shows the layers of information that constitute typical marketing-decision support applications corresponding to the three categories in the preceding list. The executive summary for the vice-president of marketing
consolidates sales of all products in all regions. The functional summary for the director of sales includes sales of a particular product line in all regions. The operational data viewed by the regional sales manager reports sales in one region for all
products.
Figure 8.2. Levels of information detail in summary and operational decision-support applications.
One of the principal objections of management personnel to MIS reports, whether displayed online or in the form of computer printouts, is excessive detail. If you use a 9-point MS Sans Serif font with a tightly spaced Visual Basic grid, you can display
several times as much data on a VDU as is possible with a character-based DOS application. You have similar potential with today's laser printers. For management, it is the aggregated data that is important, together with exception highlighting. If you
need to provide one or two levels of detail behind the summary data, first offer the detail behind the exceptions and then make additional detail information an option.
Impatience is another personal trait that increases with the level of authority and responsibility in an organization. Operatives in the trenches may be satisfied with an application that takes a minute or more to present a screen of data—a 3270
terminal on an overtaxed mainframe may take several minutes to update a session. If your summary queries (especially crosstab queries) need to traverse tens of thousands of records containing line items for a year's collection of invoices, you are certain
to face an unsatisfied client when you deliver your production front-end.
The traditional (and still the best) approach to maintaining adequate performance for time-series, decision-support applications is to consolidate time-based detail data into new tables. This process is called rolling up data, which should be
familiar to all mainframe COBOL programmers. Consolidating data, other than creating monthly and yearly rollups for accounting purposes, has been relatively uncommon in PC-based database applications. Intel Pentium PCs running at clock speeds of 90 MHz or
greater have now become the most popular CPUs for database servers, and the cost of fixed disk storage broke the $250-per-gigabyte barrier in late 1995. Therefore, the economic disincentive of replicating data is minimal.
Although rolling up data violates the no-duplicated-data rule for relational databases (because rolled up data is derived from existing tables), you'll probably want to aggregate data when your summary queries need to process more than a few thousand
records. Following are the guidelines for how and when to roll up data:
In the typical information-system hierarchy illustrated in preceding Figure 8.1, rollups of sales, manufacturing, purchasing, finance, and human resources operational databases occur at the director level. Another rollup further consolidates data for
the vice-presidents of marketing, operations, and administration.
The performance improvement you can achieve by rolling up data enables you to design Visual Basic decision-support applications that replace slide shows created with Microsoft PowerPoint or similar Windows presentation applications. Using a
presentation application to export and re-create graphs and tables in the form of slides is an inefficient, time-consuming process. Many firms now prepare monthly or weekly presentations by transferring summary data to presentation slides. A well-designed
Visual Basic decision-support application can return its development cost many times by eliminating the data import and conversion steps. Your Visual Basic presentation application needs to be totally bulletproof, and you'll probably want to store the
rolled up data on a local fixed disk to avoid the embarrassment that attends the appearance of blank screens or messages that read Unable to connect to server during the presentation.
If your audience expects the flashy graphics available from a presentation management tool, you can take advantage of Visual Basic's OLE Automation client capabilities to program objects exposed by 32-bit PowerPoint for Windows 95 version 7.0. Chapter 15, "Using OLE Automation with Productivity Applications," describes how to use Visual Basic 4.0 to program objects exposed by the member applications of Office 95.
Once you have your data sources identified, you need to implement a query strategy. The sections that follow discuss some of the issues you need to resolve before you commit to a particular strategy to obtain the Recordset objects on which to base your
decision-support applications.
Decision-support applications that consist of a fixed feature set are likely candidates for the use of persistent QueryDef objects. You only can store QueryDef objects in Jet databases, so if you're planning to roll up data from client/server or
mainframe databases, a Jet database is the most appropriate database type to store the rolled up data.
You can pretest your SQL statements by using Visual Basic 4.0's VisData application to create and store QueryDef objects for rollup or direct queries. VisData's grid control gives you a chance to preview the result of your query and to fine-tune the
SQL statement that creates the QueryDef. Once you have the SQL statement optimized, you can substitute parameters for the independent variables you set to return records for specific regions, products, or time spans.
Alternatively, you can write the Jet SQL statement for a query; then pass the value of the SQL statement as a String variable to the strSource argument of a dbName.OpenRecordset(strSource, intOptions) statement in
your code. The SQL statements of persistent QueryDef objects are stored after parsing and optimizing by the Jet database engine. The SQL statements you pass to the OpenRecordset method need to be parsed and optimized before being executed. In most cases,
using a parameterized QueryDef object for rollups is faster than the OpenRecordset method with a SQL statement.
To optimize performance of your application, you need to test both persistent and impersistent versions of your queries. VisData's query execution timing message is useful to compare performance using a QueryDef object against executing an SQL
statement dynamically. (Make sure the Preferences | Show Performance Numbers command in the VisData application's menu is checked in order to display VisData's execution timing message.)
If you're only changing one or two elements of a query, such as records-selection criteria in a WHERE clause, consider using a parameterized QueryDef object. Using a parameterized QueryDef object in a Jet database is similar to passing arguments to
stored procedures in SQL Server databases. The Jet SQL syntax that is required for you to specify that you intend to pass one or more parameters to the SQL statement of a QueryDef object is as follows:
PARAMETERS ParamName1 DataType1[, ParamName2 DataType2 [, ...]];
Parameters (ParamName#) and their corresponding Jet SQL data types (DataType#) are passed in comma-separated pairs to a named parameter placeholder in the Jet SQL statement, as in
PARAMETERS State Text, DateStart DateTime, DateEnd DateTime;
SELECT * FROM Orders
WHERE [ShipRegion] = State
AND [OrderDate] BETWEEN DateStart AND DateEnd;
The comma that separates the pair of parameter names and the Jet SQL parameter data type is required. The semicolon (;) that terminates the PARAMETERS statement also is necessary. The values of the parameter names you pass cannot match field names
contained in any of the tables that participate in the query. If you insist on using illegal punctuation in your parameter name value (such as spaces or hyphens), enclose the parameter names within square brackets.
The permissible values for the SQLType# component of the parameter are listed in the Jet SQL Parameter Type column of Table 8.1. Table 8.1 lists the conventional field data type name, the Jet SQL parameter data type, the Variant data
subtype, and the corresponding Visual Basic fundamental data types for each of the Jet SQL parameter data type identifiers corresponding to Microsoft ODBC SQL data types, not ANSI SQL data types.
| Field Data Type | Jet SQL Parameter Type | Variant Data Subtype | Visual Basic Data |
| Yes/No | Bit | 2 | Integer |
| Byte | Byte | 2 | Byte |
| Number (Integer) | Integer | 2 | Integer |
| Number (Long Integer) | Long | 3 | Long |
| Number (Single) | IEEESingle | 4 | Single |
| Number (Double) | IEEEDouble | 5 | Double |
| Currency | Currency | 6 | Currency |
| Date/Time | DateTime | 7 | Variant |
| Text | Text | 8 | String |
| OLEObject | LongBinary | 8 | String |
| Memo | LongText | 8 | String |
| Binary | Binary | 8 | Not supported |
| Value | Value | N/A | Not supported |
The Binary and Value data types are not supported by Visual Basic 4.0 or Jet 3.0. You cannot create a field of the Jet Binary or Value field data type; however, Microsoft Access offers Binary and Value options in the Data Type combo
box of the Query Parameters dialog. As mentioned earlier in this book, Microsoft uses the Binary field data type for the SID (System ID) value of the MSysAccounts table of System.mdw. If you really want to use the Binary data type, you can make a copy of
the System.mdw table with another name, import the table into a Jet database, and then alter the design of the table as desired.
You pass the value of the parameter, but not the Jet SQL parameter data type, to the QueryDef object in the second to the last of the following generic statements:
Dim dbName As Database
Dim qdfQueryDef As QueryDef
Dim rstResult As Recordset
Set dbName = OpenDatabase("DatabaseName")
Set qdfQueryDef = dbName.QueryDefs("QueryName")
qdfQueryDef!ParamName = typDesiredValue
Set rstResult = qdfQueryDef.OpenRecordset([intOptions])
...
You must use the bang symbol (!) as the separator between qdfQueryDef and ParamName; using a period (.) separator generates a "Property not found" error message.
The object identifier for the OpenRecordset method is the QueryDef object, not the Database object. When you specify a QueryDef object as the object identifier for the OpenRecordset method, you do not supply a strSource or objSource
argument. If you supply either of these arguments, you receive an "Invalid argument" error message.
An example of the use of a parameterized QueryDef object is given in the section titled, "Displaying Detail Data with the Grid Control," which follows later in this chapter.
Rollup queries are make-table queries that you execute from within a Visual Basic application. Rollup queries use the SQL aggregate SUM() function to total numeric values contained in tables of operational databases. Typically, a rollup query creates a
new table with the following fields:
The easiest method of developing rollup queries is to create a group of summary make-table QueryDef objects in a Jet database with Microsoft Access 2.0 or Microsoft Access 7.0 (depending on whether you're developing for 16-bit or 32-bit platforms) or
the VisData application. Then you write a simple Visual Basic application to execute the QueryDef objects that you created.
The Dec_supt.mdb sample database, which is included on the accompanying CD-ROM, contains several make-table QueryDef objects. (It also provides the rolled up data required by the sample decision-support forms in the sections later in this chapter.) The
Jet SQL statement of the qryMonthlySalesRollup QueryDef object that creates the data for the graph of the Button Bar form of Figure 8.3 (later in this chapter) is as follows:
SELECT Format(Orders.[ShippedDate],"yyyy") AS Year,
Format(Orders.[ShippedDate],"mm") AS Month,
SUM([Order Details].[UnitPrice]*[Order Details].Quantity*
(1-[Order Details].Discount)) AS Sales
INTO tblSalesRollupMonth
FROM Orders, [Order Details]
WHERE Orders.[OrderID]=[Order Details].[OrderID]
GROUP BY Format(Orders.[ShippedDate],"yyyy"),
Format(Orders.[ShippedDate],"mm")
HAVING Format([Orders].[ShippedDate],"yyyy"))="1994";
The Jet SQL statement differs from ANSI SQL syntax in the use of the VBA Format() function to return parts of dates (in the Year and Month fields) and in the GROUP BY and HAVING clauses. If this query were executed with the SQL passthrough
option, you would replace the Format() function with the appropriate ANSI SQL scalar function, YEAR() and MONTH(). The GROUP BY aggregations you use must correspond exactly to the corresponding SELECT descriptors in your SQL statement.
The SUM() SQL aggregate function totals the net sale amount, taking into account the discount, if any, offered to the customer on a particular product. The INTO statement identifies the name of the table that is created by the query. The initial GROUP
BY criterion that groups orders by the year in which the order was shipped is included in the GROUP BY clause because you might want to specify more than one year in the HAVING clause with an AND operator.
If the tblSalesRollupMonth table does not exist, the query creates the table. If the tblSalesRollupMonth table exists, the existing table is deleted before the new table is created. (If you execute this SQL statement in the VisData application, VisData
displays an error message. VisData prohibits you from overwriting the tblSalesRollupMonth table; in VisData, you must delete the table before running this query a second time.)
Most of the other make-table QueryDef objects in Dec_supt.mdb are more complex than the qryMonthlySalesRollup query. You can examine the syntax of each QueryDef object by opening the QueryDef object in the VisData application.
You may encounter a special problem with some rollup queries in Jet databases that use AutoNumber or Counter field types. The Jet database engine does not permit you to create tables that have more than one AutoNumber field. If your rollup queries try to combine data from two or more tables, and you include more than one AutoNumber field in the rollup table, the query will fail. For example, if you try to use a rollup query like qryMonthlySalesRollup (included in the Dec_supt.mdb sample table for this chapter) directly on the Categories and Products tables in the Northwind.mdb sample database supplied with MS Access 7.0, it won't work because the query attempts to create a new table containing the ProductID (from Products) and CategoryID (from Categories) fields, both of which are AutoNumber fields. The qryMonthlySalesRollup query works in the Dec_supt.mdb sample database because the Categories and Products tables (linked from the Crosstab.mdb used in Chapter 7, "Running Crosstab and Action Queries") have had all their AutoNumber fields converted to Number fields with a Long Integer format.
When creating your own rollup tables, you may first need to isolate the data you want in an intermediary table, and then change the AutoNumber field data types to Number fields with Long Integer format in order to create the rollup tables you desire.
To execute make-table queries with VBA code, you apply the Execute method to the QueryDef object. The Execute method is applicable only to action queries; you receive an error message if you attempt to apply the Execute method to a SELECT or TRANSFORM
(crosstab) query that returns rows. As an example, the following code executes each of the six make-table QueryDef objects in Dec_supt.mdb:
Dim dbDecSupport As Database
Set dbDecSupport = OpenDatabase("c:\ddg_vb4\32_bit\chaptr08\dec_supt.mdb")
dbDecSupport.Execute "qryMonthlySalesRollup", dbFailOnError
dbDecSupport.Execute "qryMonthlySalesRollupCategory", dbFailOnError
dbDecSupport.Execute "qryMonthlySalesRollupProduct", dbFailOnError
dbDecSupport.Execute "qryQuarterlySalesRollup", dbFailOnError
dbDecSupport.Execute "qryQuarterlySalesRollupCategory", dbFailOnError
dbDecSupport.Execute "qryQuarterlySalesRollupProduct", dbFailOnError
dbDecSupport.Close
Alternatively, you can substitute a Jet SQL make-table query statement for the name of a QueryDef object. In previous versions of Visual Basic, the ExecuteSQL() method was used for SQL passthrough queries to create tables from a client/server database
connected by the ODBC API. The ExecuteSQL method is obsolete; you now use the Execute method with the dbSQLPassThrough option flag for client/server queries.
One of the incentives for purchasing database front-end application generators is that their users can generate their own ad hoc queries against large databases. The intensity of the desire to create ad hoc queries usually is inversely proportional to
the individual's position in the corporate hierarchy. In the upper corporate echelons, executives want the click of a single button to deliver the summary information they need. At the operational level, managers and supervisors want the opportunity to
choose from a multiplicity of record-selection options.
When an unhindered user executes a SELECT * query against large mainframe or client/server databases, it can bring even the highest performance RDBMS to its knees. Accidentally or intentionally returning all of the records in a monster table can cause
severe network congestion, at least until the user's RAM and disk swapfile space is exhausted. The worst-case scenario is the accidental creation of a Cartesian product by the omission of a join condition when more than one table is involved in a query.
Some RDBMSs detect this condition and refuse to execute the query. Others, such as applications that use the Jet database engine, attempt to return every combination of records in the tables.
Do not create decision-support applications that enable users to enter their own SQL SELECT statements against production databases. Use combo boxes or list boxes to restrict the fields to be displayed and to add required WHERE clause record-selection
criteria.
This chapter concentrates on designing simple decision-support applications that use prepared queries. The next chapter, "Using Advanced Data-Aware OLE Controls," describes how to design queries that give users more freedom to define the data
they want to summarize or display in detail. Chapter 13, "Designing Online Transaction-Processing Systems," shows you how to design a general-purpose query tool for generating user-defined queries that won't overtax your RDBMS or local area
network.
Microsoft Windows achieved its commercial success because Windows 3.x has a graphic interface that most users prefer to the DOS command-line prompt. Windows 95 is likely to increase the commercial success of Windows with its increased ease of
use and better integration of the graphical user interface with the underlying operating system. Windows applications now dominate the PC software market because they use design elements which, at least in most cases, conform to the common user access
(CUA) architecture developed by IBM Corp. in the 1980s. The CUA specification describes the design and operation of menus and other common control objects, such as check boxes, radio (now option) buttons, and message dialog boxes. The sample applications
in this book employ the principles embodied in Microsoft's interface guidelines.
The primary objective of the CUA specification is to create uniformity in the overall appearance and basic operational characteristics of computer applications. CUA principles apply to character-based DOS applications executed on PCs and to mainframe
sessions running on 3270 terminals. The user interface of Microsoft Windows 3.x, Windows 95, Windows NT, and IBM OS/2 for PCs, XWindows and Motif for UNIX systems, and System 7.x for Macintosh computers conform in most respects to IBM's basic
CUA specification. Therefore, if you're accustomed to Microsoft Word for the Macintosh, you can quickly adapt to using Microsoft Word for the PC.
The sections that follow describe some of the basic requirements of the user interface for database decision-support applications designed for use at the upper-management level. Subsequent chapters in this book provide similar guidance for more
flexible decision-support applications and data-entry (online transaction-processing) applications.
The usability of mainstream Windows applications ultimately determines the products' success in the software market. Feature-list comparisons in product advertising and magazine reviews may influence the purchasing decisions of individual users, but
the primary purchasers of Windows applications are large corporations. The objective of these corporate purchasers is to minimize the time and training expenses that are required for their personnel to learn and use the applications effectively. Thus,
applications are rated by their usability, a wholly subjective attribute. An application that one user finds intuitive and easy to use may be totally incomprehensible to another user.
Testing applications for usability is an art, not a science, and it is a primitive art at best. Commercial firms that conduct usability tests on major software products charge $100,000 or more for testing relatively simple Windows applications.
Microsoft Corp. has invested tens of millions of dollars in usability testing of Windows 95 and their 32-bit Windows applications. It is quite unlikely that the applications you create will undergo commercial usability tests. Instead, your client may
simply inform you that he or she does not understand how to use your application without reading the manual. When that happens, your application has just failed the ultimate usability test.
The following sections describe characteristics of applications that achieve high usability ratings and show you how to implement these characteristics in the forms that constitute a simple executive-level, decision-support application.
When you design decision-support applications, your watchword is simplicity. Application simplicity is achieved by applying the following rules to your application design:
The preceding two rules are especially important for executive-summary, decision-support applications, because top executives are unlikely to be PC power users. A simple, intuitive user interface and a limited feature list are the two primary
characteristics of professional-quality, executive-summary applications.
Figure 8.3 illustrates the first form of a hypothetical executive-summary, decision-support application that displays sales information for a one-year period. A button bar is the primary navigation device for the application. The button bar enables the
user to make the following choices:
Figure 8.3. The opening form of an executive-summary, decision-support application.
The data source for the forms for Figures 8.3 through 8.6 is Dec_supt.mdb, which is installed from the accompanying CD-ROM into your C:\Ddg_vb4\32_bit\Chaptr08 directory. Dec_supt.mdp uses tables attached from Crosstab.mdb and expects to find
Crosstab.mdb in your C:\Ddg_vb4\32_bit\Chaptr07 directory. Data for the year 1994 is shown in these examples, because 1994 is the latest year for which 12 months of data is available in Northwind.mdb, on which Crosstab.mdb is based. If you installed either
Crosstab.mdb or Dec_supt.mdb in a location other than their default directory, you need to change the values in the code that point to the default directory (search for C:\ddg_vb4\32_bit), and you need to reattach the Crosstab.mdb tables to Dec_supt.mdb.
The following list describes the design principles embodied in the decision-support form shown in Figure 8.3.
The form shown in Figure 8.3 (and the forms of Figures 8.4 through 8.6, which follow) serves as the foundation of the form designs for the majority of the decision-support sample applications presented in this book.
When you click a button that does not have a bitmap assigned to the PictureDn property, setting the PictureDnChange property to 2, Invert 'PictureUp' Bitmap causes the bitmap to be inverted when the button is in its "down" position. The inverting process replaces each pixel with one of the "opposite" color (black pixels for white, and so on). The Sales Monthly (line chart) button in Figure 8.3 has an inverted "down" button.
The VBA code that supplies the values on which the graphs shown in this chapter are based is described in the section, "Creating Graphs from Rolled-Up Data," later in this chapter.
The adage, "Consistency is the hobgoblin of small minds," does not apply to computer applications. Both internal and external consistency of the user interface is a principal requirement of a properly designed Windows application. The
following list describes these two types of consistency:
You need to meet the following criteria to maintain internal consistency:
Figure 8.4 is an example of a form that uses a toolbar-style row of buttons, rather than a button bar (both the button bar in Fig. 8.3 and Fig. 8.4 are created with SSRibbon controls). The toolbar form is internally consistent with the button bar form
shown in Figure 8.3. The difference between a toolbar and a button bar is that the buttons of a toolbar are placed within a designated area of the form (usually at the top). A background margin surrounds individual buttons or groups of buttons in toolbars.
Using smaller buttons without captions often is necessary when you have more buttons on forms than can be accommodated by the width of the display.
To display the toolbar form that is shown in Figure 8.4, click the Divisions button of the Button Bar form (with the bar chart bitmap) or choose the View | Toolbar Form menu command of the Ui_examp application. If you did not install the
Ui_examp application in your C:\Ddg_vb4\32_bit\Chaptr08 directory, you receive an error when the DrawMonthlyDivisionChart procedure attempts to load the bitmaps with the LoadPicture() method. Change the argument of the two instances of the LoadPicture()
method to correspond to the location of the Ui_examp application on your fixed disk drive.
Figure 8.4. A bar chart decision-support form with toolbar-style buttons.
Figure 8.5 shows the bar chart display of Figure 8.4 presented as an area chart. You change the PictureUp property of the division toolbar button to a different bitmap when you display an area chart. Changing to a button bitmap representing an area
chart preserves internal consistency between the button and the look of the form that appears when you click the button.
In Fig. 8.4, notice that the displayed chart is a bar chart. The second button has an area chart icon. Clicking this button changes to the area chart shown in Fig. 8.5. Notice, in Fig. 8.5, that the button's icon is now changed to a bar chart icon. The
button icon always indicates the type of chart that will be displayed as a result of clicking the button—not the type of chart currently being displayed.
Here are the rules for maintaining external application consistency:
Borland International's products use stylized OK, Cancel, and Help buttons for message boxes. If you want to duplicate Borland's button style, you'll need to create your own message box form and add custom bitmapped buttons. (You can identify Windows
applications that were created with the Borland C++ compiler by the stylized OK buttons with an adjacent shadowed check mark that Borland includes in its resources library.)
It is difficult to create a collection of small icons (about 24x24 pixels) that unambiguously represents a variety of operations. Figure 8.6 illustrates the use of a Toolbar control with tooltips (pop-up labels built into the toolbar button object)
that appear when the mouse pointer is positioned on the surface of the button. The Toolbar control is a new feature in Visual Basic 4; the toolbar in Figure 8.6 is a single object, which contains a collection of button objects. The tooltips that appear are
a property of the button. To make these tooltip labels appear, you simply set the ToolTipText property of the particular button. All of the code needed to display and hide the tooltips is intrinsic to the Toolbar control.
By using the Toolbar control, you can easily create a "look and feel" for your applications that is similar to that found in recent releases of major Microsoft productivity applications and programming tools—Excel 5 and 7, Access 7, Word
6 and 7, and Visual Basic 4.0. By changing the Visible property of a Toolbar control, you can provide your application with multiple toolbars; the Toolbar control even has an AllowCustomize property which, if True, permits your application's user to
customize the toolbars in your application.
Using tooltips for the buttons in a Toolbar control is a better method of identifying the purpose of a button than displaying the same information in a status bar at the bottom of a form. No eye movement is necessary to read the adjacent label caption,
whereas a substantial eye movement is required to traverse the VDU from the top toolbar to the bottom status bar. Minimizing the eye movement required to accomplish each of the application's tasks is one of the principles of good user interface design.
Figure 8.6. Using tooltips and a Toolbar control for buttons on a form.
The documentation that accompanies Visual Basic 4.0 describes how to use the graphics capabilities of Visual Basic, but provides little or no practical advice for adding images to command button, 3D group pushbutton, and toolbar button controls. The
sections that follow describe how to obtain the bitmapped images you need for your toolbar buttons, how to create Windows bitmap (.bmp) files with Windows Paint, and how to add the image contained in a .bmp file to the Picture property of a command button,
or the PictureUp property of a group pushbutton or toolbar button.
Visual Basic includes a plethora of icons and bitmaps that you can use to decorate conventional, 3D command, and group pushbuttons, or as the button faces for toolbar buttons. All of the buttons used in the Button Bar and Toolbar forms in Figures 8.3
through 8.5 are based on icons that Visual Basic installs in your VB4\Bitmaps and VB4\Icons folders. Figure 8.7 displays a large icon view of the icons provided in the Icons\Misc folder. (Because these are .ico files, Windows 95 is able to
display the actual icon image in the directory listing; you'll need to use the QuickView command or Paint to view the appearance of the sample bitmaps supplied with Visual Basic 4.0).
Figure 8.7. The content of the VB4\Icons\Misc folder.
You may use either .ico or .bmp files to provide the Picture, PictureUp, PictureDisabled, and PictureDn properties of buttons.
Visual Basic 4.0 includes a bitmap editing tool called Image Editor. The Image Editor application is located on the Visual Basic CD-ROM in the \Tools\Imagedit folder. (You may need to manually copy this folder from the CD-ROM to your hard disk.) You
need to transfer the data in icon (.ico) files to Windows Paint with the Windows Clipboard, because you cannot open an .ico file in Windows Paint. The Image Editor, however, does permit you to directly open and edit .ico files. Figure 8.8 shows the sample
Mail.bmp file being edited in the Image Editor application.
Figure 8.8. The Image Editor application displaying the sample Mail.bmp file.
Choose the Edit | Copy menu command in the Image Editor to copy the entire icon to the Clipboard in BMP format; then open Paint, add a light gray background with the paint roller tool, and choose Paste from Paint's Edit menu to add the
icon to your Paint image. You can alter the appearance of the button bitmap with either the Image Editor or with Paint's toolkit. In general, you may find it easiest to use the Image Editor, which automatically magnifies the .bmp or .ico image you're
working with.
There are many full-featured, vector-based drawing packages available on the market today. Most include a variety of clip art images you can use (or adapt for use) as button faces in your applications. Some desktop publishing applications come with
several thousand clip art images on their CD-ROM. In addition, you can purchase CD-ROM disks packed with nothing but various clip art images.
When you open the icons or clip art images in commercial publishing packages, the image is typically much larger than you need or want for a button bitmap. Generally, you must reduce the image to create a bitmap that is approximately 0.25 inch high.
Add a light gray background color to the image so that the background matches the default background color of Visual Basic buttons. You'll probably have to export the image from its original clip art format, which is usually some type of vector drawing
format, rather than a bitmap. When you export the clip art image, export it to a 16-color Windows bitmap (.bmp) format file.
The event-handler for the Button_Click event for the buttons of the button bar or toolbar (except the button that represents the currently selected form) uses the Show method to open the new form and then executes the Hide method to make the current
form temporarily disappear. If the form you select has not been opened, the Show method automatically executes the Load method. (Using the Load method by itself does not create an instance of a form's window.) You can force a form's Load method to execute
by using the Visual Basic Unload statement.
The generalized code structure for showing and hiding forms is this:
Sub btnButtonName_Click
frmNewFormName.Show
[DoEvents]
frmCurrentFormName.Hide
End Sub
To prevent your application from disappearing from the display, apply the Show method to the new form before you Hide the currently open form. The optional DoEvents statement enables Windows to process the messages necessary to display the
frmNewFormName form before hiding the window that displays the current form. DoEvents is necessary only when you load a form that has a substantial amount of code that needs to be executed when the Form_Load event of the new form is triggered.
The Graph OLE control that is included with the Professional Edition of Visual Basic 4.0 enables you to create a variety of graphs and charts from data supplied by your application's queries. This book distinguishes between the terms graph and
chart. A graph consists of data points, usually connected by lines. A chart uses two-dimensional objects, such as bars or other filled-screen areas, to represent the data. Graphs and charts usually employ display colors or print patterns to
distinguish sets of data. All the graphs and charts in the sample forms discussed in this chapter use rolled-up data created by the set of make-table queries discussed in the preceding section of this chapter.
The 32-bit Graph OLE control of Visual Basic 4.0 consists of the OLE control file, Graph32.ocx, and the graphic server, consisting of Gsw32.exe and Gswdll32.dll, created by Bits Per Second, Ltd. and licensed to Microsoft Corp. for inclusion in Visual
Basic. When you add Graph32.ocx to your .vbp file, Visual Basic or your application automatically attaches the Gswdll32.dll library, which advertises its presence with an icon on the task bar. The developer versions of these products, consisting of the
Chart Builder custom control and the Graphics Server SDK, are distributed in the U.S. by Pinnacle Publishing, Inc. The developer version includes an additional feature called hot graphs, the use of which is described in Chapter 12,
"Extending Your Use of the Data Access Object."
The Graph control creates graphs much more quickly than the 16-bit Microsoft Graph 5 (MSGraph5) OLE server that is used by Access, Word, and Excel. The primary reason for the improvement in performance is that Graph32.ocx, Gsw32.exe, and Gswdll32.dll
are loaded when you load your application and remain loaded while your application is running. Access, Word, and Excel need to load MSGraph5 each time you create a new graph or edit an existing graph. The increased performance you can achieve by using the
Graph32.ocx OLE control, rather than MS Graph, compensates for the lack of many of the graph- and chart-formatting features that are offered by MSGraph5. You can use the Bits Per Second Graphic Server SDK to add features to your graphs that are not
included in the Graph custom control.
The sections that follow give examples of the code you use to create the graphs shown in the preceding Figures 8.3 through 8.6 with the graph custom control.
The graph showing Monthly Sales for 1994, as shown in Figure 8.3 earlier in this chapter, uses Dec_supt.mdb's tblSalesRollupMonth table created by the qryMonthlySalesRollup make-table query from data contained in Chapter 7's Crosstab.mdb. Most of the
properties of the graph are set in design mode. When you view the Monthly Sales for 1994 graph in design mode, the data points that create the sales line are created by a random number generator. In run mode, dynamic properties and the values of each data
point are supplied by your code.
Listing 8.1 contains the code for the Declarations section and the Main subprocedure of the Ui_examp application. All but the most trivial examples in this book initiate their execution with the Main subprocedure, which initializes the values of global
and procedure-level variables. As a rule, you declare Database objects with Public (formerly known as Global) scope to avoid the necessity of opening the Database objects each time you open a new form. You also may want to declare general-purpose Recordset
or QueryDef object variables as Public variables to eliminate the need to Dim these objects in the code behind your forms.
The two arrays, strMonths() and strQuarters(), contain month names (three-letter abbreviations, the full month name, and string month numbers, 01. . .12), and the ordinal names of quarters (First. . .Fourth), respectively. You'll want to declare these
two arrays as Public variables and add the code to initialize these arrays to the Main procedure of the majority of your decision-support applications.
This book does not use the tag prefix a to designate array variables. The trailing parenthesis of array variables that are prefixed with one of Visual Basic's fundamental data type abbreviations, such as str or var, is adequate to indicate that the
variable is an array. Indexes to array variables do not use the standard ai prefix, as in aintVarName. Instead, a general-purpose (intCtr) counter variable or a variable that includes an abbreviated description of the source of the index (intCol or intRow)
is used.
Listing 8.1. The Declarations section and Main procedure of Ui_examp.vbp.
Option Explicit
'Arrays for month names (short and long form), and quarters
Public strMonths(12, 3) As String
Public strQuarters(4) As String
Public dbDecSupport As Database 'Decision support database
Public intCtr As Integer 'General purpose counter
Sub Main()
'Purpose: Open example database, initialize arrays, etc.
'Called by: Visual Basic Project Startup Form (Options Project)
Set dbDecSupport = _
OpenDatabase("c:\ddg_vb4\32_bit\chaptr08\dec_supt.mdb")
For intCtr = 1 To 12
strMonths(intCtr, 1) = _
Format(DateSerial(1994, intCtr, 1), "mmm")
strMonths(intCtr, 2) = _
Format(DateSerial(1994, intCtr, 1), "mmmm")
If intCtr < 10 Then
strMonths(intCtr, 3) = "0" & LTrim$(Str$(intCtr))
Else
strMonths(intCtr, 3) = LTrim$(Str$(intCtr))
End If
Next intCtr
strQuarters(1) = "First"
strQuarters(2) = "Second"
strQuarters(3) = "Third"
strQuarters(4) = "Fourth"
'Open the initial form
frmButtonBar.Show
End Sub
Unless you want to create an animated graph, in which displaying the individual data points is delayed by a timer control, you send the data to the graph custom control in the Form_Load subprocedure. Listing 8.2 is the code you use to draw the simple
line graph that is illustrated in Figure 8.3 and that appears on the Button Bar form. Form_Load in Butn_bar.frm calls the DrawMonthlySalesGraph subprocedure, which supplies data and properties to the form. The value assigned to the DrawMode method enables
you to display, print, or copy the graph to the Clipboard. These choices are implemented in the Button Bar form as follows:
Each of the preceding menu choices and button clicks needs to re-execute DrawMonthlySalesGraph with the appropriate DrawMode value to display, print, copy, or save the graph.
Listing 8.2. The code to create and display a line graph.
Private Sub Form_Load()
'Generate the monthly sales graph
Call DrawMonthlySalesGraph
'Draw the graph on the form as a Windows metafile
grfMonthlySales.DrawMode = 2
GroupPush3D1.Value = True 'reset first button
End Sub
Private Sub DrawMonthlySalesGraph()
'Purpose: Draw the monthly sales graph using the data in
' tblSalesRollupMonth
'Called by: frmButtonBar_Load and gpbAttachment
Dim tblMonthlySales As Recordset
'Run-mode settings for the monthly sales graph
grfMonthlySales.Width = 9500 'Set the width (> the form width)
grfMonthlySales.FontUse = 4 'Set all fonts to the font family and style
grfMonthlySales.FontFamily = 1 'Set the font family to Arial (Swiss)
grfMonthlySales.FontStyle = 2 'Set the font attribute to bold
grfMonthlySales.AutoInc = 0 'Turn off autoincrement
grfMonthlySales.NumPoints = 12 'Up to 12 months of data
grfMonthlySales.LineStats = 9 'Add best fit and mean statistics
'Open the rollup table
Set tblMonthlySales = dbDecSupport.OpenRecordset("tblSalesRollupMonth")
tblMonthlySales.MoveFirst
'Load the data points explicitly
intCtr = 1
Do Until tblMonthlySales.EOF Or intCtr > 12
grfMonthlySales.ThisPoint = intCtr
grfMonthlySales.LabelText = strMonths(intCtr, 1)
grfMonthlySales.GraphData = tblMonthlySales!Sales
tblMonthlySales.MoveNext
intCtr = intCtr + 1
DoEvents
Loop
'The Close method is used to maintain good programming practices
'(Always close the database objects that you open)
tblMonthlySales.Close
End Sub
Only the Button Bar form includes a full implementation of menu choices and button operations for the graph object. The remainder of the forms in Ui_examp primarily are intended to demonstrate differences in user interface features rather than to
exemplify a fully developed application.
You create the bar and area charts shown in Figures 8.4 and 8.5 with the VBA code shown in Listing 8.3. The code in Listing 8.3 is more complex than that for the simple line chart of the previous listing because eight sets (categories) of 12 data
points (months) are plotted. Also, you need to change the default formatting of the bar chart if you want to display the narrow bars shown in Figure 8.4. You also need to create a legend that identifies the data sets by color for the display and by
different patterns for printing.
Listing 8.3. The code to create and display a bar or area chart.
Private Sub Form_Load()
Dim fAreaChart As Boolean
'Set the fAreaChart to True to substitute an area chart for the bar chart
fAreaChart = False
'Draw the stacked vertical bar chart or the area chart
DrawMonthlyDivisionsChart fAreaChart
'Draw a Windows metafile of the chart
grfMonthlyDivisions.DrawMode = 2
End Sub
Private Sub DrawMonthlyDivisionsChart(fAreaChart As Boolean)
'Purpose: Draw either an area chart or a vertical stacked bar chart by
' division (Category ID) from the monthly sales rollup by category
'Called by: frmToolBar_Load
Dim intMonth As Integer
Dim intDivision As Integer
Dim tblMonthlyDivisions As Recordset
Dim varPicture As Variant
If fAreaChart Then
gpbDivisionSales.PictureUp = _
LoadPicture("c:\ddg_vb4\32_bit\chaptr08\tb_divis.bmp")
'Set the graph type to area chart, default style, horizontal
'and vertical grid, 12 points with ticks on both axes
grfMonthlyDivisions.GraphType = 8
grfMonthlyDivisions.GraphStyle = 0
grfMonthlyDivisions.GridStyle = 3
grfMonthlyDivisions.NumPoints = 12
grfMonthlyDivisions.Ticks = 1
Else
gpbDivisionSales.PictureUp = _
LoadPicture("c:\ddg_vb4\32_bit\chaptr08\tb_divs2.bmp")
'Set the graph type to vertical bar chart, stacked style, horizontal
'grid only, 24 points with ticks on the Y-axis only. To provide a
'better aspect ratio, the bars are half-width
grfMonthlyDivisions.GraphType = 3
grfMonthlyDivisions.GraphStyle = 2
grfMonthlyDivisions.GridStyle = 1
grfMonthlyDivisions.NumPoints = 24
grfMonthlyDivisions.Ticks = 3
End If
grfMonthlyDivisions.Width = 9400
grfMonthlyDivisions.FontUse = 4
grfMonthlyDivisions.FontFamily = 1
grfMonthlyDivisions.FontStyle = 2
grfMonthlyDivisions.FontUse = 3
grfMonthlyDivisions.FontSize = 80
grfMonthlyDivisions.FontUse = 2
grfMonthlyDivisions.AutoInc = 0
grfMonthlyDivisions.NumSets = 8
'Set the table object
Set tblMonthlyDivisions = _
dbDecSupport.OpenRecordset("tblCategoryRollupMonth")
tblMonthlyDivisions.MoveFirst
'Create the legends for the table from the first 8 records
For intDivision = 1 To 8
grfMonthlyDivisions.ThisPoint = intDivision
grfMonthlyDivisions.LegendText = _
tblMonthlyDivisions![CategoryName]
tblMonthlyDivisions.MoveNext
Next intDivision
'Get the 12 data points (months) in each of 8 data sets (categories)
intDivision = 1
intMonth = 1
tblMonthlyDivisions.MoveFirst
Do Until tblMonthlyDivisions.EOF
grfMonthlyDivisions.ThisSet = intDivision
If fAreaChart Then
grfMonthlyDivisions.ThisPoint = intMonth
Else
'Skip alternate points
grfMonthlyDivisions.ThisPoint = intMonth * 2
End If
If intDivision = 1 Then
'Add the label text to the x-axis
grfMonthlyDivisions.LabelText = strMonths(intMonth, 1)
End If
'Add the data for the point and set
grfMonthlyDivisions.GraphData = tblMonthlyDivisions!Sales
tblMonthlyDivisions.MoveNext
intDivision = intDivision + 1
If intDivision = 9 Then
'The divisions cycle is complete
intDivision = 1
intMonth = intMonth + 1
End If
DoEvents 'For safety and to assure that messages are passed
Loop
'refresh the graph on-screen
grfMonthlyDivisions.Refresh
'Close the table object
tblMonthlyDivisions.Close
End Sub
The code to draw the pie chart shown in Figure 8.6, which displays sales distribution by product for the first quarter of 1994, appears in Listing 8.4. If you want to display the numerical percentage for each product category, you need to sum the total
sales for the period before you create the pie chart. You calculate the percentage of total sales and add the value in parentheses to the label text for each product category. Alternately, you can create a legend, identical to that in the preceding
example, and label the pie wedges only with the category identifier.
Listing 8.4. The code to create and display a pie chart.
Private Sub Form_Load()
'Generate the graph
DrawQuarterlyProductsPieChart
'Draw the graph on the form as a Windows metafile
grfQuarterlyProducts.DrawMode = 2
End Sub
Private Sub DrawQuarterlyProductsPieChart()
'Purpose: Draw the monthly sales graph using the data in tblSalesRollupMonth
'Called by: frmButtonBar_Load and gpbAttachment
Dim tblCategorySales As Recordset
Dim varTotalSales As Variant
Dim strLabel As String
'Run-mode settings for the monthly sales graph
grfQuarterlyProducts.Left = -700 'Set the left off the form
grfQuarterlyProducts.Width = 10000 'Set the width (> the form width)
grfQuarterlyProducts.Height = 5500 'Set the width (> the form width)
grfQuarterlyProducts.FontUse = 4 'Set all fonts to the font family and style
grfQuarterlyProducts.FontFamily = 1 'Set the font family to Arial (Swiss)
grfQuarterlyProducts.FontStyle = 2 'Set the font attribute to bold
grfQuarterlyProducts.AutoInc = 0 'Turn off autoincrement
grfQuarterlyProducts.NumPoints = 8 'There are 8 product categories
'Open the rollup table
Set tblCategorySales = _
dbDecSupport.OpenRecordset("tblCategoryRollupQuarter")
tblCategorySales.MoveFirst
'Sum the data to get total sales for the quarter
intCtr = 1
Do Until tblCategorySales.EOF Or intCtr > 8
If Not IsNull(tblCategorySales!Sales) Then
varTotalSales = varTotalSales + tblCategorySales!Sales
End If
intCtr = intCtr + 1
tblCategorySales.MoveNext
DoEvents
Loop
'Load the data points explicitly
intCtr = 1
tblCategorySales.MoveFirst
Do Until tblCategorySales.EOF Or intCtr > 8
grfQuarterlyProducts.ThisPoint = intCtr
strLabel = tblCategorySales!CategoryName & " ("
strLabel = strLabel & _
Format((tblCategorySales!Sales / varTotalSales), _
"#0.0%") & ")"
grfQuarterlyProducts.LabelText = strLabel
grfQuarterlyProducts.GraphData = tblCategorySales!Sales
tblCategorySales.MoveNext
intCtr = intCtr + 1
DoEvents
Loop
'The Close method is used to maintain good programming practices
'(Always close the database objects that you open)
tblCategorySales.Close
End Sub
The Grid control is Visual Basic's primary method of displaying the tabular detail behind a graphical presentation. Unlike Access 7.0's subforms, the Grid control cannot be linked to a Recordset object directly, although the DBGrid control (which is
different from the plain Grid control) can be bound to a Data control, and will use the Data control's Recordset. For the Grid control, however, you can write a simple Visual Basic routine to display data created by the qryMonthlyProductSalesParam query
from the Products table and the qryMonthlyProductSalesCrosstab query of Dec_supt.mdb. The SQL statements used to create the two QueryDef objects that you use to generate a Snapshot-type Recordset object (which you manipulate to supply data to the grid) are
as follows:
TRANSFORM Sum(tblProductRollupMonth.Sales) AS SumOfSales
SELECT tblProductRollupMonth.[ProductID]
FROM tblProductRollupMonth
GROUP BY tblProductRollupMonth.[ProductID]
PIVOT tblProductRollupMonth.Month
IN ("01","02","03","04","05","06","07","08","09","10","11","12");
PARAMETERS CategID Text;
SELECT Products.[ProductID], Products.[ProductName],
[01], [02], [03], [04], [05], [06], [07], [08], [09],
[10], [11], [12]
FROM qryMonthlyProductSalesCrosstab, Products,
qryMonthlyProductSalesCrosstab RIGHT JOIN Products
ON qryMonthlyProductSalesCrosstab.[ProductID] =
Products.[ProductID]
WHERE Products.[CategoryID]=CategID;
The TRANSFORM (crosstab) query creates a Snapshot-type Recordset object that consists of the ProductID column and 12 monthly columns labeled 01 through 12. One row is created for each product. The IN predicate is added to ensure that the query returns
12 columns, even if no data is available for all of the months of the year. The SQL SELECT statement uses the Recordset object created by the Jet SQL TRANSFORM statement as if the Recordset were a persistent table.
Using a Snapshot-type rather than a Dynaset-type Recordset object usually is faster when your query result set contains less than about 100 rows. (Summary decision-support forms never should contain large numbers of rows.) Decision-support applications don't modify data, and Dynaset-type Recordset objects aren't updatable; therefore, there's no reason to specify a Recordset of the Dynaset type for most summary queries.
The purpose of the parameterized SELECT query is to ensure that all rows for products within a category designated by the CategID parameter appear in the resulting Recordset, regardless of whether sales of the product occurred during the year, and to
supply a column containing the name of the product. A RIGHT JOIN is needed to make all of the products in the category appear. You need to use Jet SQL for the JOIN statement because the Jet database engine does not recognize the ANSI SQL =* operator that
designates a right join.
The Detail Grid form containing the data generated by the preceding SQL statements appears in Figure 8.9. The first row (row 0) and the first two columns (0 and 1) of the grid are fixed. The ID and Product Name columns remain in position regardless of
the location of the slider of the horizontal scrollbar. The first row, which contains the column headers, remains fixed despite the movement of a vertical scrollbar. A vertical scrollbar only appears if the number of rows exceeds that which can be
displayed within the Height property value of the grid. The fixed row corresponds to Access's form header. There is no provision for fixed columns in Access subforms that do not use the datasheet. The capability to fix columns in the grid control more than
compensates for Visual Basic's lack of a native Access subform control.
Figure 8.9. A grid control with data from the crosstab query for monthly sales by product category.
The code that loads the grid with the data from the parameterized qryMonthlyProductSalesParam query does not differ greatly from that used to create graphs with multiple data sets. Listing 8.5 sets the number of rows of the grid based on the number of
records returned by the query. Then, you add the column headings and use nested loops to add the data in column-by-row sequence. Populating the grid control is an example of using integer arguments (instead of field name strings) to specify members of the
Fields() collection.
Listing 8.5. The code to display crosstab data in a grid control.
Private Sub Form_Load()
'Purpose: Dimension and add data to a grid control
'Declare the required object and conventional variables
Dim rssMPS As Recordset
Dim qdfMPS As QueryDef
Dim strSQL As String
Dim strCategory As String
Dim intCol As Integer
Dim intRow As Integer
'Add the column headers to the grid (Product ID, Product Name, and months)
grdMPS.Row = 0
For intCol = 0 To 13
grdMPS.Col = intCol
If intCol = 0 Then
'Product ID column
grdMPS.Text = "ID"
grdMPS.ColWidth(intCol) = 300
grdMPS.FixedAlignment(intCol) = 1
ElseIf intCol = 1 Then
'Product Name column
grdMPS.Text = "Product Name"
grdMPS.ColWidth(intCol) = 2000
grdMPS.FixedAlignment(intCol) = 0
Else
'12 monthly data columns
grdMPS.Text = strMonths(intCol - 1, 1)
grdMPS.ColWidth(intCol) = 742
grdMPS.FixedAlignment(intCol) = 2
grdMPS.ColAlignment(intCol) = 1
End If
Next intCol
'Open the QueryDef and provide the value of the CategID parameter
Set qdfMPS = _
dbDecSupport.QueryDefs("qryMonthlyProductSalesParam")
qdfMPS!CategID = "1" 'for beverages
'Create the recordset object to supply data for the grid
Set rssMPS = qdfMPS.OpenRecordset(dbOpenSnapshot)
rssMPS.MoveLast
'Set the vertical dimensions of the grid control
grdMPS.Rows = rssMPS.RecordCount + 1
grdMPS.Height = grdMPS.RowHeight(0) * grdMPS.Rows + 460
'Don't exceed the space available on the form
If grdMPS.Height > 4095 Then
grdMPS.Height = 4095
End If
'Add the data from the recordset object to the grid
intRow = 1
rssMPS.MoveFirst
Do Until rssMPS.EOF
'Process one record at a time
For intCol = 0 To 13
'The table data is arranged in rows of columns
grdMPS.Row = intRow
grdMPS.Col = intCol
If Not IsNull(rssMPS.Fields(intCol)) Then
'Attempting to insert Null values causes an error
If intCol > 1 Then
grdMPS.Text = Format(CCur(rssMPS.Fields(intCol)), "#,##0")
Else
grdMPS.Text = rssMPS.Fields(intCol)
End If
End If
Next intCol
rssMPS.MoveNext
intRow = intRow + 1
DoEvents
Loop
rssMPS.Close
qdfMPS.Close
End Sub
If your queries return relatively few (less than 100) rows to a Grid control, the performance of your Visual Basic 4.0 application is likely to be much better than an identical application created with a DBGrid control bound to a Dynaset-type Recordset
of a Data control. As the number of rows increases, the DBGrid control gains the advantage because Jet only returns 100 or so records to Recordset objects of the Dynaset type before painting the subform. If you fill the Grid control in the Form_Load event
handler with the data from a few hundred or more rows, your form takes a substantial period of time to open. The next chapter shows you how to use the vertical scrollbar to control the amount of data that is transferred to the Grid control.
Although the DBGrid control requires much less VBA code, the basic Grid control is considerably more flexible. As an example, you can easily add row and column totals (called crossfooting) to a Grid control by adding a row and a column to the grid. You sum the values of each column and row as you iterate the columns and rows in your Do While. . .Loop structure. Crossfooting grids that contain time-series data is appreciated by users of summary decision-support forms.
This chapter introduced you to an almost-commercial Visual Basic 4.0 database application, the first such application to be presented in this book. The table-rollup, query-design, and user-interface guidelines discussed in this chapter can start you on
your way to creating production-decision support applications with Visual Basic 4.0. The forms included in the Ui_examp application are the basis of the designs of all of the decision-support examples in the chapters that follow.
The code examples that create the graphs, charts, and grid objects shown in this chapter demonstrate that you can create an attractive and quite functional Visual Basic 4.0 database decision-support application with relatively little code. The next
chapter shows you how to add additional functionality to your decision-support applications with data-aware control objects and more sophisticated coding techniques.
