Today you'll learn how to create reports that can be called from within your Visual Basic 4 programs. To do this, you'll use the Crystal Reports Writer, which ships with Visual Basic 4. You'll learn some basic concepts on how a report writer works, including the following:
When you have an understanding of the basics, you'll take a quick tour of Crystal Reports to learn how to create list reports. Finally, you'll learn how to use the Crystal Reports Control in Visual Basic 4 programs to run reports directly from your Visual Basic 4 applications.
The Crystal Reports Writer is a complete program that gives you the ability to define reports, save these report definitions to disk, and then run these report against databases in order to create final printouts. Crystal Reports has an added feature that lets you run the final reports from within your Visual Basic 4 application using the Crystal Reports Control, which ships with Visual Basic 4.
Throughout this day, you'll use Crystal Reports to illustrate concepts and to work out practice examples. Start Crystal Reports now and follow along through the rest of the day. You can start Crystal Reports in one of two ways: from the Visual Basic 4 main menu by selecting Add Ins | Report Designer, or by selecting the Crystal Reports icon from the Visual Basic 4 program group. If you have not already done so, start Visual Basic 4 and select Report Designer from the Add Ins menu. (See Figure 7.1.)
Starting Crystal Reports from Visual Basic 4.
When you start Crystal Reports, you'll see the Crystal Reports greeting dialog box. Press Proceed to Crystal Reports. You'll now see the main Crystal Reports screen (see Figure 7.2). This is where you create, modify, and run your reports.
NOTE: You will see the Crystal Reports Registration form the first time you load Crystal Reports. Complete this form and follow its instructions to register your software. After it is completed, you will not see this form again.
The Crystal Reports main screen.
Crystal Reports is a banded report writer. A banded report writer treats all output as "bands" of data. Each band has its own processes (such as functions it performs) and settings (properties) that you can manipulate in order to create the report layout and behaviors you need. Here are the main bands in Crystal Reports:
The header and footer bands contain information that appears at the top and bottom of every page of the report. This could be report titles, page numbers, print date and time, and so on. Every report has a header and a footer band.
The detail band contains the actual print lines. The detail band is the report version of a data table record. You use the report writer to lay out a detail band the same way you use a Visual Basic 4 form to lay out a data entry screen. Detail bands can have more than one physical line. However, detail bands only describe one logical record.
Within each band, you place fields to be displayed. Crystal Reports recognizes three types of fields:
Database fields are fields taken directly from data tables in the database you open when you first start Crystal Reports. You can load any database format recognized by the Visual Basic 4 database object (for example, Microsoft Access, FoxPro, dBASE), including ODBC data sources. You add fields by selecting them from a list of available fields and placing them in the desired location on the report form.
Text fields are fields that contain explicit text you want to appear on the report form. This text is not stored in a data table. An example of a text field is Print Date. If you want this text to appear at the top of every page, you would create a text field that contains it and then you would place it in the header band.
Formula fields are fields that are calculated results of either database fields or text fields (or a combination of both). Crystal Reports requires you to declare a formula field name and then allows you to use any existing text field, database, or other formula field as part of the new formula field. Formula fields can be numeric or character-based. For example, if you want to print the values Expiration Date: followed by the database field DataTable.Expire, but do not want to have to place two field objects in the detail band, you could create a single formula field, called ExpDate, that contains the following expression:
"Expiration Date: "+DataTable.Expire
Crystal Reports has several predefined formula fields available, along with a host of functions and operators that you can use to construct complex formulas, including the use of nested If statements to test data.
In the following sections, you'll begin a report definition to illustrate how bands and fields are used in Crystal Reports.
If you haven't already done so, start Crystal Reports from the Visual Basic 4 main menu bar. Select New | Report from the File menu and use the Open File dialog box to locate and load the CRYSRPT.MDB Microsoft Access database file. This file can be copied from the CD that ships with this book. It can be found in the TYSDBVB\CHAP07 directory. Your screen should look similar to the one shown in Figure 7.3.
Loading a database with Crystal Reports.
When the database is loaded, Crystal Reports creates a blank report definition and displays the list of available tables and fields in a box in the lower-right corner of the screen. (See Figure 7.4.)
Starting a new report with Crystal Reports.
First, you'll add a database field to the detail band of the report. Double-click the EntryNbr field in the Insert Database Field box, and then move the pointer onto the report form. You'll see that a rectangle outline appears, showing you where the database field will appear. Position the rectangle at the left corner of the detail band and click the mouse button once to drop the field on the report form. Notice that the field name automatically appears as a column title in the header band above the field in the detail band. Your report form should look like the one shown in Figure 7.5.
Placing a database field in the detail band.
Now you'll add the company name and address to the detail band. But before you do this, expand the detail band to accept more than one line of data. Move the pointer over the solid line that separates the detail band from the footer band. When the cursor turns to a double-sided arrow, press the left mouse button and pull down the detail band line to allow for several lines of data (just a rough guess will do fine). When you are satisfied that the detail area is large enough, release the mouse button to drop the detail band line. (See Figure 7.6 for a reference.)
Expanding the detail band.
Now, add the following fields to the detail area:
Place the CompanyName, Addr1, and Addr2 fields under each other and next to the EntryNbr field. Place the City, StateProv, and PostalCode fields together on one line. As you place each field in the detail band, you'll see the field names appear, one on top of another, in the header section. Delete all the field names except CompanyName. Refer to Figure 7.7 for sizing and placement of the database fields.
Placing the CompanyName and Address fields on the report.
Before going any further, you should save this report definition. Select Save As from the File menu. Enter COMAST1.RPT as the report name and then click OK to save the report. (See Figure 7.8.)
Saving the report form as COMAST1.RPT.
Now run the report to see whether everything is working. Select Print Preview from the File menu. Crystal Reports automatically opens the data table, loads the records, and sends the report to a display window. You should see something like the example shown in Figure 7.9.
Running the first report to a window.
Notice that you can use this window to scroll up and down the page, to "walk" through the pages of the report, to zoom in and out with the magnifying glass, and to send the report to the default printer. I cover these options in more depth later. For now, close the report.
You can add information that will appear at the top and bottom of every page by adding fields to the header and footer bands. Add a report title and date in the header band and a page number in the footer band.
You'll need to use a text field to create a report title for the header band. Select Text Field from the Insert menu and enter the text Company Master Report in the dialog box. (See Figure 7.10.)
Creating a text field.
Click the Accept button to store the text. Move the rectangle cursor to the top center of the header band and press the left mouse button to drop the text field on the header band. Your report form should look like the one shown in Figure 7.11.
Dropping the text field in the header band.
You can type text directly on the report form. This is easier than creating text fields, but it has its drawbacks. Once you type text on the form, you cannot move the text or resize it in any way. If you want to move the field later, you'll have to erase it and re-enter the data in the new location. If you use text fields, you can simply select the field and move it or resize it as needed.
To illustrate the process of adding text directly to the report form, move the cursor to the top-left corner of the report form and type Date:. Next, select Special Field | Print Date Field from the Insert menu. Now, move your rectangle cursor to a location near Date: and press the left mouse button to drop the report date onto the form. Your report form should now look like the one shown in Figure 7.12.
Adding direct text and a data field to the report.
You can also add page numbers to the footer band. This time, create a text field that contains the text Page:. Place this text field at the bottom of the footer band. Select Special Field | Page Number Field from the Insert menu, and then place this field next to the text field. Refer to Figure 7.13 for placement and sizing.
Adding page numbers to the report form.
Save and preview the report by selecting Print Preview from the File menu. You'll see the report title, print date, and page numbers display on each page of the report.
You need to add one more improvement to your report. Notice how the City, StateProv, and PostalCode fields print very far apart? You need to allow enough space for long city names, but you do not want to see lots of empty space on the form. What you need is a formula field that combines all three fields into a single field that has extra spaces removed.
Select Formula Field from the Insert menu. In the dialog box enter CityLine as the name for the formula field. (See Figure 7.14.)
Naming a new formula field.
After you click OK, you'll see the formula window. This is where you put together the details of the CityLine formula. You see the following four sections in this window:
You can type all the information into the Formula Text window, or you can use your mouse to point-and-click items from the Fields, Functions, and Operators windows through most of the formula-building process. The point-and-click method saves time and reduces typing errors.
You need to remove trailing spaces from the right of the fields, so start the formula by double-clicking the TrimRight() function from the Functions list. Notice that when you add a function to the Formula Text window, your cursor is positioned ready to insert the required parameters. Because the cursor is already between the two parentheses of the TrimRight() function, double-click City from the Fields list. Crystal Reports will place the field name (along with the data table name) inside the TrimRight() function. (See Figure 7.15.)
Adding the TrimRight() function and the City field.
You need to add a similar function that does the same thing to the StateProv database field. First, move the cursor in the Formula text box to the end of the formula string and enter a plus sign (+). Next, add another TrimRight() function and insert the StateProv field into the function. Compare your screen to the one shown in Figure 7.16.
Adding the TrimRight() function and the StateProv field.
Now you need to add the PostalCode field to the formula. You don't need to trim spaces from the PostalCode field, so just add the plus sign and select the PostalCode field. Your formula should look like the one shown in Figure 7.17.
Adding the PostalCode field to the formula.
Before you save the field, you can check the syntax by using the Check button. When you click the Check button, Crystal Reports checks the formula for any errors and then reports the results in a message box. (See Figure 7.18.)
Checking the formula.
If you have no errors, press the Accept button. Crystal Reports will return you to the report form. You are ready to place the newly constructed formula field on the form. Place the new field anywhere on the report form (wherever you have space). Next, delete the City, StateProv, and PostalCode fields from the detail band. You can do this by selecting all three fields with the mouse button and then pressing the Delete key on your keyboard. When all three fields are gone, move the CityLine formula field into place in the detail band. Your form should look like the one shown in Figure 7.19.
Placing the CityLine formula field on the report form.
Save and run the report. You'll see that there are no spaces between the City, StateProv, and PostalCode fields. But you need some spaces, right? You need to edit the formula field in order to insert a comma and a space between the City and the StateProv fields and to insert two spaces between the StateProv and the PostalCode fields.
To edit an existing formula field, select the field by clicking it with the mouse. Then, select Formula from the Edit menu. Crystal Reports will present you with the formula window with the CityLine formula already loaded. Go directly to the Formula text box and make the needed changes to the formula. Your formula should now look like the one shown in Figure 7.20.
Editing the CityLine formula.
Press the Accept button to save the formula; then save and run the report. You now see a much better looking final line on the address.
Crystal Reports is a great tool for putting together simple list reports. It is also excellent for creating a wide variety of labels, including mailing labels, name tags, diskette labels, and others as well. What follows is a quick tour of Crystal Reports. For a more in-depth treatment of Crystal Reports, refer to the documentation that ships with Visual Basic 4.
The items in the File menu allow you to define new reports, open existing reports, save reports, print the current report, and set program-level options such as default directories, default display formats, and default database formats. Table 7.1 contains a summary of the menu items and their uses.
Table 7.1. Crystal Reports File menu options.
| Menu Option | Description |
|---|---|
| New | Report... | Use this to create a brand new report. You are first prompted to select a database (even if you already have one open). When the database is open, you can assemble a basic report by selecting fields from a list box. |
| New | Cross-Tab... | This selection brings up a dialog box that will help you build a cross-tab report. This type of report arranges data in rows and columns, similar to those of a spreadsheet. |
| New | Mailing Labels Report | Use this selection to create a label. There are many default label formats, including user-defined. You are first prompted to select a database and then select (or define) a label format. |
| Open... | This selection prompts you to open an existing Crystal Reports report definition (*.RPT). When the definition is open, you can edit the report and save the changes. |
| Save | Use this selection to save the report definition to the current report name. If no name exists, you will be prompted to supply one. The default file extension is .RPT. |
| Save As... | Use this option to save an existing report under a new name. It is handy if you want to use an existing report as a "template" for creating a new, slightly different report. |
| Close | This selection closes the current report. If you have made changes, you will be asked if you want to save the report definition before it is closed. |
| Print Preview | This selection displays the report on-screen for your review. |
| Print | Printer | This selection sends the current report to the attached printer. See the Printer menu for more options. |
| Print | Export | This selection allows you to print to a file in numerous formats, including Lotus and Excel. |
| Print | Report Definition | This selection prints an abstract of the current report. Information is displayed regarding the fields, headers, database, formulas, and other items placed on the report. |
| Printer Setup | This selection displays a dialog box of the current printer settings. |
| Page Margins | This selection allows you to set the top, bottom, left, and right margins of the report. |
| Options... | Use this item to set program-level defaults for all reports. You can set defaults for the directory to which reports are saved and the directory from which databases are read. You can also set the default
database and index formats. You can set the default display formats for string, numeric, currency, date, and boolean data formats. You can also set the default fonts for the header, footer, detail, group, and total bands. There are also several preference settings that control how Crystal Reports displays menu bars, fields on a report, and so on. |
| Exit | This selection exits Crystal Reports. If you have made any changes to any open report definition, you will be asked whether you want to save the changes before exiting. |
The Edit menu contains the usual Cut, Copy, Paste, and Clear options, plus several other options that allow you to edit formulas, text fields, and summary and group bands. See Table 7.2 for a brief summary of the Edit menu.
Table 7.2. Crystal Reports Edit menu options.
| Menu Option | Description |
|---|---|
| Cut | Use this selection to cut out selected text. This only works for text that is placed directly on the report form. It does not work for any field type objects (database, text, or formula). |
| Copy | Use this selection to copy selected text from your report form to the Clipboard. This copies text that was placed directly onto a form and does not work for any field type objects (database, text, or formula). |
| Paste | Use this selection to paste text from the Clipboard directly into your report form. This does not place the selected text into database, formula, or text fields. |
| Paste Special | This selection allows you to use the Windows Clipboard to copy information from other applications and place the information into your Crystal Report. Objects can either be embedded or linked. If they are linked, changes in the source will flow through to your report. |
| Select Fields | This item allows you to use a "lasso" to draw a rectangle around and select an entire group of objects. You can accomplish the same effect by holding the Shift key as you select objects; however, this process is not as quick. |
| Formula... | Use this option to edit an existing formula field. First, you must select the formula field to edit; then you select this menu item to call up the formula editor. |
| Text Field... | Use this option to edit an existing text field. First, you must select the text field to edit; then you select this menu item to call up the Text Field edit box. |
| Summary Field... | Use this option to edit an existing summary operation field. First, you must select the summary field to edit; then you select this menu item to call up the Summary Operation dialog box. Summary options include Sum, Average, Min, Max, Count, Variance, and Standard Deviation. |
| Browse Field Data... | Use this option to view a list of all the possible values in a data field. First, you must select the data field on the report form to browse; then you select this menu item to see a list box containing all the unique values for this field. You'll also see field definition information in the upper-left corner of the list box (string type, length 30, and so on). This is handy if you want to review the data behind the form while you are constructing a report. |
| Show/Hide Sections... | This option displays a dialog box that allows you to hide or display different sections of your report. This option is also available by pressing the right mouse button while on a section heading. |
| Send Behind Others | Use this command to access fields on the report form that are stacked underneath each other. It is common practice to place more than one field in the same location on the form and allow criteria in the report to decide which of the stacked fields actually gets displayed. This menu item allows you to work down the stack in order to find the field you want to edit. |
| Group Section... | Use this menu item to edit an existing group section. When you select this menu item, you'll be presented with a list box showing all the existing groups. Select the group you want to edit; then you'll see a dialog box that lets you set the grouping field and the sort order (ascending or descending). |
| Delete Section... | Use this menu item to delete an existing group from the report. |
| Object | This menu item allows you to edit an OLE object that you have embedded in your report. An object must first be selected before you can choose this item. The types of objects within your report will appear at the bottom of the Edit menu. Each object type will have submenus of actions that can be performed on that type of object. |
| Links... | This menu item allows you to update and change links to objects embedded within your report. |
The Insert menu allows you to add database, text, and formula fields to your report definition. You can also add graphic images, lines, and boxes. Crystal Reports gives you shortcuts to add page numbers, record numbers, print date, and group numbers to the report definition. The Insert menu is the menu you use to add new sections, subtotal bands, summary bands, and the report grand total band. Table 7.3 provides a short summary of the Insert menu options.
Table 7.3. Crystal Reports Insert menu options.
| Menu Option | Description |
|---|---|
| Database Field... | Use this option to select a field from the attached database. You can select any field in any table. You can select the same field more than once. |
| Text Field... | Use this option to create a text field for your report form. Once you create the text field, you can manipulate the format, font, and color the same way you can in a database field. |
| Formula Field... | Use this option to create a new formula field or edit an existing formula field. Select this menu item and you'll see a list box showing all the formula fields defined for this report. If you double-click one of the fields in the list, you'll see the formula editor with the selected formula loaded, ready for editing. If you type in a new formula name, you'll see the formula editor ready for you to create a new formula. |
| Special Field | Page Number Field | Use this option to create a page number field for your report form. This field will always report the current page number. |
| Special Field | Record Number Field | Use this option to create a record number for your report. This field will always report the current sequential record number in the selected records as sorted by the report. This does not report the position of the record in the physical table, it reports the position of the record in the sorted report list. |
| Special Field | Group Number Field | Use this option to create a group number field to place on your reports. This field can be used to report counts of group breaks within the report. |
| Special Field | Print Date Field | Use this option to create a Today's Date field to place on your report form. This field reports the date on which the report is printed. |
| Subtotal... | Use this option to create a subtotal band for your report form. First, you must select a numeric field to subtotal. Once you create a subtotal field, a new section will automatically be created (if it does not already exist). You can select the grouping field to use for each subtotal as well as the sort order of the grouping field. |
| Grand Total... | Use this menu item to create a grand total band on your report form. First, you must select a field to total; then you select this menu item. You do not have to select a numeric field for the grand total band because the grand total band can report a count as well as a numeric total. |
| Summary... | Use this option to insert fields for counts, sums, averages, minimums, maximums, sample variances, sample standard deviations, population variances, or population standard deviations of selected fields. |
| Group Section... | This option allows you to set the points at which your report will break and total. |
| Line | Use this item to draw lines on your report. Once a line has been placed on the report, you can resize it using the mouse pointer. You can set the line thickness, type, and color by double-clicking anywhere on the line to call up a dialog box. |
| Box | Use this item to place a box anywhere on the report form. You can use the mouse pointer to resize the box. When you double-click the selected box, you'll call up a dialog box that lets you set the border style, thickness, and color. You can also set the fill color of the box. |
| Graphic... | Use this item to place a bitmap graphic image on your report. When you select this item you'll be shown the Choose Graphics dialog box, which you use to locate a graphic image file. Once you select a file and press OK, Crystal Reports will allow you to place and size that image anywhere on the report. |
| Object | This option allows you to select and insert an OLE object into your report. You can either embed or link the object. |
The Format menu gives you options for changing the font, borders, color, and display format of existing fields. You use this menu to edit the graphic, line, and box objects on you report. You can also set formatting options for existing section bands of your report. Table 7.4 provides a short summary of the Format menu options.
Table 7.4. Crystal Reports Format menu options.
| Menu Item | Description |
|---|---|
| Font... | Use this menu item to edit the font attributes of the selected fields. You must first select one or more fields; then you select this menu item. |
| Field... | Use this menu item to change the display format of the selected field. Different dialog boxes appear depending on the field type selected. |
| Border and Colors... | Use this menu option to set field colors, to set borders around fields, to set the width of the borders, to add shadows to the borders, and so on. |
| Graphic... | Use this item to modify the sizing, scaling, and positioning of graphic images loaded from the Insert | Graphics menu item. (See Table 7.3.) |
| Line | Use this menu item to modify the thickness, type, and color of existing line objects on the report form. |
| Box | Use this menu item to modify the attributes of a box object already on the report form. |
| Section... | Use this menu item to set attributes of all the sections (bands) of the report. There are several attributes that can be set from this dialog box, but not all apply to all objects. They are Hide Section, Print at Bottom of Page, New Page After, New Page Before, Reset Page Number After, Keep Section Together, Suppress Blank Lines, Format with Multi-Columns. |
The Database menu can be used to set and update table links, to add and remove database files, to establish table aliases to correct naming conflicts, to make sure the data set currently being used by Crystal Reports is updated, and to log on or off ODBC data sources. Table 7.5 provides a short summary of the Database menu options.
Table 7.5. Crystal Reports Database menu options.
| Menu Option | Description |
|---|---|
| Links... | Use this menu option to create links between data tables. When you select this item, you will be presented with a dialog box that allows you to add, update, or delete file links. A link allows you to associate a field in one table to a field in another table, thus linking the two files. This allows you to establish one-to-one or one-to-many relationships using Crystal Reports. |
| Add Database to Report... | Use this menu item to add additional database files to your report definition. It is possible to have more than one Microsoft Access database as a source for your report definition. |
| Remove from Report... | Use this menu item to remove a database file from your report definition. |
| Set Location... | This option allows you to set the physical location of the databases being used in your report. This option is extremely helpful if databases are moved or if network mappings vary for the users of your reports. |
| Set Alias... | Use this menu item to establish helpful alias names for the tables in your report. Using meaningful alias names can make it easier to maintain your reports in the future. |
| Verify Database | Selecting this menu item forces Crystal Reports to refresh all data tables used in the report. This is a one-time action that makes sure you have the most up-to-date data to work with for your report. |
| Verify on Every Print | This menu item is a toggle on/off option. When the item is toggled on, Crystal Reports performs a refresh each time it runs the report. This is an "automated" version of the Verify Database menu option. |
| Log On Server | Use this menu item to attach an ODBC data source to the report definition. |
| Log Off Server | Use this menu item to detach an ODBC data source from the report definition. |
| Show SQL Query | Use this option to view the SQL query that Crystal Reports is generating and sending to your data source. You can edit the query that displays. |
The Report menu contains all the options for record selection, grouping, and sorting. Also available are options for database refreshing and report defaults. Table 7.6 provides a short summary of the Report menu options.
Table 7.6. Crystal Reports Report menu options.
| Menu Option | Description |
|---|---|
| Select Records | Use this menu item to create record-level selection criteria for your report. You must first select a report field that you will use as the start of your criteria. You are then prompted to select from a list of criteria, which includes >, <, =, as well as other operators. |
| Edit Record Selection Formula | This function is similar to the Select Records menu item. The difference is that this function allows you to work in the Crystal Reports script language in order to write your Pick criteria. |
| Record Sort Order | Use this menu item to establish the sort order of the report. When you select this item, you'll see a sort dialog box that lists all the possible sort fields on the left and shows the selected sort order on the right. You can use more than one report field in the sort criteria. Also, you can indicate an ascending or descending sort at the field level. |
| Select Groups | Use this menu item to establish selection criteria for report groups (bands). This option is similar to the Select Records option. The criteria built here is applied to the designated group field each time it is printed. In this way, you can set selection criteria for your report that is actually the result of the subtotaling of the data. |
| Edit Group Selection Formula | Use this menu item to develop your own group selection formula using the formula editor. This is useful if you want to build a selection criteria that does not easily fit into the options available in the Select Groups menu option. |
| Group Sort Order | Use this item to establish the sort order of the existing report groups (bands). You'll see a dialog box similar to the one used in the Record Sort Order menu item. Use this option to control the order in which groups appear on your report. |
| Refresh Report Data | Crystal Reports only automatically retrieves data only under a few circumstances while in print preview mode. Use this option to reload your data if you expect that it has changed since the last time you previewed the report. |
| Save Data with Closed Report | This option allows you to save the data records with the report definition. This is a handy feature if you want to archive information. It does (as you would expect) take more disk space to save a report with data records. |
| Report Title | Use this option to set the title bar text that appears on your report. |
| Set Print Date | Use this option to set the date that will appear in all date fields on your report. |
Once you have developed and saved your report definition using Crystal Reports, you are ready to modify your Visual Basic 4 program to run the report from within your Visual Basic 4 application. Throughout the rest of this day you'll be modifying the data entry form you built in Day 6, "Input Validation." If you haven't done so yet, start Visual Basic 4 and load the CompanyMaster data entry program you created in Day 6.
It is very easy to run reports defined using Crystal Reports from Visual Basic 4. Crystal Reports for Visual Basic 4 ships with a special control (the Crystal Reports Control) that can be added to any Visual Basic 4 form. Once you drop the control on your form, you only need to set a few properties to get a report printout from your program. The control has several properties that you can alter when setting up or running a report.
First, place the Crystal Reports Control onto the CompanyMaster data entry form. It doesn't matter where you place the control because it's invisible at runtime. Once you have placed the control on the form, set the ReportFileName property to C:\TYSDBVB\CHAP07\COMAST1.RPT and the WindowTitle property to Company Master Report. Leave CrystalReport1 as the default name for the control. Next, add a command button to the form. Refer to Figure 7.21 for placement and sizing.
Adding the Print command button.
Use the information in Table 7.7 to set the properties of the command button.
Table 7.7. Property settings for the Print command button.
| Control | Properties | Settings |
|---|---|---|
| Command Button | Name Caption FontBold Height Left Top Width | cmdPrint False 330 7380 4140 1200 |
Now add the following line of code behind the cmdPrint_Click event (this code line starts Crystal Reports):
Sub cmdPrint_Click () CrystalReport1.Action = 1 'force Crystal Reports to run report End Sub
Now save and run the program. When you click the Print button, Crystal Reports creates the report and sends it to a screen window. You can preview this report in the window and, if you like, use the Print button on the report window toolbar to send the report to the printer. (See Figure 7.22.)
Viewing the report from Visual Basic 4.
There are a handful of report parameters that you can set using the Crystal Reports Control. Instead of setting them in Visual Basic 4 code, you'll create a simple report dialog box that can be used to set the most common parameters. This dialog box will be portable, so you'll be able to use it in any future Visual Basic 4 program.
Use Figure 7.23 and the information in Table 7.8 to construct a generic Print Report dialog box. Please note that you will be adding text boxes that have their Visible property set to False, meaning that they won't appear at runtime. These controls should simply be placed in a convenient position (such as the open space between the Printer Setup and Exit command buttons).
Building a generic Print Report dialog box.
Table 7.8. Control information for the Print Report dialog box.
| Control | Property | Setting |
|---|---|---|
| Form | Name BackColor Caption Height Width Save As | frmReport Light Gray Print Report 2475 6540 Report.frm |
| Command Button | Name Caption FontBold Height Left Top Width | cmdOK &OK False 330 5100 240 1200 |
| Command Button | Name Caption FontBold Height Left Top cmdPrnSetup Width | Printer Setup... False 330 5100 720 1200 |
| Command Button | Name Caption FontBold Height Left Top Width | cmdExit E&xit False 330 5100 1560 1200 |
| SSPanel | BorderWidth Caption Height Left Top Width | 1 (blank) 1815 120 120 4815 |
| Text Box | Name Visible | txtReportName False |
| Text Box | Name Visible | txtWindowTitle False |
| Text Box | Name Visible | txtReportDBName False |
| Common Dialog | Left Top | 5700 1 |
| Crystal Report | Left Top | 5200 1 |
| SSFrame | Caption FontBold Height Left Top Width | Destination False 1515 120 120 1200 |
| SSOption | Name Caption FontBold Height Left Top Width | opt3dDest(0) Window False 330 120 240 1000 |
| SSOption | Name Caption FontBold Height Left Top Width | opt3dDest(1) Printer False 330 120 660 1000 |
| SSOption | Name Caption FontBold Height Left Top Width | opt3dDest(2) File False 330 120 1080 1000 |
| Combo Box | Name FontBold Left Style Top Width | cboFileTypes False 2475 2 - DropDown 240 2220 |
| Text Box | Name FontBold Height Left Text Top Width | txtFileName False 330 2475 (blank) 720 1995 |
| Command Button | Name Caption Height Left Top Width | cmdFileName "?" 330 4440 720 260 |
| Text Box | Name FontBold Height Left Text Top Width | txtCopies False 330 2475 1 1140 600 |
| Label | BackStyle Caption FontBold Height Left Top Width | Transparent File Type: False 330 1680 240 900 |
| Label | BackStyle Caption FontBold Height Left Top Width | Transparent File Name: False 330 1680 720 900 |
| Label | BackStyle Caption FontBold Height Left Top Width | Transparent Copies: False 330 1680 1140 900 |
After you have constructed the form, you need to add some code behind the form. First, declare two form-level variables in the declarations section. You'll use these variables to set the properties of the Crystal Reports Control:
Option Explicit Dim cFileName As String Dim cReportName As String
The LoadFileTypes procedure loads the various report file types recognized by Crystal Reports into a drop-down combo box. Add this procedure to your project:
Sub LoadFileTypes () ' ' load type selections cboFileTypes.Clear cboFileTypes.AddItem "Record" cboFileTypes.AddItem "Tab Separated" cboFileTypes.AddItem "Text" cboFileTypes.AddItem "DIF" cboFileTypes.AddItem "CSV" cboFileTypes.AddItem "*RESERVED*" cboFileTypes.AddItem "Tab Separated Text" End Sub
The code in the Form_Activate event initializes the form caption and the Crystal Reports window caption. It also checks to see that a report name and database name have been passed to the form. This is where you load the combo box, too.
Sub Form_Activate ()
'
' fix up form caption
If Len(Trim(Me.txtWindowTitle)) = 0 Then
Me.txtWindowTitle = "Print Report"
End If
Me.Caption = Me.txtWindowTitle
'
' check for passed database name
If Len(Trim(Me.txtReportDBName)) = 0 Then
MsgBox "Missing Database Name!"
Unload Me
End If
'
' check for passed report name
If Len(Trim(Me.txtReportName)) = 0 Then
MsgBox "Missing Report Name!"
Unload Me
End If
'
' set default copies
txtCopies = 1
'
LoadFileTypes ' fill drop down list box
End Sub
The following code section handles the selection of the report destination. Notice that this code toggles the enabled/disabled properties of the file-related controls. The controls are kept disabled unless the user select the "file" destination option. Here's the code:
Sub opt3dDest_Click (Index As Integer, Value As Integer)
Dim nFile As Integer
'
' send report to window
If opt3dDest(0) = True Then
CrystalReport1.Destination = 0
nFile = False
End If
'
' send report to printer
If opt3dDest(1) = True Then
CrystalReport1.Destination = 1
nFile = False
End If
'
' send report to file
If opt3dDest(2) = True Then
CrystalReport1.Destination = 2
nFile = True
End If
'
' enable/disable file controls
txtFileName.Enabled = nFile
cboFileTypes.Enabled = nFile
cmdFileName.Enabled = nFile
End Sub
The next section of code calls the Visual Basic 4 common dialog box to allow the user to select a file name as the destination for the report output. Notice the use of the &H2 value in the Flags property. This forces the common dialog box to issue a warning message if the user selects a filename that already exists. Once a valid file is selected, it is loaded into a form-level variable for later use. Here's the code:
Sub cmdFileName_Click ()
'
' set some parms
CMDialog1.DialogTitle = "Save Report File Name"
CMDialog1.Filter = "Text (*.txt)|*.txt|"
CMDialog1.Flags = &H2
'
' run the save as dialog
CMDialog1.Action = 2
'
' load the selected filename into control
If Len(CMDialog1.Filename) > 0 Then
cFileName = CMDialog1.Filename
End If
Me.txtFileName = cFileName
End Sub
You need a bit of code to enable the Print Setup command button. Notice that you set the Flags property to &H40. This forces the common dialog box to display the Printer Setup dialog box. Here's the code:
Sub cmdPrnSetup_Click () CMDialog1.Flags = &H40 ' force the printers setup dialog box CMDialog1.Action = 5 ' run the printer setup End Sub
The code for the OK command button is the most involved of the form. This routine performs input validation, sets final report properties, sets up an error trap, and then runs the Crystal Reports report. The input validation should look familiar. Note that an additional input validation had to be invented to check the combo box. After the validation pass, a few properties of the report control are set. Afterwards, the report is generated.
The report run is wrapped in an error trapping routine. I go into error trapping in greater detail next week. For now, you should note that after the error trap is turned on, the code attempts to erase the output file name, if necessary, and then runs the report. Once the report is done, the error trapping is turn off.
The error routine is simple. It displays a Message box for any encountered error, except for the error that occurs when the user attempts to erase a file that does not exist. Here's the code:
Sub cmdOK_Click ()
Dim nOK As Integer ' validation results
Dim nVldErr As Integer ' validatoin pass/fail flag
Dim cMsg As String ' report error string
Dim cTitle As String ' error title
'
' peform validation
If CrystalReport1.Destination = 2 Then
nOK = IsValid(txtFileName, "Save File Name", True)
If nOK = False Then
nVldErr = True
End If
'
If cboFileTypes.ListIndex = -1 Then
MsgBox "Missing Print File Type", 0, "Validation Error"
cboFileTypes.SetFocus
nVldErr = True
End If
End If
'
' did we find an error?
If nVldErr = True Then
GoTo OKExit ' leave now!
End If
'
' set some final parameters
CrystalReport1.WindowTitle = txtWindowTitle ' set the window title
CrystalReport1.DataFiles(0) = txtReportDBName ' set the database location
CrystalReport1.ReportFileName = txtReportName ' set the report location
CrystalReport1.CopiesToPrinter = txtCopies ' set the copies parm
'
' if it's going to a file
If cboFileTypes.ListIndex <> -1 Then
CrystalReport1.PrintFileType = cboFileTypes.ListIndex
CrystalReport1.PrintFileName = txtFileName
End If
'
On Error GoTo ReportErr ' set error trap
Kill txtFileName ' delete file if it's there
CrystalReport1.Action = 1 ' run report
On Error GoTo 0 ' trun off error trap
GoTo OKExit ' exit sub
'
' report any error you get
ReportErr:
If Err <> 53 Then ' skip file not found msg
'
' see if the error is from CRW
If CrystalReport1.LastErrorNumber <> 0 Then
cMsg = Str(CrystalReport1.LastErrorNumber)
cMsg = cMsg + ":" + CrystalReport1.LastErrorString
cTitle = "Crystal Reports Error"
Else
'
' error was from VB
cMsg = Str(Err) + ":" + Error$(Err)
cTitle = "Visual Basic Error"
End If
'
' show the error # and text
MsgBox cMsg, 0, cTitle
End If
Resume Next
'
' end of this procedure
OKExit:
End Sub
Finally, you need a bit of code behind the Exit button:
Sub cmdExit_Click () Unload Me End Sub
Save the form as REPORT.FRM before you continue. You now need to make a few changes to the code behind the Print button on the CompanyMaster data entry form. You'll get rid of the Crystal Reports Control on the main form since you have one on the Print Report dialog box now. You'll also set two parameters behind the Print button before you call the Print Report dialog box.
TIP: Notice in the following code that you load the form (without showing it), then set the values of the form's controls, and then show the form modally. This is a good way to pass parameters between forms—load it, pass them, show it.
Sub cmdPrint_Click () ' ' load the next form Load frmReport ' ' set values on the next form frmReport.txtReportDBName = "c:\tysdbvb\chap07\Crysrpt.mdb" frmReport.txtReportName = "c:\tysvbdb\chap07\comast1.rpt" frmReport.txtWindowTitle = "Company Master Report" ' ' show the form modally frmReport.Show 1 End Sub
Now save and run the project. You should see the Company Master Report dialog box prompting you to set parameters for your print job. (See Figure 7.24.)
Running the Company Master Print Report dialog box.
Not only have you finished a report routine for the CompanyMaster project you have been working on this week, but you also have the Print Report dialog box, which can be used in any future Visual Basic 4 project that uses the Crystal Reports report writer.
Today you have learned how to use the Crystal Reports report writer to create a simple list report using the data tables you created earlier in the week. You have also learned that Crystal Reports is a banded report writer. Here are the main bands in a report:
You have also learned that Crystal Reports recognizes three types of fields on the report form:
You have also learned how to use the Crystal Reports Control to run a report from within your Visual Basic 4 program. Finally, you created the Print Report dialog box, which lets you control the report destination, the file type, and the number of copies printed.
Print the report and the report definition when you have finished the layout.
