Control Break Concepts
Control break processing is a technique used to process a sorted sequential file that contains groups of records with common values in one or more fields which are designated as the control fields. An example of a control field would be the sale date in a file of sales records for a particular month. Suppose you had eight sales on the first day of the month, and ten sales on the second day of the month. The set of eight records for day one and the set of ten records for day two each form a control group. So a control group is a set of records that all have the same value in the control field. As your program process the sales records, it notices that the date has changed between the eighth and ninth record. This change in the value of the control field is called a control break. When the control break is detected, the program will typically perform an action for the previous control group, such as print a subtotal for that group.
Control break processing typically comes into play when writing report programs "manually" (i.e., without the benefit of a report designer such as Crystal Reports, the VB Data Report Designer, the MS-Access Report Designer, or others). Such report designers handle all of this control break stuff behind the scenes while you visually prepare the format of the report. Writing report programs manually (i.e., handling all details of the printing and subtotalling logic yourself within the program) is typically done in mainframe environments using a language like COBOL. So why bother with report program logic and control break logic in VB? Well, there are times when a plain-text printed report will get the job done and you can avoid the processing overhead of using a report designer. And even if you never have to write a report program "manually" (using native VB I/O), knowledge of control break processing can come in handy for other programming tasks, such as populating a TreeView control or generating an XML file.
In this article we will examine control break logic in the context of processing a sequential file of sales order data for the Northwind Traders company. (Note: This data was extracted from the Northwind sample database that Microsoft provides with VB.) We will also examine good old fashioned report program logic (come on, gang, let's step into that wayback machine!).
The Northwind sales file is a comma-delimited file consisting of the following fields:
· Year-Month (YYYYMM format). For example, if the sale occurred in October 2004, the value would be "200410".
· Order Date (The date on which the sale was made, M/D/YYYY format).
· Order Number (5-digit order identifier)
· Customer ID (5-character customer code identifier)
· Product Description (textual description of the product sold, 32 characters max)
· Unit Price (numeric unit price of the product sold)
· Quantity (integer representing the quantity of the product sold)
· Discount Percentage (Numeric decimal value representing the percent discount given to the customer on the item, if any. Could be 0, or 0.1 for 10%, 0.25 for 25%, etc.)
When the sample programs process this file, fields from each input record will be printed along with three calculated values:
· Extended Price (the base amount of a sale line item, calculated as Unit Price * Quantity)
· Discount Amount (the amount "off", calculated as Extended Price * Discount Percentage)
· Sale Total (the total amount for a sale line item, calculated as Extended Price – Discount Amount)
The content of the input file (NWSALES.TXT) is shown below:
200410,10/3/2004,10295,VINET,Gnocchi di nonna Alice,30.4,4,0
200410,10/4/2004,10296,LILAS,Pavlova,13.9,30,0
200410,10/4/2004,10296,LILAS,Gudbrandsdalsost,28.8,15,0
200410,10/4/2004,10296,LILAS,Queso Cabrales,16.8,12,0
200410,10/5/2004,10297,BLONP,Mozzarella di Giovanni,27.8,20,0
200410,10/5/2004,10297,BLONP,Chartreuse verte,14.4,60,0
200410,10/6/2004,10298,HUNGO,Chang,15.2,40,0
200410,10/6/2004,10298,HUNGO,Inlagd Sill,15.2,40,0.25
200410,10/6/2004,10298,HUNGO,Raclette Courdavault,44,30,0.25
200410,10/6/2004,10298,HUNGO,Tarte au sucre,39.4,15,0
200410,10/7/2004,10299,RICAR,Teatime Chocolate Biscuits,7.3,15,0
200410,10/7/2004,10299,RICAR,Outback Lager,12,20,0
200410,10/10/2004,10300,MAGAA,Louisiana Hot Spiced Okra,13.6,30,0
200410,10/10/2004,10300,MAGAA,Scottish Longbreads,10,20,0
200410,10/10/2004,10301,WANDK,Boston Crab Meat,14.7,10,0
200410,10/10/2004,10301,WANDK,Gnocchi di nonna Alice,30.4,20,0
200410,10/11/2004,10302,SUPRD,Alice Mutton,31.2,40,0
200410,10/11/2004,10302,SUPRD,Rössle Sauerkraut,36.4,28,0
200410,10/11/2004,10302,SUPRD,Ipoh Coffee,36.8,12,0
200410,10/12/2004,10303,GODOS,Boston Crab Meat,14.7,40,0.1
200410,10/12/2004,10303,GODOS,Louisiana Fiery Hot Pepper Sauce,16.8,30,0.1
200410,10/12/2004,10303,GODOS,Scottish Longbreads,10,15,0.1
200410,10/13/2004,10304,TORTU,Maxilaku,16,30,0
200410,10/13/2004,10304,TORTU,Raclette Courdavault,44,10,0
200410,10/13/2004,10304,TORTU,Fløtemysost,17.2,2,0
200410,10/14/2004,10305,OLDWO,Carnarvon Tigers,50,25,0.1
200410,10/14/2004,10305,OLDWO,Thüringer Rostbratwurst,99,25,0.1
200410,10/14/2004,10305,OLDWO,Chartreuse verte,14.4,30,0.1
200410,10/17/2004,10306,ROMEY,Nord-Ost Matjeshering,20.7,10,0
200410,10/17/2004,10306,ROMEY,Perth Pasties,26.2,10,0
200410,10/17/2004,10306,ROMEY,Tourtière,5.9,5,0
200410,10/18/2004,10307,LONEP,Scottish Longbreads,10,3,0
200410,10/18/2004,10307,LONEP,Tarte au sucre,39.4,10,0
200410,10/19/2004,10308,ANATR,Outback Lager,12,5,0
200410,10/19/2004,10308,ANATR,Gudbrandsdalsost,28.8,1,0
200410,10/20/2004,10309,HUNGO,Chef Anton's Cajun Seasoning,17.6,20,0
200410,10/20/2004,10309,HUNGO,Grandma's Boysenberry Spread,20,30,0
200410,10/20/2004,10309,HUNGO,Singaporean Hokkien Fried Mee,11.2,2,0
200410,10/20/2004,10309,HUNGO,Ipoh Coffee,36.8,20,0
200410,10/20/2004,10309,HUNGO,Fløtemysost,17.2,3,0
200410,10/21/2004,10310,THEBI,Pavlova,13.9,10,0
200410,10/21/2004,10310,THEBI,Tarte au sucre,39.4,5,0
200410,10/21/2004,10311,DUMON,Gudbrandsdalsost,28.8,7,0
200410,10/21/2004,10311,DUMON,Singaporean Hokkien Fried Mee,11.2,6,0
200410,10/24/2004,10312,WANDK,Rhönbräu Klosterbier,6.2,10,0
200410,10/24/2004,10312,WANDK,Perth Pasties,26.2,20,0
200410,10/24/2004,10312,WANDK,Rössle Sauerkraut,36.4,4,0
200410,10/24/2004,10312,WANDK,Ipoh Coffee,36.8,24,0
200410,10/25/2004,10313,QUICK,Inlagd Sill,15.2,12,0
200410,10/26/2004,10314,RATTC,Mascarpone Fabioli,25.6,40,0.1
200410,10/26/2004,10314,RATTC,Escargots de Bourgogne,10.6,30,0.1
200410,10/26/2004,10314,RATTC,Tarte au sucre,39.4,25,0.1
200410,10/27/2004,10315,ISLAT,Outback Lager,12,30,0
200410,10/27/2004,10315,ISLAT,Sasquatch Ale,11.2,14,0
200410,10/28/2004,10316,RATTC,Tarte au sucre,39.4,70,0
200410,10/28/2004,10316,RATTC,Jack's New England Clam Chowder,7.7,10,0
200410,10/31/2004,10317,LONEP,Chai,14.4,20,0
200411,11/1/2004,10318,ISLAT,Jack's New England Clam Chowder,7.7,20,0
200411,11/1/2004,10318,ISLAT,Lakkalikööri,14.4,6,0
200411,11/2/2004,10319,TORTU,Alice Mutton,31.2,8,0
200411,11/2/2004,10319,TORTU,Rössle Sauerkraut,36.4,14,0
200411,11/2/2004,10319,TORTU,Lakkalikööri,14.4,30,0
200411,11/3/2004,10320,WARTH,Fløtemysost,17.2,30,0
200411,11/3/2004,10321,ISLAT,Steeleye Stout,14.4,10,0
200411,11/4/2004,10322,PERIC,Filo Mix,5.6,20,0
200411,11/7/2004,10323,KOENE,NuNuCa Nuß-Nougat-Creme,11.2,4,0
200411,11/7/2004,10323,KOENE,Chartreuse verte,14.4,4,0
200411,11/7/2004,10323,KOENE,Genen Shouyu,12.4,5,0
200411,11/8/2004,10324,SAVEA,Pavlova,13.9,21,0.15
200411,11/8/2004,10324,SAVEA,Steeleye Stout,14.4,70,0.15
200411,11/8/2004,10324,SAVEA,Spegesild,9.6,30,0
200411,11/8/2004,10324,SAVEA,Raclette Courdavault,44,40,0.15
200411,11/8/2004,10324,SAVEA,Vegie-spread,35.1,80,0.15
200411,11/9/2004,10325,KOENE,Konbu,4.8,12,0
200411,11/9/2004,10325,KOENE,Mozzarella di Giovanni,27.8,40,0
200411,11/9/2004,10325,KOENE,Tofu,18.6,9,0
200411,11/9/2004,10325,KOENE,Grandma's Boysenberry Spread,20,6,0
200411,11/9/2004,10325,KOENE,Gorgonzola Telino,10,4,0
200411,11/10/2004,10326,BOLID,Chef Anton's Cajun Seasoning,17.6,24,0
200411,11/10/2004,10326,BOLID,Ravioli Angelo,15.6,16,0
200411,11/10/2004,10326,BOLID,Rhönbräu Klosterbier,6.2,50,0
200411,11/11/2004,10327,FOLKO,Escargots de Bourgogne,10.6,30,0.2
200411,11/11/2004,10327,FOLKO,Chang,15.2,25,0.2
200411,11/11/2004,10327,FOLKO,Queso Cabrales,16.8,50,0.2
200411,11/11/2004,10327,FOLKO,Nord-Ost Matjeshering,20.7,35,0.2
200411,11/14/2004,10328,FURIB,Scottish Longbreads,10,10,0
200411,11/14/2004,10328,FURIB,Raclette Courdavault,44,9,0
200411,11/14/2004,10328,FURIB,Louisiana Fiery Hot Pepper Sauce,16.8,40,0
200411,11/15/2004,10329,SPLIR,Teatime Chocolate Biscuits,7.3,10,0.05
200411,11/15/2004,10329,SPLIR,Nord-Ost Matjeshering,20.7,8,0.05
200411,11/15/2004,10329,SPLIR,Côte de Blaye,210.8,20,0.05
200411,11/15/2004,10329,SPLIR,Gnocchi di nonna Alice,30.4,12,0.05
200411,11/16/2004,10330,LILAS,Gumbär Gummibärchen,24.9,50,0.15
200411,11/16/2004,10330,LILAS,Mozzarella di Giovanni,27.8,25,0.15
200411,11/16/2004,10331,BONAP,Tourtière,5.9,15,0
200411,11/17/2004,10332,MEREP,Zaanse koeken,7.6,16,0.2
200411,11/17/2004,10332,MEREP,Singaporean Hokkien Fried Mee,11.2,10,0.2
200411,11/17/2004,10332,MEREP,Carnarvon Tigers,50,40,0.2
200411,11/18/2004,10333,WARTH,Tofu,18.6,10,0
200411,11/18/2004,10333,WARTH,Sir Rodney's Scones,8,10,0.1
200411,11/18/2004,10333,WARTH,Fløtemysost,17.2,40,0.1
200411,11/21/2004,10334,VICTE,Filo Mix,5.6,8,0
200411,11/21/2004,10334,VICTE,Scottish Longbreads,10,10,0
200411,11/22/2004,10335,HUNGO,Mascarpone Fabioli,25.6,6,0.2
200411,11/22/2004,10335,HUNGO,Manjimup Dried Apples,42.4,48,0.2
200411,11/22/2004,10335,HUNGO,Gorgonzola Telino,10,25,0.2
200411,11/22/2004,10335,HUNGO,Chang,15.2,7,0.2
200411,11/23/2004,10336,PRINI,Chef Anton's Cajun Seasoning,17.6,18,0.1
200411,11/24/2004,10337,FRANK,Tunnbröd,7.2,40,0
200411,11/24/2004,10337,FRANK,Gumbär Gummibärchen,24.9,24,0
200411,11/24/2004,10337,FRANK,Inlagd Sill,15.2,20,0
200411,11/24/2004,10337,FRANK,Gravad lax,20.8,28,0
200411,11/24/2004,10337,FRANK,Mozzarella di Giovanni,27.8,25,0
200411,11/25/2004,10338,OLDWO,Nord-Ost Matjeshering,20.7,15,0
200411,11/25/2004,10338,OLDWO,Alice Mutton,31.2,20,0
200411,11/28/2004,10339,MEREP,Tarte au sucre,39.4,28,0
200411,11/28/2004,10339,MEREP,Alice Mutton,31.2,70,0.05
200411,11/28/2004,10339,MEREP,Chef Anton's Cajun Seasoning,17.6,10,0
200411,11/29/2004,10340,BONAP,Carnarvon Tigers,50,20,0.05
200411,11/29/2004,10340,BONAP,Jack's New England Clam Chowder,7.7,12,0.05
200411,11/29/2004,10340,BONAP,Ipoh Coffee,36.8,40,0.05
200411,11/29/2004,10341,SIMOB,Geitost,2,8,0
200411,11/29/2004,10341,SIMOB,Raclette Courdavault,44,9,0.15
200411,11/30/2004,10342,FRANK,Inlagd Sill,15.2,40,0.2
200411,11/30/2004,10342,FRANK,Pâté chinois,19.2,40,0.2
200411,11/30/2004,10342,FRANK,Gorgonzola Telino,10,56,0.2
200411,11/30/2004,10342,FRANK,Chang,15.2,24,0.2
200412,12/1/2004,10343,LEHMS,Scottish Longbreads,10,4,0.05
200412,12/1/2004,10343,LEHMS,Lakkalikööri,14.4,15,0
200412,12/1/2004,10343,LEHMS,Wimmers gute Semmelknödel,26.6,50,0
200412,12/2/2004,10344,WHITC,Chef Anton's Cajun Seasoning,17.6,35,0
200412,12/2/2004,10344,WHITC,Northwoods Cranberry Sauce,32,70,0.25
200412,12/5/2004,10345,QUICK,Northwoods Cranberry Sauce,32,70,0
200412,12/5/2004,10345,QUICK,Teatime Chocolate Biscuits,7.3,80,0
200412,12/5/2004,10345,QUICK,Singaporean Hokkien Fried Mee,11.2,9,0
200412,12/6/2004,10346,RATTC,Alice Mutton,31.2,36,0.1
200412,12/6/2004,10346,RATTC,Gnocchi di nonna Alice,30.4,20,0
200412,12/7/2004,10347,FAMIA,Chartreuse verte,14.4,50,0.15
200412,12/7/2004,10347,FAMIA,Boston Crab Meat,14.7,4,0
200412,12/7/2004,10347,FAMIA,Rhönbräu Klosterbier,6.2,6,0.15
200412,12/7/2004,10347,FAMIA,NuNuCa Nuß-Nougat-Creme,11.2,10,0
200412,12/8/2004,10348,WANDK,Chai,14.4,15,0.15
200412,12/8/2004,10348,WANDK,Tunnbröd,7.2,25,0
200412,12/9/2004,10349,SPLIR,Tourtière,5.9,24,0
200412,12/12/2004,10350,LAMAI,Gudbrandsdalsost,28.8,18,0.1
200412,12/12/2004,10350,LAMAI,Valkoinen suklaa,13,15,0.1
200412,12/12/2004,10351,ERNSH,Louisiana Fiery Hot Pepper Sauce,16.8,10,0.05
200412,12/12/2004,10351,ERNSH,Gula Malacca,15.5,77,0.05
200412,12/12/2004,10351,ERNSH,Côte de Blaye,210.8,20,0.05
200412,12/12/2004,10351,ERNSH,Jack's New England Clam Chowder,7.7,13,0
200412,12/13/2004,10352,FURIB,Guaraná Fantástica,3.6,10,0
200412,12/13/2004,10352,FURIB,Tourtière,5.9,20,0.15
200412,12/14/2004,10353,PICCO,Queso Cabrales,16.8,12,0.2
200412,12/14/2004,10353,PICCO,Côte de Blaye,210.8,50,0.2
200412,12/15/2004,10354,PERIC,Thüringer Rostbratwurst,99,4,0
200412,12/15/2004,10354,PERIC,Chai,14.4,12,0
200412,12/16/2004,10355,AROUT,Ravioli Angelo,15.6,25,0
200412,12/16/2004,10355,AROUT,Guaraná Fantástica,3.6,25,0
200412,12/19/2004,10356,WANDK,Gorgonzola Telino,10,30,0
200412,12/19/2004,10356,WANDK,Pâté chinois,19.2,12,0
200412,12/19/2004,10356,WANDK,Gudbrandsdalsost,28.8,20,0
200412,12/20/2004,10357,LILAS,Ikura,24.8,30,0.2
200412,12/20/2004,10357,LILAS,Gumbär Gummibärchen,24.9,16,0
200412,12/20/2004,10357,LILAS,Camembert Pierrot,27.2,8,0.2
200412,12/21/2004,10358,LAMAI,Sasquatch Ale,11.2,10,0.05
200412,12/21/2004,10358,LAMAI,Inlagd Sill,15.2,20,0.05
200412,12/21/2004,10358,LAMAI,Guaraná Fantástica,3.6,10,0.05
200412,12/22/2004,10359,SEVES,Gorgonzola Telino,10,70,0.05
200412,12/22/2004,10359,SEVES,Camembert Pierrot,27.2,80,0.05
200412,12/22/2004,10359,SEVES,Pavlova,13.9,56,0.05
200412,12/23/2004,10360,BLONP,Rössle Sauerkraut,36.4,30,0
200412,12/23/2004,10360,BLONP,Thüringer Rostbratwurst,99,35,0
200412,12/23/2004,10360,BLONP,Côte de Blaye,210.8,10,0
200412,12/23/2004,10360,BLONP,Maxilaku,16,35,0
200412,12/23/2004,10360,BLONP,Tourtière,5.9,28,0
200412,12/23/2004,10361,QUICK,Chartreuse verte,14.4,54,0.1
200412,12/23/2004,10361,QUICK,Camembert Pierrot,27.2,55,0.1
200412,12/26/2004,10362,BONAP,NuNuCa Nuß-Nougat-Creme,11.2,50,0
200412,12/26/2004,10362,BONAP,Manjimup Dried Apples,42.4,20,0
200412,12/26/2004,10362,BONAP,Tourtière,5.9,24,0
200412,12/27/2004,10363,DRACD,Gorgonzola Telino,10,20,0
200412,12/27/2004,10363,DRACD,Rhönbräu Klosterbier,6.2,12,0
200412,12/27/2004,10363,DRACD,Lakkalikööri,14.4,12,0
200412,12/27/2004,10364,EASTC,Gudbrandsdalsost,28.8,30,0
200412,12/27/2004,10364,EASTC,Fløtemysost,17.2,5,0
200412,12/28/2004,10365,ANTON,Queso Cabrales,16.8,24,0
200412,12/29/2004,10366,GALED,Louisiana Fiery Hot Pepper Sauce,16.8,5,0
200412,12/29/2004,10366,GALED,Original Frankfurter grüne Soße,10.4,5,0
200412,12/29/2004,10367,VAFFE,Sasquatch Ale,11.2,36,0
200412,12/29/2004,10367,VAFFE,Tourtière,5.9,18,0
200412,12/29/2004,10367,VAFFE,Louisiana Fiery Hot Pepper Sauce,16.8,15,0
200412,12/29/2004,10367,VAFFE,Original Frankfurter grüne Soße,10.4,7,0
200412,12/30/2004,10368,ERNSH,Wimmers gute Semmelknödel,26.6,35,0.1
200412,12/30/2004,10368,ERNSH,Sir Rodney's Scones,8,5,0.1
200412,12/30/2004,10368,ERNSH,Rössle Sauerkraut,36.4,13,0.1
200412,12/30/2004,10368,ERNSH,Ravioli Angelo,15.6,25,0
Upon examination of the file, you can see that there is three months worth of data (several records for October, November, and December 2004, indicated by the first field values 200410, 200411, and 200412, respectively). We could say that the major control field is the Year-Month, forming control groups for the sets of records for each month (all the records with the value of 200410 in the first field is one control group, as is all the records with the value of 200411 in the first field, as is all the records with the value of 200412 in the first field). In a printed report, management would want to see subtotals for each month.
Within each month, we see that there are (or can be) several records for each date (for example, only one record for 10/3/2004, but four records for 10/10/2004, and six records for 12/12/2004). It would follow that management would want to see subtotals for each date in addition to the monthly totals.
A final breakdown would be by order number. An order can consist of more than one product; an examination of the data bears this out. For example, order number 10296 on 10/4/2004 consisted of three products; order number 10309 on 10/20/2004 consisted of five products. So we can see that subtotals for each order would also be required on the report.
The last sample program in this article will show you how to create a report with these three levels of control break subtotals (year-month, date, and order number) in addition to grand totals. A note on terminology: when you have two or more control breaks, the highest level break (in this case, year-month) is considered the major control break; the lowest level break (in this case, order number) is considered the minor control break; and any breaks in between (in this case, the date) are considered intermediate control breaks.
Four sample programs will be presented in this article, each one a variation on the other. The four sample programs will respectively produce:
· Sales report with no control breaks (grand totals only)
· Sales report with control break totals for year-month (as well as the grand totals)
· Sales report with control break totals for both year-month and date (as well as the grand totals)
· Sales report with control break totals for year-month, date, and order number (as well as the grand totals)
Sample Program 1
Sample Program 1 processes the Northwind input file described above and produces a sales report with grand totals only (no control breaks or subtotals). Excerpts of the output are shown below. Report headings, including the date, time, page number, and column headings are printed at the top of each page. A detail line is printed for each input record, consisting of the year-month, order date, order number, customer ID, product description, unit price, and quantity fields from the input record, along with the calculated fields extended price, discount amount, and sale total (the discount percentage from the input record is used for the calculations but is not itself printed on the report). Grand totals for the three calcluated fields are printed in boldface type at the end of the report.
Print Date: 04/25/05 THEVBPROGRAMMER.COM Page: 1
Print Time: 21:36:35 NORTHWIND TRADERS SALES REPORT
WITH NO CONTROL BREAKS (GRAND TOTALS ONLY)
YEAR/ ORDER CUST UNIT EXTENDED DISCOUNT
MONTH ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL
----- ---------- ------ -- ------------------- ----- --- ----- ------ ----------
200410 10/3/2004 10295 VINET Gnocchi di nonna Alice 30.40 4 $121.60 $0.00 $121.60
200410 10/4/2004 10296 LILAS Pavlova 13.90 30 $417.00 $0.00 $417.00
200410 10/4/2004 10296 LILAS Gudbrandsdalsost 28.80 15 $432.00 $0.00 $432.00
200410 10/4/2004 10296 LILAS Queso Cabrales 16.80 12 $201.60 $0.00 $201.60
200410 10/5/2004 10297 BLONP Mozzarella di Giovanni 27.80 20 $556.00 $0.00 $556.00
200410 10/5/2004 10297 BLONP Chartreuse verte 14.40 60 $864.00 $0.00 $864.00
200410 10/6/2004 10298 HUNGO Chang 15.20 40 $608.00 $0.00 $608.00
200410 10/6/2004 10298 HUNGO Inlagd Sill 15.20 40 $608.00 $152.00 $456.00
200410 10/6/2004 10298 HUNGO Raclette Courdavault 44.00 30 $1,320.00 $330.00 $990.00
200410 10/6/2004 10298 HUNGO Tarte au sucre 39.40 15 $591.00 $0.00 $591.00
. . .
200412 12/29/2004 10366 GALED Louisiana Fiery Hot Pepper Sauce 16.80 5 $84.00 $0.00 $84.00
200412 12/29/2004 10366 GALED Original Frankfurter grüne Soße 10.40 5 $52.00 $0.00 $52.00
200412 12/29/2004 10367 VAFFE Sasquatch Ale 11.20 36 $403.20 $0.00 $403.20
200412 12/29/2004 10367 VAFFE Tourtière 5.90 18 $106.20 $0.00 $106.20
200412 12/29/2004 10367 VAFFE Louisiana Fiery Hot Pepper Sauce 16.80 15 $252.00 $0.00 $252.00
200412 12/29/2004 10367 VAFFE Original Frankfurter grüne Soße 10.40 7 $72.80 $0.00 $72.80
200412 12/30/2004 10368 ERNSH Wimmers gute Semmelknödel 26.60 35 $931.00 $93.10 $837.90
200412 12/30/2004 10368 ERNSH Sir Rodney's Scones 8.00 5 $40.00 $4.00 $36.00
200412 12/30/2004 10368 ERNSH Rössle Sauerkraut 36.40 13 $473.20 $47.32 $425.88
200412 12/30/2004 10368 ERNSH Ravioli Angelo 15.60 25 $390.00 $0.00 $390.00
GRAND TOTALS: $118,543.60 $9,046.43 $109,497.17
This sample program introduces report logic techniques that are used in all of the subsequent examples for this article. The native VB Printer object will be used to generate the report output. Although the VB Printer object can be used to generate "fancy" printouts that include graphics, lines, boxes, a mixture of fonts, etc., we are going to limit the printing to plain-text only, and use only one font in one size, Courier New 10 point. As a "special treat", we will make the total line bold.
By using a monospaced font like Courier New, we can determine the number of characters that will fit on one line and the number of lines that will fit on one page. Note: A monospaced font is one in which each character takes up the same amount of space, like a typewriter. Examples of monospaced fonts are Courier and Lucida Console. This is contrasted with proportional fonts, where the width of each letter varies (for example, a "W" is much wider than an "i"). Examples of proportional fonts are Times New Roman and Arial (this font).
By knowing the number of characters that can fit on one line, we can plan the format of the data to be presented. By knowing the number of lines that will fit on one page, we can use logic to perform page breaks and print headings when a page fills up with data.
Assuming a standard page size of 8 ½" by 11" and a font of Courier New 10 point (which gives you 12 characters per inch), you can plan your report layouts and logic as follows:
Portrait Mode: 80 characters per line, 60 lines per page
Landscape Mode: 118 characters per line, 47 lines per page
The above figures are subjective to a degree, as they take into account room for top, bottom, left and right margins. If you want bigger or smaller margins, or if you want a different font style and/or font size, the number of characters per line and the number of lines per page will vary accordingly. (Keep in mind however, that designing printouts of this nature would be very difficult if not impossible with a proportional font – that is why you should use a monospaced font.)
The reports produced by the sample programs for this article all print in landscape mode, and 118 characters per line, 47 lines per page are the assumed limits.
Programmers in the days of yore used a printer spacing chart form to design printouts. An actual printer spacing chart form might be hard to come by these days, but any type of form with grid lines like this would get the job done. A freeware Excel template file that replicates the printer spacing chart (as well as other "old-school" forms) is available. To use the Excel template, either print the blank sheet and pencil your design in, or copy the template to a separate work file and enter your design directly in the cells of the Excel template worksheet. The latter method was used to produce the example shown below. The Excel template file, as well as the file containing the sample chart below, can be downloaded here.
The sample programs use the following methods and properties of the Printer object:
|
Method |
Description |
|
|
Generates output as specified by the expression list that follows the keyword Print. As examined in the earlier tutotials on the Print method, the expression list may contain a mix of constants, variables, the Tab() and Spc() functions, and can use the comma ("print zone") and/or semicolon separators. |
|
NewPage |
Advances to the next printer page and resets the print position to the upper-left corner of the new page. It is not necessary to use the NewPage method for the first page to be printed (this would cause an extra blank page to be generated). |
|
EndDoc |
This is a very important method (without it, you may wonder where your output is). The EndDoc method terminates a print operation sent to the Printer object, releasing the document to the print device or spooler. |
|
Property |
Description |
|
Fonts |
Property array containing the names of the fonts available on the current default printer. Should be referenced with an index (i.e. Printer.Fonts(X), where X is a number between 0 and Printer.FontCount – 1). |
|
FontCount |
The number of fonts available on the current default printer. |
|
FontName |
The name of the current font being used on the current default printer. |
|
FontSize |
The size of the current font being used on the current default printer. |
|
FontBold |
Boolean indicating whether or not the bold style is applied to the current font being used on the current default printer. |
|
Orientation |
Indicates whether the current default printer is printing in portrait or landscape mode. A value of 1 (or the built-in constant vbPRORPortrait) indicates portrait mode; a value of 2 (or the built-in constant vbPRORLandscape) indicates landscape mode. One thing to keep in mind is pointed out by the MSDN help: The effect of the properties of the Printer object depends on the driver supplied by the printer manufacturer. Some property settings may have no effect, or several different property settings may all have the same effect. Settings outside the accepted range may or may not produce an error. For more information, see the manufacturer's documentation for the specific driver. |
The interface for the program is quite simple: two command buttons named cmdPrint and cmdExit. All of the code for this program resides behind the default form (Form1) with the majority of the code in the Click event of the cmdPrint button.
The code for the General Declarations section, where the form-level variables are declared, is shown below.
The first set of variables (mstrYearMonth through msngDiscountPct) are used to hold the fields of the current input record.
The variable mintLineCtr is used to keep track of how many lines have been printed on the page so that we know when the lines per page limit has been reached. The variable mintPageCtr is used to keep track of the page number so that it can be printed on each page heading.
The variable mintSalesFileNbr is the file number used to refer to the input file.
The variable mblnEOF is used to test whether or not we have reached the end-of-file condition. The reason that this variable is used instead of just using the EOF function will be explained a little further below.
The constant mintLINE_START_POS is used to control the left margin of the page. By setting this to 6, we are saying we want a margin of 5 characters. The Tab functions used in the Print statements use this constant to determine the actual starting position of the item to be printed. For example, the statement Printer.Print Tab(mintLINE_START_POS + 20); "HELLO" would cause "HELLO" to print in position 26 of the current line. However, if later on, I decided I wanted a wider margin, I would only have to change the value of the constant mintLINE_START_POS (to, say a value of 8) rather than change every single Printer.Print statement.
Option Explicit
Private mstrYearMonth As String
Private mstrOrderNbr As String
Private mstrOrderDate As String
Private mstrCustID As String
Private mstrProdDesc As String
Private msngUnitPrice As Single
Private mintQty As Integer
Private msngDiscountPct As Single
Private mintLineCtr As Integer
Private mintPageCtr As Integer
Private mintSalesFileNbr As Integer
Private mblnEOF As Boolean
Private Const mintLINE_START_POS As Integer = 6
Before we look at the code behind the cmdPrint button, let us examine the two programmer-defined Subs used in the program. The Sub ReadSalesRecord implements a way to delay the testing for the end of file condition until after the last record has been read. As you know, the Input #n statement detects EOF at the same time the last record in the file is read. That is why a normal file processing loop looks like this:
Do Until EOF(FileNumber)
Input #FileNumber, variable-list
Process current record
Loop
However, for many algorithms, it is more convenient have the end of file condition triggered on a separate iteration of the processing loop. This is the type of "input" or "read" loop that uses a priming read outside the loop; all subsequent reads occur at the bottom of the loop. The pseudocode might be written as follows:
READ A RECORD ' priming read
DO UNTIL EOF
PROCESS THE RECORD
READ A RECORD ' second and subsequent reads
LOOP
To simulate this behavior, the ReadSalesRecord Sub is called when we want to read a record. With this coding, when the last record is read, the system "knows" that EOF has occurred, but your processing loop won't "know" it until it calls the Sub one last time, at which point the condition EOF(mintSalesFileNbr) will be True, which will cause the form-level Boolean variable mblnEOF to be set to True.
'-----------------------------------------------------------------------------
Private Sub ReadSalesRecord()
'-----------------------------------------------------------------------------
If EOF(mintSalesFileNbr) Then
mblnEOF = True
Else
Input #mintSalesFileNbr, mstrYearMonth, _
mstrOrderDate, _
mstrOrderNbr, _
mstrCustID, _
mstrProdDesc, _
msngUnitPrice, _
mintQty, _
msngDiscountPct
End If
End Sub
Thus, the ReadSalesRecord Sub can be used as follows:
Call ReadSalesRecord ' priming read
Do Until mblnEOF
' PROCESS THE RECORD
Call ReadSalesRecord ' second and subsequent reads
Loop
Note: Recall that the keyword Call is optional when calling a Sub, so if desired, the keyword Call could be omitted from the above code.
The Sub PrintHeadings is called when a new page must be printed (when printing the first page, or when the line limit of the current page has been exceeded). Note that it is in this Sub that the page counter variable (mintPageCtr) is incremented so that that the page number will appear in the first heading line, and that at the bottom of the Sub, the line counter variable (mintLineCtr) is reset.
'-----------------------------------------------------------------------------
Private Sub PrintHeadings()
'-----------------------------------------------------------------------------
' If we are about to print any page other than the first, invoke the NewPage
' method to perform a page break. The NewPage method advances to the next
' printer page and resets the print position to the upper-left corner of the
' new page.
If mintPageCtr > 0 Then
Printer.NewPage
End If
' increment the page counter
mintPageCtr = mintPageCtr + 1
' Print 4 blank lines, which provides a for top margin. These four lines do NOT
' count toward the limit of 47 lines.
Printer.Print
Printer.Print
Printer.Print
Printer.Print
' Print the main headings
Printer.Print Tab(mintLINE_START_POS); _
"Print Date: "; _
Format$(Date, "mm/dd/yy"); _
Tab(mintLINE_START_POS + 50); _
"THEVBPROGRAMMER.COM"; _
Tab(mintLINE_START_POS + 110); _
"Page:"; _
Format$(mintPageCtr, "@@@")
Printer.Print Tab(mintLINE_START_POS); _
"Print Time: "; _
Format$(Time, "hh:nn:ss"); _
Tab(mintLINE_START_POS + 45); _
"NORTHWIND TRADERS SALES REPORT"
Printer.Print Tab(mintLINE_START_POS + 39); _
"WITH NO CONTROL BREAKS (GRAND TOTALS ONLY)"
Printer.Print
' Print the column headings
Printer.Print Tab(mintLINE_START_POS); _
"YEAR/"; _
Tab(23 + mintLINE_START_POS); _
"ORDER CUST"; _
Tab(72 + mintLINE_START_POS); _
"UNIT"; _
Tab(87 + mintLINE_START_POS); _
"EXTENDED"; _
Tab(99 + mintLINE_START_POS); _
"DISCOUNT"
Printer.Print Tab(mintLINE_START_POS); _
"MONTH"; _
Tab(11 + mintLINE_START_POS); _
"ORDER DATE NUMBER ID"; _
Tab(37 + mintLINE_START_POS); _
"PRODUCT DESCRIPTION"; _
Tab(71 + mintLINE_START_POS); _
"PRICE QTY"; _
Tab(88 + mintLINE_START_POS); _
"PRICE"; _
Tab(100 + mintLINE_START_POS); _
"AMOUNT SALE TOTAL"
Printer.Print Tab(mintLINE_START_POS); _
"-----"; _
Tab(11 + mintLINE_START_POS); _
"---------- ------ --"; _
Tab(37 + mintLINE_START_POS); _
"-------------------"; _
Tab(71 + mintLINE_START_POS); _
"----- ---"; _
Tab(88 + mintLINE_START_POS); _
"-----"; _
Tab(100 + mintLINE_START_POS); _
"------ ----------"
Printer.Print
' reset the line counter to reflect the number of lines that have now
' been printed on the new page.
mintLineCtr = 8
End Sub
The code for the cmdPrint_Click event is shown below. This is where most of the work occurs in the program. The code is shown in its entirety a little further below; but right here we will analyze it piece by piece.
First, the local variables (and constant) are declared as follows:
Dim intX As Integer
Dim strCustFileName As String
Dim strBackSlash As String
Dim dblExtPriceDtl As Double
Dim dblDiscAmtDtl As Double
Dim dblSaleTotDtl As Double
Dim dblExtPriceGrand As Double
Dim dblDiscAmtGrand As Double
Dim dblSaleTotGrand As Double
Const intLINES_PER_PAGE As Integer = 47
The variable intX is an integer that will be used for looping through the Fonts property array. The variables dblExtPriceDtl, dblDiscAmtDtl, and dblSaleTotDtl are the variables used for the calculated fields (extended price, discount amount, and sale total). The variables dblExtPriceGrand, dblDiscAmtGrand, and dblSaleTotGrand are the variables for the corresponding grand totals; as each input record is read and the calculated field variables are computed, the calculated values are added to their corresponding grand total variables. The constant intLINES_PER_PAGE is used to compare against the line counter variable (mintLineCtr, declared in the General Declarations section, updated every time a line is printed) to detect when we've filled up a page.
Following the variables and constant declaration, an error handler is set up (so that if there is a problem with the printer or with the input file, we can handle the error ourselves rather than let VB simply crash):
On Error GoTo cmdPrint_Click_Error
With the following code, we give the user an opportunity to make sure his or her printer is ready (or to back out of printing if so desired):
If MsgBox("Make sure your printer is on-line and " _
& "loaded with paper.", vbOKCancel, "Check Printer") = vbCancel _
Then
Exit Sub
End If
The following code segment is used to set the printer's font to Courier. It loops through the Printer object's Fonts property array, looking for the first font name that starts with "Courier". Once such a font name is found, that font is assigned to the FontName property, thus making Courier (or "Courier New" or "Courier PS") the current font for the printer, and the loop then exits. If, for whatever reason, no font name starting with "Courier" is found, the FontName property will not be explicitly set, so the Printer object will just use the default font for the printer.
For intX = 0 To Printer.FontCount - 1
If Printer.Fonts(intX) Like "Courier*" Then
Printer.FontName = Printer.Fonts(intX)
Exit For
End If
Next
After the font name is set, the font size is set to 10 and the printer orientation is set to landscape with the following two lines:
Printer.FontSize = 10
Printer.Orientation = vbPRORLandscape
The page counter variable is then initialized to 0 (by default this variable would already be initialized to 0; it is done here explicitly for improved readability of the code) :
mintPageCtr = 0
The file name and file number are then set up, and the Open statement is issued to start the processing (this code should be familiar from the previous articles on sequential file processing) :
' prepare file name & number
strBackSlash = IIf(Right$(App.Path, 1) = "\", "", "\")
strCustFileName = App.Path & strBackSlash & "NWSALES.txt"
mintSalesFileNbr = FreeFile
' open the input file
Open strCustFileName For Input As #mintSalesFileNbr
A call to the PrintHeadings Sub is made to print the headings for the first page of the report:
Call PrintHeadings
The grand total variables are then initialized to 0 (by default these variables would already be initialized to 0; it is done explicitly here for improved readability of the code – showing that these variables are cleared at the beginning of the processing and will be accumulated into as each record is processed) :
dblExtPriceGrand = 0
dblDiscAmtGrand = 0
dblSaleTotGrand = 0
The priming read to get the first input record is then made:
Call ReadSalesRecord
The main processing loop then begins:
Do Until mblnEOF
A check is made to see if the number of lines printed so far exceeds the maximum number of lines allowed on a page, and if so, the PrintHeadings Sub is called to start a new page:
If mintLineCtr > intLINES_PER_PAGE Then
Call PrintHeadings
End If
Computations for the calculated fields (extended price, discount amount, and sale total) are then performed:
dblExtPriceDtl = msngUnitPrice * mintQty
dblDiscAmtDtl = dblExtPriceDtl * msngDiscountPct
dblSaleTotDtl = dblExtPriceDtl - dblDiscAmtDtl
A detail line, consisting of fields from the input record, along with the calculated fields, is then printed. The use of the Tab function and the various Format$ options has been examined in previous articles. Note how nested Format$ functions are used to right-justify the numeric values.
Printer.Print Tab(mintLINE_START_POS); _
mstrYearMonth; _
Tab(11 + mintLINE_START_POS); _
mstrOrderDate; _
Tab(23 + mintLINE_START_POS); _
mstrOrderNbr; _
Tab(30 + mintLINE_START_POS); _
mstrCustID; _
Tab(37 + mintLINE_START_POS); _
mstrProdDesc; _
Tab(70 + mintLINE_START_POS); _
Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _
Tab(79 + mintLINE_START_POS); _
Format$(mintQty, "@@@"); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))
The line counter variable is then incremented to account for the detail line we just printed:
mintLineCtr = mintLineCtr + 1
The calculated field variables are then added to their corresponding grand total variables. This is sometimes called "rolling" the values to the totals.
dblExtPriceGrand = dblExtPriceGrand + dblExtPriceDtl
dblDiscAmtGrand = dblDiscAmtGrand + dblDiscAmtDtl
dblSaleTotGrand = dblSaleTotGrand + dblSaleTotDtl
Having completed the work required for the current input record, the next record is then read via a call to the ReadSalesRecord Sub at the bottom of the loop, and the Loop statement then returns us to the top of the Do loop for another processing iteration:
Call ReadSalesRecord
Loop
Once we have processed all of the input records and the end of file condition has been met, we continue with the task of printing the grand totals. Before we do so, we check first to see if there is enough room on the current page to print them. A blank line will precede the total line, so we check to make sure there is enough room to print two lines as follows:
If mintLineCtr > (intLINES_PER_PAGE - 2) Then
Call PrintHeadings
End If
We then print the grand totals. A blank line is printed first (with the Printer.Print statement). We then turn bold styling on by setting the FontBold property to True, print the actual total line, and then turn bold styling off by setting the FontBold property to False:
Printer.Print
Printer.FontBold = True
Printer.Print Tab(70 + mintLINE_START_POS); _
"GRAND TOTALS:"; _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceGrand, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtGrand, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotGrand, "Currency"), String$(11, "@"))
Printer.FontBold = False
We then wrap things up by closing the input file, executing the EndDoc method on the Printer object (remember, this is very important, as the EndDoc method actually releases the output to the printer), setting focus to the Exit button as a visual indication that the print processing is complete, and finally exiting the sub.
Close #mintSalesFileNbr
Printer.EndDoc
cmdExit.SetFocus
Exit Sub
If an error occurs in the processing, the error handling code below will be executed. It shows the user what the error is and asks whether or not they want to continue. If yes, the program will try to continue with the statement past the one that caused the error; if no, the program will end.
cmdPrint_Click_Error:
If MsgBox("Error # " & Err.Number & " - " & Err.Description & vbNewLine & _
"Do you want to continue?", _
vbYesNo + vbQuestion, _
"Error") = vbYes Then
Resume Next
Else
End
End If
Here is the code for the entire cmdPrint_Click event:
'-----------------------------------------------------------------------------
Private Sub cmdPrint_Click()
'-----------------------------------------------------------------------------
Dim intX As Integer
Dim strCustFileName As String
Dim strBackSlash As String
Dim dblExtPriceDtl As Double
Dim dblDiscAmtDtl As Double
Dim dblSaleTotDtl As Double
Dim dblExtPriceGrand As Double
Dim dblDiscAmtGrand As Double
Dim dblSaleTotGrand As Double
Const intLINES_PER_PAGE As Integer = 47
On Error GoTo cmdPrint_Click_Error
' Have the user make sure his/her printer is ready ...
If MsgBox("Make sure your printer is on-line and " _
& "loaded with paper.", vbOKCancel, "Check Printer") = vbCancel _
Then
Exit Sub
End If
' Set the printer font to Courier, if available (otherwise, we would be
' relying on the default font for the Windows printer, which may or
' may not be set to an appropriate font) ...
For intX = 0 To Printer.FontCount - 1
If Printer.Fonts(intX) Like "Courier*" Then
Printer.FontName = Printer.Fonts(intX)
Exit For
End If
Next
Printer.FontSize = 10
Printer.Orientation = vbPRORLandscape
' initialize page counter ...
mintPageCtr = 0
' prepare file name & number
strBackSlash = IIf(Right$(App.Path, 1) = "\", "", "\")
strCustFileName = App.Path & strBackSlash & "NWSALES.txt"
mintSalesFileNbr = FreeFile
' open the input file
Open strCustFileName For Input As #mintSalesFileNbr
Call PrintHeadings
dblExtPriceGrand = 0
dblDiscAmtGrand = 0
dblSaleTotGrand = 0
' priming read
Call ReadSalesRecord
' main processing loop
Do Until mblnEOF
' if the number of lines printed so far exceeds the
' maximum number of lines allowed on a page, invoke
' the PrintHeadings sub to do a page break
If mintLineCtr > intLINES_PER_PAGE Then
Call PrintHeadings
End If
dblExtPriceDtl = msngUnitPrice * mintQty
dblDiscAmtDtl = dblExtPriceDtl * msngDiscountPct
dblSaleTotDtl = dblExtPriceDtl - dblDiscAmtDtl
' print a line of data
Printer.Print Tab(mintLINE_START_POS); _
mstrYearMonth; _
Tab(11 + mintLINE_START_POS); _
mstrOrderDate; _
Tab(23 + mintLINE_START_POS); _
mstrOrderNbr; _
Tab(30 + mintLINE_START_POS); _
mstrCustID; _
Tab(37 + mintLINE_START_POS); _
mstrProdDesc; _
Tab(70 + mintLINE_START_POS); _
Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _
Tab(79 + mintLINE_START_POS); _
Format$(mintQty, "@@@"); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))
' increment the line count
mintLineCtr = mintLineCtr + 1
' "roll" the detail amounts to the grand totals
dblExtPriceGrand = dblExtPriceGrand + dblExtPriceDtl
dblDiscAmtGrand = dblDiscAmtGrand + dblDiscAmtDtl
dblSaleTotGrand = dblSaleTotGrand + dblSaleTotDtl
' read the next record
Call ReadSalesRecord
Loop
' end of main processing loop - time to print the grand totals
' make sure we have enough room to print the lines for the grand total
If mintLineCtr > (intLINES_PER_PAGE - 2) Then
Call PrintHeadings
End If
Printer.Print
Printer.FontBold = True
Printer.Print Tab(70 + mintLINE_START_POS); _
"GRAND TOTALS:"; _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceGrand, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtGrand, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotGrand, "Currency"), String$(11, "@"))
Printer.FontBold = False
' close the input file
Close #mintSalesFileNbr
' Important! When done, the EndDoc method of the Printer object must be invoked.
' The EndDoc method terminates a print operation sent to the Printer object,
' releasing the document to the print device or spooler.
Printer.EndDoc
cmdExit.SetFocus
Exit Sub
cmdPrint_Click_Error:
If MsgBox("Error # " & Err.Number & " - " & Err.Description & vbNewLine & _
"Do you want to continue?", _
vbYesNo + vbQuestion, _
"Error") = vbYes Then
Resume Next
Else
End
End If
End Sub
The code for the cmdExit_Click event simply contains the End statement.
Download the project files for Sample Program 1 here.
Sample Program 2
Sample Program 2 processes the same input file as Sample Program 1. The difference in the output is that Sample Program 2 produces subtotals based on the year-month field in addition to the grand totals. And instead of showing the year-month on each detail line, it is "factored out" and printed on a separate "sub-heading" line preceding the detail lines for that year-month. Furthermore, the year-month is formatted with the month name and year (i.e., OCTOBER, 2004 as opposed to 200410).
Excerpts of the output report produced by Sample Program 2 are shown below. Note how the month/year is printed on a separate line in bold at the start of each month/year group. Note also that subtotals for the month/year are printed in bold whenever there is a change (or break) in the month/year groups. As in Sample Program 1, grand totals are printed in bold at the end of the report.
Print Date: 04/25/05 THEVBPROGRAMMER.COM Page: 1
Print Time: 21:51:39 NORTHWIND TRADERS SALES REPORT
WITH CONTROL BREAK ON MONTH
ORDER CUST UNIT EXTENDED DISCOUNT
ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL
---------- ------ -- ------------------- ----- --- ----- ------ ----------
OCTOBER, 2004
10/3/2004 10295 VINET Gnocchi di nonna Alice 30.40 4 $121.60 $0.00 $121.60
10/4/2004 10296 LILAS Pavlova 13.90 30 $417.00 $0.00 $417.00
10/4/2004 10296 LILAS Gudbrandsdalsost 28.80 15 $432.00 $0.00 $432.00
10/4/2004 10296 LILAS Queso Cabrales 16.80 12 $201.60 $0.00 $201.60
10/5/2004 10297 BLONP Mozzarella di Giovanni 27.80 20 $556.00 $0.00 $556.00
10/5/2004 10297 BLONP Chartreuse verte 14.40 60 $864.00 $0.00 $864.00
10/6/2004 10298 HUNGO Chang 15.20 40 $608.00 $0.00 $608.00
10/6/2004 10298 HUNGO Inlagd Sill 15.20 40 $608.00 $152.00 $456.00
10/6/2004 10298 HUNGO Raclette Courdavault 44.00 30 $1,320.00 $330.00 $990.00
. . .
10/28/2004 10316 RATTC Tarte au sucre 39.40 70 $2,758.00 $0.00 $2,758.00
10/28/2004 10316 RATTC Jack's New England Clam Chowder 7.70 10 $77.00 $0.00 $77.00
10/31/2004 10317 LONEP Chai 14.40 20 $288.00 $0.00 $288.00
----------- ----------- -----------
TOTALS FOR OCTOBER, 2004: $27,636.00 $1,254.60 $26,381.40
NOVEMBER, 2004
11/1/2004 10318 ISLAT Jack's New England Clam Chowder 7.70 20 $154.00 $0.00 $154.00
11/1/2004 10318 ISLAT Lakkalikööri 14.40 6 $86.40 $0.00 $86.40
11/2/2004 10319 TORTU Alice Mutton 31.20 8 $249.60 $0.00 $249.60
11/2/2004 10319 TORTU Rössle Sauerkraut 36.40 14 $509.60 $0.00 $509.60
11/2/2004 10319 TORTU Lakkalikööri 14.40 30 $432.00 $0.00 $432.00
. . .
11/30/2004 10342 FRANK Inlagd Sill 15.20 40 $608.00 $121.60 $486.40
11/30/2004 10342 FRANK Pâté chinois 19.20 40 $768.00 $153.60 $614.40
11/30/2004 10342 FRANK Gorgonzola Telino 10.00 56 $560.00 $112.00 $448.00
11/30/2004 10342 FRANK Chang 15.20 24 $364.80 $72.96 $291.84
----------- ----------- -----------
TOTALS FOR NOVEMBER, 2004: $39,617.60 $3,685.88 $35,931.72
DECEMBER, 2004
12/1/2004 10343 LEHMS Scottish Longbreads 10.00 4 $40.00 $2.00 $38.00
12/1/2004 10343 LEHMS Lakkalikööri 14.40 15 $216.00 $0.00 $216.00
12/1/2004 10343 LEHMS Wimmers gute Semmelknödel 26.60 50 $1,330.00 $0.00 $1,330.00
. . .
12/30/2004 10368 ERNSH Wimmers gute Semmelknödel 26.60 35 $931.00 $93.10 $837.90
12/30/2004 10368 ERNSH Sir Rodney's Scones 8.00 5 $40.00 $4.00 $36.00
12/30/2004 10368 ERNSH Rössle Sauerkraut 36.40 13 $473.20 $47.32 $425.88
12/30/2004 10368 ERNSH Ravioli Angelo 15.60 25 $390.00 $0.00 $390.00
----------- ----------- -----------
TOTALS FOR DECEMBER, 2004: $51,290.00 $4,105.96 $47,184.05
GRAND TOTALS: $118,543.60 $9,046.43 $109,497.17
The simple interface for the program remains the same, except for the verbiage in form's Caption to indicate what we are doing:
The difference in the coding between Sample Program 1 and Sample Program 2 all takes place in the cmdPrint_Click event. The code for the event is shown below, with the added code shown in bold.
In examining the code, let's start with the newly added variables. The variable strPrevMonth will be needed to compare the current contents of the year-month field with its previous contents to detect when a change in that field occurs. Also, because we will now have an additional set of totals at the year-month level for the calculated fields extended price, discount amount, and sale total, we declare the variables dblExtPriceMonth, dblDiscAmtMonth, and dblSaleTotMonth for that purpose.
The next new area of code occurs after the start of the main processing loop (after the line Do Until mblnEOF). It is here that several "setup" steps are coded to provide for the year/month control break. The statement
strPrevMonth = mstrYearMonth
is critical because saves the value of the current year-month field (mstrYearMonth) to the "comparison" variable strPrevMonth.
The next group of statements deal with printing the "month, year" sub-heading.
The statement
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
checks to see if there is enough room on the current page to print that sub-heading, followed by a blank line, followed by at least one detail line (in other words, checks to see if there enough room to print 3 more lines on the page). If not, the PrintHeadings Sub is called.
The statements
Printer.FontBold = True
Printer.Print Tab(mintLINE_START_POS); _
UCase$(MonthName(CLng(Right$(mstrYearMonth, 2)))) _
& ", " _
& Left$(mstrYearMonth, 4)
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 2
turn bold styling on by setting the FontBold property to True, then some nested functions are used to convert what might be "200410" to "OCTOBER, 2004". If we break down UCase$(MonthName(CLng(Right$(mstrYearMonth, 2)))), the segment Right$(mstrYearMonth, 2) would give you the string "10", the CLng function then converts that to a long integer data type, the MonthName function would then return "October", then the UCase$ function would convert that to "OCTOBER". All that is concatenated with a comma followed by Left$(mstrYearMonth, 4) which would give you "2004". After that month/year string is printed, bold styling is turned off by setting the FontBold property to False, a blank line is printed, and the line counter variable is incremented by 2 to account for the two lines we just printed.
The statements
dblExtPriceMonth = 0
dblDiscAmtMonth = 0
dblSaleTotMonth = 0
are critical to producing accurate subtotals at the year-month level. These variables must be cleared at the start of every year-month group.
The statement
Do Until (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
sets up an inner, or nested loop; it is the body of this inner loop which will process the detail records. The loop condition "Until (strPrevMonth <> mstrYearMonth)" means that the loop will keep executing until there is a change (or break) in the mstrYearMonth field – the condition checks the value of strPrevMonth (which we have set in the setup steps above) against the value of mstrYearMonth, which is refreshed every time an input record is read. This loop must also check for the end-of-file condition (hence the piece Or (mblnEOF)), and of course must terminate if that condition occurs.
The statement that prints the detail line
Printer.Print Tab(11 + mintLINE_START_POS); _
mstrOrderDate; _
Tab(23 + mintLINE_START_POS); _
mstrOrderNbr; _
Tab(30 + mintLINE_START_POS); _
mstrCustID; _
Tab(37 + mintLINE_START_POS); _
mstrProdDesc; _
Tab(70 + mintLINE_START_POS); _
Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _
Tab(79 + mintLINE_START_POS); _
Format$(mintQty, "@@@"); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))
differs from its predecessor only in that the mstrYearMonth variable is omitted, since we have now factored that out and are printing the month and year as a separate sub-heading.
The statements
dblExtPriceMonth = dblExtPriceMonth + dblExtPriceDtl
dblDiscAmtMonth = dblDiscAmtMonth + dblDiscAmtDtl
dblSaleTotMonth = dblSaleTotMonth + dblSaleTotDtl
add the calculated fields at the detail level to their corresponding year-month totals. In Sample Program 1, the calculated fields at the detail level were added directly to the corresponding grand totals – but in Sample Program 2, there is the additional year-month subtotal level, and it is these year-month subtotal accumulators to which the detail variables must be added. The rule in a control-break subtotal program is that detail-level values or subtotal values are added (or "rolled") to the next highest level of totals.
The next set of new statements handle the control break condition – i.e., the code does what needs to be done when a change in the year-month is detected (which is basically to print the year-month subtotals and roll the year-month subtotals to the grand totals):
Once we move past the
Loop
statement, this means a change has occurred in the value of the year-month field. We have printed all the detail records for one month, and we have the first record of the next month in memory waiting to be processed. However, before we start processing the new month, we have some unfinished business to take care of with the previous month. First, we must print the subtotals for the previous month. We are going to use three lines to do this: a "dash line", the subtotal line itself, and a blank line following. The statement
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
checks to see if there is enough room on the current page to print those three lines. If not, the PrintHeadings Sub is called.
The statement
Printer.Print Tab(84 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(96 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(108 + mintLINE_START_POS); _
String$(11, "-")
prints a string of dashes under each detail field.
The statements
Printer.FontBold = True
Printer.Print Tab(56 + mintLINE_START_POS); _
Format$("TOTALS FOR " & _
UCase$(MonthName(CLng(Right$(strPrevMonth, 2)))) & ", " & _
Left$(strPrevMonth, 4) & ":", String$(27, "@")); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceMonth, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtMonth, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotMonth, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 3
turn bold styling on, print the subtotals, turn bold styling off, print the blank line, and increment the line counter by 3. Note that the part of the Print statement that prints "TOTALS FOR MMMMMMMMM, YYYY:" uses strPrevMonth, NOT mstrYearMonth – this is because mstrYearMonth now reflects the new year/month, whereas strPrevMonth has not yet changed and so still has the value for the previous year/month, which is what we need when printing this line. Note also that the string "TOTALS FOR MMMMMMMMM, YYYY:" is formatted with a string of 27 "@" symbols, causing that string to be right-justified when printed.
The next set of statements add the year/month totals to their corresponding grand totals. (As mentioned earlier, in a control-break subtotal program, subtotal values are added (or "rolled") to the next highest level of totals.)
dblExtPriceGrand = dblExtPriceGrand + dblExtPriceMonth
dblDiscAmtGrand = dblDiscAmtGrand + dblDiscAmtMonth
dblSaleTotGrand = dblSaleTotGrand + dblSaleTotMonth
From that point on, the code for this program is the same as its precedessor. (The Loop statement returns the program to the top of the main outer processing loop, which will do the setup for the next year/month group, process the detail records for that group, generate the group's subtotals, roll those subtotals to the grand totals, and so on for each group.) But when the end of file condition occurs, the grand totals are printed, the wrap-up code executes, and the report is done.
'-----------------------------------------------------------------------------
Private Sub cmdPrint_Click()
'-----------------------------------------------------------------------------
Dim intX As Integer
Dim strCustFileName As String
Dim strBackSlash As String
Dim strPrevMonth As String
Dim dblExtPriceDtl As Double
Dim dblDiscAmtDtl As Double
Dim dblSaleTotDtl As Double
Dim dblExtPriceMonth As Double
Dim dblDiscAmtMonth As Double
Dim dblSaleTotMonth As Double
Dim dblExtPriceGrand As Double
Dim dblDiscAmtGrand As Double
Dim dblSaleTotGrand As Double
Const intLINES_PER_PAGE As Integer = 47
On Error GoTo cmdPrint_Click_Error
' Have the user make sure his/her printer is ready ...
If MsgBox("Make sure your printer is on-line and " _
& "loaded with paper.", vbOKCancel, "Check Printer") = vbCancel _
Then
Exit Sub
End If
' Set the printer font to Courier, if available (otherwise, we would be
' relying on the default font for the Windows printer, which may or
' may not be set to an appropriate font) ...
For intX = 0 To Printer.FontCount - 1
If Printer.Fonts(intX) Like "Coudrier*" Then
Printer.FontName = Printer.Fonts(intX)
Exit For
End If
Next
Printer.FontSize = 10
Printer.Orientation = vbPRORLandscape
' initialize page counter ...
mintPageCtr = 0
' prepare file name & number
strBackSlash = IIf(Right$(App.Path, 1) = "\", "", "\")
strCustFileName = App.Path & strBackSlash & "NWSALES.txt"
mintSalesFileNbr = FreeFile
' open the input file
Open strCustFileName For Input As #mintSalesFileNbr
Call PrintHeadings
dblExtPriceGrand = 0
dblDiscAmtGrand = 0
dblSaleTotGrand = 0
' priming read
Call ReadSalesRecord
' main outer processing loop
Do Until mblnEOF
' set up for month break
strPrevMonth = mstrYearMonth
' make sure we have enough room to print the month/year heading line
' plus at least one detail line
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
' print the month heading line
Printer.FontBold = True
Printer.Print Tab(mintLINE_START_POS); _
UCase$(MonthName(CLng(Right$(mstrYearMonth, 2)))) _
& ", " _
& Left$(mstrYearMonth, 4)
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 2
dblExtPriceMonth = 0
dblDiscAmtMonth = 0
dblSaleTotMonth = 0
' inner loop 1 for month break
Do Until (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
' if the number of lines printed so far exceeds the
' maximum number of lines allowed on a page, invoke
' the PrintHeadings sub to do a page break
If mintLineCtr > intLINES_PER_PAGE Then
Call PrintHeadings
End If
dblExtPriceDtl = msngUnitPrice * mintQty
dblDiscAmtDtl = dblExtPriceDtl * msngDiscountPct
dblSaleTotDtl = dblExtPriceDtl - dblDiscAmtDtl
' print a line of data
Printer.Print Tab(11 + mintLINE_START_POS); _
mstrOrderDate; _
Tab(23 + mintLINE_START_POS); _
mstrOrderNbr; _
Tab(30 + mintLINE_START_POS); _
mstrCustID; _
Tab(37 + mintLINE_START_POS); _
mstrProdDesc; _
Tab(70 + mintLINE_START_POS); _
Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _
Tab(79 + mintLINE_START_POS); _
Format$(mintQty, "@@@"); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))
' increment the line count
mintLineCtr = mintLineCtr + 1
' "roll" the detail amounts to the month totals
dblExtPriceMonth = dblExtPriceMonth + dblExtPriceDtl
dblDiscAmtMonth = dblDiscAmtMonth + dblDiscAmtDtl
dblSaleTotMonth = dblSaleTotMonth + dblSaleTotDtl
' read the next record
Call ReadSalesRecord
Loop
' end of inner loop 1
' we have our break on month, so print the month totals
' make sure we have enough room to print the lines for the month totals
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
Printer.Print Tab(84 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(96 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(108 + mintLINE_START_POS); _
String$(11, "-")
Printer.FontBold = True
Printer.Print Tab(56 + mintLINE_START_POS); _
Format$("TOTALS FOR " & _
UCase$(MonthName(CLng(Right$(strPrevMonth, 2)))) & ", " & _
Left$(strPrevMonth, 4) & ":", String$(27, "@")); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceMonth, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtMonth, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotMonth, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 3
' "roll" the month totals to the grand totals
dblExtPriceGrand = dblExtPriceGrand + dblExtPriceMonth
dblDiscAmtGrand = dblDiscAmtGrand + dblDiscAmtMonth
dblSaleTotGrand = dblSaleTotGrand + dblSaleTotMonth
Loop
' end of outer processing loop - time to print the grand totals
' make sure we have enough room to print the lines for the grand total
If mintLineCtr > (intLINES_PER_PAGE - 2) Then
Call PrintHeadings
End If
Printer.Print
Printer.FontBold = True
Printer.Print Tab(70 + mintLINE_START_POS); _
"GRAND TOTALS:"; _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceGrand, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtGrand, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotGrand, "Currency"), String$(11, "@"))
Printer.FontBold = False
' close the input file
Close #mintSalesFileNbr
' Important! When done, the EndDoc method of the Printer object must be invoked.
' The EndDoc method terminates a print operation sent to the Printer object,
' releasing the document to the print device or spooler.
Printer.EndDoc
cmdExit.SetFocus
Exit Sub
cmdPrint_Click_Error:
If MsgBox("Error # " & Err.Number & " - " & Err.Description & vbNewLine & _
"Do you want to continue?", _
vbYesNo + vbQuestion, _
"Error") = vbYes Then
Resume Next
Else
End
End If
End Sub
Download the project files for Sample Program 2 here.
Sample Program 3
Sample Program 3 builds on Sample Program 2. In addition to the year-month control break implemented in Sample Program 2, Sample Program 3 implements an additional control break on the order date.
Excerpts of the output report produced by Sample Program 3 are shown below. Note that the report now includes subtotals by order date in addition to subtotals by year-month. Note also that for each group of items printed where the order date is the same, the date is only displayed on the first line of that group; it is blanked out for the remaining lines. This technique is called group indication. (We are also "group indicating" by printing the year/month in its own sub-heading.) The idea behind group indication is to improve readability of the report and suppress redundant information.
As in both Sample Program 2 and Sample Program 1, grand totals are printed in bold at the end of the report.
Print Date: 05/01/05 THEVBPROGRAMMER.COM Page: 1
Print Time: 14:08:22 NORTHWIND TRADERS SALES REPORT
WITH CONTROL BREAKS ON DATE AND MONTH
ORDER CUST UNIT EXTENDED DISCOUNT
ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL
---------- ------ -- ------------------- ----- --- ----- ------ ----------
OCTOBER, 2004
10/3/2004 10295 VINET Gnocchi di nonna Alice 30.40 4 $121.60 $0.00 $121.60
----------- ----------- -----------
TOTALS FOR 10/3/2004: $121.60 $0.00 $121.60
10/4/2004 10296 LILAS Pavlova 13.90 30 $417.00 $0.00 $417.00
10296 LILAS Gudbrandsdalsost 28.80 15 $432.00 $0.00 $432.00
10296 LILAS Queso Cabrales 16.80 12 $201.60 $0.00 $201.60
----------- ----------- -----------
TOTALS FOR 10/4/2004: $1,050.60 $0.00 $1,050.60
10/5/2004 10297 BLONP Mozzarella di Giovanni 27.80 20 $556.00 $0.00 $556.00
10297 BLONP Chartreuse verte 14.40 60 $864.00 $0.00 $864.00
----------- ----------- -----------
TOTALS FOR 10/5/2004: $1,420.00 $0.00 $1,420.00
10/6/2004 10298 HUNGO Chang 15.20 40 $608.00 $0.00 $608.00
10298 HUNGO Inlagd Sill 15.20 40 $608.00 $152.00 $456.00
10298 HUNGO Raclette Courdavault 44.00 30 $1,320.00 $330.00 $990.00
10298 HUNGO Tarte au sucre 39.40 15 $591.00 $0.00 $591.00
----------- ----------- -----------
TOTALS FOR 10/6/2004: $3,127.00 $482.00 $2,645.00
. . .
Print Date: 05/01/05 THEVBPROGRAMMER.COM Page: 11
Print Time: 14:08:22 NORTHWIND TRADERS SALES REPORT
WITH CONTROL BREAKS ON DATE AND MONTH
ORDER CUST UNIT EXTENDED DISCOUNT
ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL
---------- ------ -- ------------------- ----- --- ----- ------ ----------
----------- ----------- -----------
TOTALS FOR 12/29/2004: $970.20 $0.00 $970.20
12/30/2004 10368 ERNSH Wimmers gute Semmelknödel 26.60 35 $931.00 $93.10 $837.90
10368 ERNSH Sir Rodney's Scones 8.00 5 $40.00 $4.00 $36.00
10368 ERNSH Rössle Sauerkraut 36.40 13 $473.20 $47.32 $425.88
10368 ERNSH Ravioli Angelo 15.60 25 $390.00 $0.00 $390.00
----------- ----------- -----------
TOTALS FOR 12/30/2004: $1,834.20 $144.42 $1,689.78
TOTALS FOR DECEMBER, 2004: $51,290.00 $4,105.96 $47,184.05
GRAND TOTALS: $118,543.60 $9,046.43 $109,497.17
The simple interface for the program still remains the same, except for the verbiage in form's Caption to indicate what we are doing:
The difference in the coding between Sample Program 2 and Sample Program 3 all takes place in the cmdPrint_Click event. The code for the event is shown below, with the added code shown in bold.
In examining the code, let's start with the newly added variables. The variable strPrevDate will be needed to compare the current contents of the order date field with its previous contents to detect when a change in that field occurs. We have also added two variables to support "group indication" on the date. The variable blnNewDate will indicate whether or not we are processing the first record of a batch of records with a new date value. The variable strPrintDate will be used to store the contents of the order date field (if blnNewDate is True) or blank spaces (if blnNewDate is False). Also, because we will now have an additional set of totals at the date level for the calculated fields extended price, discount amount, and sale total, we declare the variables dblExtPriceDate, dblDiscAmtDate, and dblSaleTotDate for that purpose.
The next new area of code takes place after the Do Until loop is set up for the year/month break (Do Until (strPrevMonth <> mstrYearMonth) Or (mblnEOF)). It is here that we perform the setup steps for the control break on the order date field.
The statement
strPrevDate = mstrOrderDate
performs the critical step of saving the value of the current date field (mstrOrderDate) to the "comparison" variable strPrevDate.
The statements
dblExtPriceDate = 0
dblDiscAmtDate = 0
dblSaleTotDate = 0
perform the critical step of clearing the date subtotals, which must be cleared at the start of every date group.
The statement
blnNewDate = True
sets the Boolean variable blnNewDate to True, indicating that we have started a new date group. This variable will be tested in the processing loop that follows.
The statement
Do Until (strPrevDate <> mstrOrderDate) _
Or (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
sets up a second inner, or nested loop; it is the body of this inner loop which will process the detail records. The loop condition "Until (strPrevDate <> mstrOrderDate)" means that the loop will keep executing until there is a change (or break) in the mstrOrderDate field – the condition checks the value of strPrevDate (which we have set in the setup steps above) against the value of mstrOrderDate, which is refreshed every time an input record is read. Note also that we are checking for a change in the year/month field (hence the piece Or (strPrevMonth <> mstrYearMonth)). In a control-break program, a break in a higher level automatically causes a break in all lower levels. This loop must also check for the end-of-file condition (hence the piece Or (mblnEOF)), and of course must terminate if that condition occurs. It should be noted that the end-of-file condition is the "mother" of all control breaks – end-of-file automatically causes a break in all control levels.
The statements
If mintLineCtr > intLINES_PER_PAGE Then
Call PrintHeadings
' force "group indicated" info to be printed
' if we've had a page break
blnNewDate = True
End If
If blnNewDate Then
strPrintDate = mstrOrderDate
blnNewDate = False
Else
strPrintDate = " "
End If
handle the group indication feature for the printing of the order date. When we check to see if it is time to perform a page break, not only are the headings printed, but the blnNewDate variable is reset to True – because on the first detail line of a new page, we want the order date to be displayed, even if we are in the middle of a group of records with the same order date. The next If statement tests the value of blnNewDate. If True, the value of the current mstrOrderDate field is assigned to strPrintDate and blnNewDate is set to False; if False, a blank space is assigned to the strPrintDate variable.
The statement that prints the detail line
Printer.Print Tab(11 + mintLINE_START_POS); _
strPrintDate; _
Tab(23 + mintLINE_START_POS); _
mstrOrderNbr; _
Tab(30 + mintLINE_START_POS); _
mstrCustID; _
Tab(37 + mintLINE_START_POS); _
mstrProdDesc; _
Tab(70 + mintLINE_START_POS); _
Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _
Tab(79 + mintLINE_START_POS); _
Format$(mintQty, "@@@"); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))
differs from its predecessor only in that the strPrintDate variable is used to display the date (or a blank space), depending on the result of the group indication logic discussed above.
The statements
dblExtPriceDate = dblExtPriceDate + dblExtPriceDtl
dblDiscAmtDate = dblDiscAmtDate + dblDiscAmtDtl
dblSaleTotDate = dblSaleTotDate + dblSaleTotDtl
add the calculated fields at the detail level to their corresponding date totals. In Sample Program 1, the calculated fields at the detail level were added directly to the corresponding grand totals; in Sample Program 2, the calculated fields at the detail level were added to their corresponding year-month totals; now in Sample Program 3, there is the additional date subtotal level, and it is these date subtotal accumulators to which the detail variables must be added. As mentioned earlier, the rule in a control-break subtotal program is that detail-level values or subtotal values are added (or "rolled") to the next highest level of totals.
The next set of new statements handle the control break condition – i.e., the code does what needs to be done when a change in the order date is detected (which is basically to print the date subtotals and roll the date subtotals to the year-month subtotals):
Once we move past the
Loop
statement, this means a change has occurred in the value of the order date field. We have printed all the detail records for one date, and we have the first record for the next date in memory waiting to be processed. However, before we start processing the new date, we have some unfinished business to take care of with the previous date. First, we must print the subtotals for the previous date. We are going to use three lines to do this: a "dash line", the subtotal line itself, and a blank line following. The statement
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
checks to see if there is enough room on the current page to print those three lines. If not, the PrintHeadings Sub is called.
The statement
Printer.Print Tab(84 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(96 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(108 + mintLINE_START_POS); _
String$(11, "-")
prints a string of dashes under each detail field.
The statements
Printer.FontBold = True
Printer.Print Tab(61 + mintLINE_START_POS); _
Format$("TOTALS FOR " & strPrevDate & ":", String$(22, "@")); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDate, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDate, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDate, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 3
turn bold styling on, print the subtotals, turn bold styling off, print the blank line, and increment the line counter by 3. Note that the part of the Print statement that prints "TOTALS FOR (Date):" uses strPrevDate, NOT mstrOrderDate – this is because mstrOrderDate now reflects the new date, whereas strPrevDate has not yet changed and so still has the value for the previous date, which is what we need when printing this line. Note also that the string "TOTALS FOR (Date):" is formatted with a string of 22 "@" symbols, causing that string to be right-justified when printed.
The next set of statements add the date subtotals to their corresponding year/month subtotals. (As mentioned earlier, in a control-break subtotal program, subtotal values are added (or "rolled") to the next highest level of totals.)
dblExtPriceMonth = dblExtPriceMonth + dblExtPriceDate
dblDiscAmtMonth = dblDiscAmtMonth + dblDiscAmtDate
dblSaleTotMonth = dblSaleTotMonth + dblSaleTotDate
From that point on, the code for this program is very similar to its precedessor. (The Loop statement returns the program to the top of the first inner processing loop, which will do the setup for the next date group, process the detail records for that group, generate the date group's subtotals, roll those subtotals to the year/month subtotals, and so on for each date group. When there is a break on year/month, control will return to the main outer processing loop, which will do the setup for the next year/month group, process the date groups for that year/month group, generate the year/month group's subtotals, roll those year/month subtotals to the grand totals, and so on for each year/month group.) But when the end of file condition occurs, the grand totals are printed, the wrap-up code executes, and the report is done.
'-----------------------------------------------------------------------------
Private Sub cmdPrint_Click()
'-----------------------------------------------------------------------------
Dim intX As Integer
Dim strCustFileName As String
Dim strBackSlash As String
Dim strPrevMonth As String
Dim strPrevDate As String
Dim blnNewDate As Boolean
Dim strPrintDate As String
Dim dblExtPriceDtl As Double
Dim dblDiscAmtDtl As Double
Dim dblSaleTotDtl As Double
Dim dblExtPriceDate As Double
Dim dblDiscAmtDate As Double
Dim dblSaleTotDate As Double
Dim dblExtPriceMonth As Double
Dim dblDiscAmtMonth As Double
Dim dblSaleTotMonth As Double
Dim dblExtPriceGrand As Double
Dim dblDiscAmtGrand As Double
Dim dblSaleTotGrand As Double
Const intLINES_PER_PAGE As Integer = 47
On Error GoTo cmdPrint_Click_Error
' Have the user make sure his/her printer is ready ...
If MsgBox("Make sure your printer is on-line and " _
& "loaded with paper.", vbOKCancel, "Check Printer") = vbCancel _
Then
Exit Sub
End If
' Set the printer font to Courier, if available (otherwise, we would be
' relying on the default font for the Windows printer, which may or
' may not be set to an appropriate font) ...
For intX = 0 To Printer.FontCount - 1
If Printer.Fonts(intX) Like "Courier*" Then
Printer.FontName = Printer.Fonts(intX)
Exit For
End If
Next
Printer.FontSize = 10
Printer.Orientation = vbPRORLandscape
' initialize page counter ...
mintPageCtr = 0
' prepare file name & number
strBackSlash = IIf(Right$(App.Path, 1) = "\", "", "\")
strCustFileName = App.Path & strBackSlash & "NWSALES.txt"
mintSalesFileNbr = FreeFile
' open the input file
Open strCustFileName For Input As #mintSalesFileNbr
Call PrintHeadings
dblExtPriceGrand = 0
dblDiscAmtGrand = 0
dblSaleTotGrand = 0
' priming read
Call ReadSalesRecord
' main outer processing loop
Do Until mblnEOF
' set up for month break
strPrevMonth = mstrYearMonth
' make sure we have enough room to print the month/year heading line
' plus at least one detail line
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
' print the month heading line
Printer.FontBold = True
Printer.Print Tab(mintLINE_START_POS); _
UCase$(MonthName(CLng(Right$(mstrYearMonth, 2)))) _
& ", " _
& Left$(mstrYearMonth, 4)
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 2
dblExtPriceMonth = 0
dblDiscAmtMonth = 0
dblSaleTotMonth = 0
' inner loop 1 for month break
Do Until (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
' set up for date break
strPrevDate = mstrOrderDate
dblExtPriceDate = 0
dblDiscAmtDate = 0
dblSaleTotDate = 0
blnNewDate = True
' inner loop 2 for date break
Do Until (strPrevDate <> mstrOrderDate) _
Or (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
' if the number of lines printed so far exceeds the
' maximum number of lines allowed on a page, invoke
' the PrintHeadings sub to do a page break
If mintLineCtr > intLINES_PER_PAGE Then
Call PrintHeadings
' force "group indicated" info to be printed
' if we've had a page break
blnNewDate = True
End If
If blnNewDate Then
strPrintDate = mstrOrderDate
blnNewDate = False
Else
strPrintDate = " "
End If
dblExtPriceDtl = msngUnitPrice * mintQty
dblDiscAmtDtl = dblExtPriceDtl * msngDiscountPct
dblSaleTotDtl = dblExtPriceDtl - dblDiscAmtDtl
' print a line of data
Printer.Print Tab(11 + mintLINE_START_POS); _
strPrintDate; _
Tab(23 + mintLINE_START_POS); _
mstrOrderNbr; _
Tab(30 + mintLINE_START_POS); _
mstrCustID; _
Tab(37 + mintLINE_START_POS); _
mstrProdDesc; _
Tab(70 + mintLINE_START_POS); _
Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _
Tab(79 + mintLINE_START_POS); _
Format$(mintQty, "@@@"); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))
' increment the line count
mintLineCtr = mintLineCtr + 1
' "roll" the detail amounts to the date totals
dblExtPriceDate = dblExtPriceDate + dblExtPriceDtl
dblDiscAmtDate = dblDiscAmtDate + dblDiscAmtDtl
dblSaleTotDate = dblSaleTotDate + dblSaleTotDtl
' read the next record
Call ReadSalesRecord
Loop
' end of inner loop 2
' we have our break on date, so print the date totals
' make sure we have enough room to print the lines for the date totals
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
Printer.Print Tab(84 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(96 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(108 + mintLINE_START_POS); _
String$(11, "-")
Printer.FontBold = True
Printer.Print Tab(61 + mintLINE_START_POS); _
Format$("TOTALS FOR " & strPrevDate & ":", String$(22, "@")); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDate, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDate, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDate, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 3
' "roll" the date totals to the month totals
dblExtPriceMonth = dblExtPriceMonth + dblExtPriceDate
dblDiscAmtMonth = dblDiscAmtMonth + dblDiscAmtDate
dblSaleTotMonth = dblSaleTotMonth + dblSaleTotDate
Loop
' end of inner loop 1
' we have our break on month, so print the month totals
' make sure we have enough room to print the lines for the month totals
If mintLineCtr > (intLINES_PER_PAGE - 2) Then
Call PrintHeadings
End If
Printer.FontBold = True
Printer.Print Tab(56 + mintLINE_START_POS); _
Format$("TOTALS FOR " & _
UCase$(MonthName(CLng(Right$(strPrevMonth, 2)))) & ", " & _
Left$(strPrevMonth, 4) & ":", String$(27, "@")); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceMonth, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtMonth, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotMonth, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 2
' "roll" the month totals to the grand totals
dblExtPriceGrand = dblExtPriceGrand + dblExtPriceMonth
dblDiscAmtGrand = dblDiscAmtGrand + dblDiscAmtMonth
dblSaleTotGrand = dblSaleTotGrand + dblSaleTotMonth
Loop
' end of outer processing loop - time to print the grand totals
' make sure we have enough room to print the lines for the grand total
If mintLineCtr > (intLINES_PER_PAGE - 2) Then
Call PrintHeadings
End If
Printer.Print
Printer.FontBold = True
Printer.Print Tab(70 + mintLINE_START_POS); _
"GRAND TOTALS:"; _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceGrand, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtGrand, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotGrand, "Currency"), String$(11, "@"))
Printer.FontBold = False
' close the input file
Close #mintSalesFileNbr
' Important! When done, the EndDoc method of the Printer object must be invoked.
' The EndDoc method terminates a print operation sent to the Printer object,
' releasing the document to the print device or spooler.
Printer.EndDoc
cmdExit.SetFocus
Exit Sub
cmdPrint_Click_Error:
If MsgBox("Error # " & Err.Number & " - " & Err.Description & vbNewLine & _
"Do you want to continue?", _
vbYesNo + vbQuestion, _
"Error") = vbYes Then
Resume Next
Else
End
End If
End Sub
Download the project files for Sample Program 3 here.
Sample Program 4
Sample Program 4 builds on Sample Program 3. In addition to the year-month and order date control breaks implemented in Sample Program 3, Sample Program 4 implements an additional control break on order number.
Excerpts of the output report produced by Sample Program 4 are shown below. Note that the report now includes subtotals by order number in addition to subtotals by year-month and order date. Note also that the technique of group indication has been applied to the order number (and its associated field, the customer ID, because an order is always associated with a single customer). The order number and customer ID are displayed on the first line of the group of records associated with an order, but blanked out (suppressed) for the remaining lines of the group. As mentioned earlier, the idea behind group indication is to improve readability of the report and suppress redundant information.
As in both Sample Programs 1, 2, and 3, grand totals are printed in bold at the end of the report.
Print Date: 04/25/05 THEVBPROGRAMMER.COM Page: 14
Print Time: 22:03:35 NORTHWIND TRADERS SALES REPORT
WITH BREAKS ON ORDER, DATE, AND MONTH
ORDER CUST UNIT EXTENDED DISCOUNT
ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL
---------- ------ -- ------------------- ----- --- ----- ------ ----------
TOTALS FOR 12/23/2004: $9,663.80 $227.36 $9,436.44
12/26/2004 10362 BONAP NuNuCa Nuß-Nougat-Creme 11.20 50 $560.00 $0.00 $560.00
Manjimup Dried Apples 42.40 20 $848.00 $0.00 $848.00
Tourtière 5.90 24 $141.60 $0.00 $141.60
----------- ----------- -----------
TOTALS FOR ORDER NUMBER 10362: $1,549.60 $0.00 $1,549.60
TOTALS FOR 12/26/2004: $1,549.60 $0.00 $1,549.60
12/27/2004 10363 DRACD Gorgonzola Telino 10.00 20 $200.00 $0.00 $200.00
Rhönbräu Klosterbier 6.20 12 $74.40 $0.00 $74.40
Lakkalikööri 14.40 12 $172.80 $0.00 $172.80
----------- ----------- -----------
TOTALS FOR ORDER NUMBER 10363: $447.20 $0.00 $447.20
10364 EASTC Gudbrandsdalsost 28.80 30 $864.00 $0.00 $864.00
Fløtemysost 17.20 5 $86.00 $0.00 $86.00
----------- ----------- -----------
TOTALS FOR ORDER NUMBER 10364: $950.00 $0.00 $950.00
TOTALS FOR 12/27/2004: $1,397.20 $0.00 $1,397.20
12/28/2004 10365 ANTON Queso Cabrales 16.80 24 $403.20 $0.00 $403.20
----------- ----------- -----------
TOTALS FOR ORDER NUMBER 10365: $403.20 $0.00 $403.20
TOTALS FOR 12/28/2004: $403.20 $0.00 $403.20
12/29/2004 10366 GALED Louisiana Fiery Hot Pepper Sauce 16.80 5 $84.00 $0.00 $84.00
Original Frankfurter grüne Soße 10.40 5 $52.00 $0.00 $52.00
----------- ----------- -----------
TOTALS FOR ORDER NUMBER 10366: $136.00 $0.00 $136.00
10367 VAFFE Sasquatch Ale 11.20 36 $403.20 $0.00 $403.20
Tourtière 5.90 18 $106.20 $0.00 $106.20
Louisiana Fiery Hot Pepper Sauce 16.80 15 $252.00 $0.00 $252.00
Original Frankfurter grüne Soße 10.40 7 $72.80 $0.00 $72.80
Print Date: 04/25/05 THEVBPROGRAMMER.COM Page: 15
Print Time: 22:03:35 NORTHWIND TRADERS SALES REPORT
WITH BREAKS ON ORDER, DATE, AND MONTH
ORDER CUST UNIT EXTENDED DISCOUNT
ORDER DATE NUMBER ID PRODUCT DESCRIPTION PRICE QTY PRICE AMOUNT SALE TOTAL
---------- ------ -- ------------------- ----- --- ----- ------ ----------
----------- ----------- -----------
TOTALS FOR ORDER NUMBER 10367: $834.20 $0.00 $834.20
TOTALS FOR 12/29/2004: $970.20 $0.00 $970.20
12/30/2004 10368 ERNSH Wimmers gute Semmelknödel 26.60 35 $931.00 $93.10 $837.90
Sir Rodney's Scones 8.00 5 $40.00 $4.00 $36.00
Rössle Sauerkraut 36.40 13 $473.20 $47.32 $425.88
Ravioli Angelo 15.60 25 $390.00 $0.00 $390.00
----------- ----------- -----------
TOTALS FOR ORDER NUMBER 10368: $1,834.20 $144.42 $1,689.78
TOTALS FOR 12/30/2004: $1,834.20 $144.42 $1,689.78
TOTALS FOR DECEMBER, 2004: $51,290.00 $4,105.96 $47,184.05
GRAND TOTALS: $118,543.60 $9,046.43 $109,497.17
The simple interface for the program still remains the same, except for the verbiage in form's Caption to indicate what we are doing:
The difference in the coding between Sample Program 3 and Sample Program 4 all takes place in the cmdPrint_Click event. The code for the event is shown below, with the added code shown in bold.
In examining the code, let's start with the newly added variables. The variable strPrevOrderNbr will be needed to compare the current contents of the order number field with its previous contents to detect when a change in that field occurs. We have also added three variables to support "group indication" on the order number. The variable blnNewOrder will indicate whether or not we are processing the first record of a batch of records with a new order number value. The variables strPrintOrderNbr and strPrintCustID will be used to store the contents of the order number and customer ID fields respectively (if blnNewOrder is True) or blank spaces in each (if blnNewOrder is False). Also, because we will now have an additional set of totals at the order number level for the calculated fields extended price, discount amount, and sale total, we declare the variables dblExtPriceOrd, dblDiscAmtOrd, and dblSaleTotOrd for that purpose.
The next new area of code takes place after the Do Until loop is set up for the date break (Do Until (strPrevDate <> mstrOrderDate) Or (strPrevMonth <> mstrYearMonth) Or (mblnEOF)). It is here that we perform the setup steps for the control break on the order number field.
The statement
strPrevOrderNbr = mstrOrderNbr
performs the critical step of saving the value of the current order number field (mstrOrderNbr) to the "comparison" variable strPrevOrderNbr.
The statements
dblExtPriceOrd = 0
dblDiscAmtOrd = 0
dblSaleTotOrd = 0
perform the critical step of clearing the order number subtotals, which must be cleared at the start of every order number group.
The statement
blnNewOrder = True
sets the Boolean variable blnNewOrder to True, indicating that we have started a new order number group. This variable will be tested in the processing loop that follows.
The statement
Do Until (strPrevOrderNbr <> mstrOrderNbr) _
Or (strPrevDate <> mstrOrderDate) _
Or (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
sets up a third inner, or nested loop; it is the body of this inner loop which will process the detail records. The loop condition "Until (strPrevOrderNbr <> mstrOrderNbr)" means that the loop will keep executing until there is a change (or break) in the mstrOrderNbr field – the condition checks the value of strPrevOrderNbr (which we have set in the setup steps above) against the value of mstrOrderNbr, which is refreshed every time an input record is read. Note also that we are checking for a change in the date field (hence the piece Or (strPrevDate <> mstrOrderDate)) and we are checking for a change in the year/month field (hence the piece Or (strPrevMonth <> mstrYearMonth)). As mentioned several times in this article, in a control-break program, a break in a higher level automatically causes a break in all lower levels. This loop must also check for the end-of-file condition (hence the piece Or (mblnEOF)), and of course must terminate if that condition occurs. Recall that the end-of-file condition is the "mother" of all control breaks – end-of-file automatically causes a break in all control levels.
The statements
If mintLineCtr > intLINES_PER_PAGE Then
Call PrintHeadings
' force "group indicated" info to be printed
' if we've had a page break
blnNewDate = True
blnNewOrder = True
End If
If blnNewDate Then
strPrintDate = mstrOrderDate
blnNewDate = False
Else
strPrintDate = " "
End If
If blnNewOrder Then
strPrintOrderNbr = mstrOrderNbr
strPrintCustID = mstrCustID
blnNewOrder = False
Else
strPrintOrderNbr = " "
strPrintCustID = " "
End If
handle the group indication feature for the printing of the order number as well as for the order date (the code to do this from the preceding sample program is still needed here). When we check to see if it is time to perform a page break, not only are the headings printed, but the blnNewOrder and the blnNewDate variables are reset to True – because on the first detail line of a new page, we want the order number and the order date to be displayed, even if we are in the middle of a group of records with the same order date and/or order number. The next If statement (retained from the previous sample program) tests the value of blnNewDate. If True, the value of the current mstrOrderDate field is assigned to strPrintDate and blnNewDate is set to False; if False, a blank space is assigned to the strPrintDate variable. The next If statement tests the value of blnNewOrder. If True, the value of the current mstrOrderNbr field is assigned to strPrintOrderNbr and the value of the current mstrCustID field is assigned to the strPrintCustID field, and blnNewOrder is set to False; if False, a blank space is assigned to both the strPrintOrderNbr and strPrintCustID variables.
The statement that prints the detail line
Printer.Print Tab(11 + mintLINE_START_POS); _
strPrintDate; _
Tab(23 + mintLINE_START_POS); _
strPrintOrderNbr; _
Tab(30 + mintLINE_START_POS); _
strPrintCustID; _
Tab(37 + mintLINE_START_POS); _
mstrProdDesc; _
Tab(70 + mintLINE_START_POS); _
Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _
Tab(79 + mintLINE_START_POS); _
Format$(mintQty, "@@@"); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))
differs from its predecessor only in that the strPrintOrderNbr and strPrintCustID variables are used to display the order number and customerID (or a blank spaces) respectively, depending on the result of the group indication logic discussed above.
The statements
dblExtPriceOrd = dblExtPriceOrd + dblExtPriceDtl
dblDiscAmtOrd = dblDiscAmtOrd + dblDiscAmtDtl
dblSaleTotOrd = dblSaleTotOrd + dblSaleTotDtl
add the calculated fields at the detail level to their corresponding order number subtotals. In Sample Program 1, the calculated fields at the detail level were added directly to the corresponding grand totals; in Sample Program 2, the calculated fields at the detail level were added to their corresponding year-month totals; in Sample Program 3, the calculated fields at the detail level were added to their corresponding date totals; now in Sample Program 4, there is the additional order number subtotal level, and it is these order number subtotal accumulators to which the detail variables must be added. As mentioned earlier, the rule in a control-break subtotal program is that detail-level values or subtotal values are added (or "rolled") to the next highest level of totals.
The next set of new statements handle the control break condition – i.e., the code does what needs to be done when a change in the order number is detected (which is basically to print the order number subtotals and roll the order number subtotals to the date subtotals):
Once we move past the
Loop
statement, this means a change has occurred in the value of the order number field. We have printed all the detail records for one order number, and we have the first record for the next order number in memory waiting to be processed. However, before we start processing the new order number, we have some unfinished business to take care of with the previous order number. First, we must print the subtotals for the previous order number. We are going to use three lines to do this: a "dash line", the subtotal line itself, and a blank line following. The statement
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
checks to see if there is enough room on the current page to print those three lines. If not, the PrintHeadings Sub is called.
The statement
Printer.Print Tab(84 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(96 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(108 + mintLINE_START_POS); _
String$(11, "-")
prints a string of dashes under each detail field.
The statements
Printer.FontBold = True
Printer.Print Tab(53 + mintLINE_START_POS); _
"TOTALS FOR ORDER NUMBER " & strPrevOrderNbr & ":"; _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceOrd, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtOrd, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotOrd, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 3
turn bold styling on, print the subtotals, turn bold styling off, print the blank line, and increment the line counter by 3. Note that the part of the Print statement that prints "TOTALS FOR ORDER NUMBER XXXXX:" uses strPrevOrderNbr, NOT mstrOrderNbr – this is because mstrOrderNbr now reflects the new order number, whereas strPrevOrderNbr has not yet changed and so still has the value for the previous order number, which is what we need when printing this line.
The next set of statements add the order number subtotals to their corresponding date subtotals. (As mentioned earlier, in a control-break subtotal program, subtotal values are added (or "rolled") to the next highest level of totals.)
dblExtPriceDate = dblExtPriceDate + dblExtPriceOrd
dblDiscAmtDate = dblDiscAmtDate + dblDiscAmtOrd
dblSaleTotDate = dblSaleTotDate + dblSaleTotOrd
From that point on, the code for this program is very similar to its precedessor. (The Loop statement returns the program to the top of the second inner processing loop, which will do the setup for the next order number group, process the detail records for that group, generate the order number group's subtotals, roll those subtotals to the date subtotals, and so on for each order number. When there is a break on date, control will return to the first inner processing loop, which will do the setup for the next date group, process the order number groups for that date group, generate the date group's subtotals, and roll those subtotals to the year/month subtotals, and so on for each date group. When there is a break on year/month, control will return to the main outer processing loop, which will do the setup for the next year/month group, process the date groups for that year/month group, generate the year/month group's subtotals, roll those year/month subtotals to the grand totals, and so on for each year/month group.) But when the end of file condition occurs, the grand totals are printed, the wrap-up code executes, and the report is done.
'-----------------------------------------------------------------------------
Private Sub cmdPrint_Click()
'-----------------------------------------------------------------------------
Dim intX As Integer
Dim strCustFileName As String
Dim strBackSlash As String
Dim strPrevMonth As String
Dim strPrevDate As String
Dim strPrevOrderNbr As String
Dim blnNewDate As Boolean
Dim blnNewOrder As Boolean
Dim strPrintDate As String
Dim strPrintOrderNbr As String
Dim strPrintCustID As String
Dim dblExtPriceDtl As Double
Dim dblDiscAmtDtl As Double
Dim dblSaleTotDtl As Double
Dim dblExtPriceOrd As Double
Dim dblDiscAmtOrd As Double
Dim dblSaleTotOrd As Double
Dim dblExtPriceDate As Double
Dim dblDiscAmtDate As Double
Dim dblSaleTotDate As Double
Dim dblExtPriceMonth As Double
Dim dblDiscAmtMonth As Double
Dim dblSaleTotMonth As Double
Dim dblExtPriceGrand As Double
Dim dblDiscAmtGrand As Double
Dim dblSaleTotGrand As Double
Const intLINES_PER_PAGE As Integer = 47
On Error GoTo cmdPrint_Click_Error
' Have the user make sure his/her printer is ready ...
If MsgBox("Make sure your printer is on-line and " _
& "loaded with paper.", vbOKCancel, "Check Printer") = vbCancel _
Then
Exit Sub
End If
' Set the printer font to Courier, if available (otherwise, we would be
' relying on the default font for the Windows printer, which may or
' may not be set to an appropriate font) ...
For intX = 0 To Printer.FontCount - 1
If Printer.Fonts(intX) Like "Courier*" Then
Printer.FontName = Printer.Fonts(intX)
Exit For
End If
Next
Printer.FontSize = 10
Printer.Orientation = vbPRORLandscape
' initialize page counter ...
mintPageCtr = 0
' prepare file name & number
strBackSlash = IIf(Right$(App.Path, 1) = "\", "", "\")
strCustFileName = App.Path & strBackSlash & "NWSALES.txt"
mintSalesFileNbr = FreeFile
' open the input file
Open strCustFileName For Input As #mintSalesFileNbr
Call PrintHeadings
dblExtPriceGrand = 0
dblDiscAmtGrand = 0
dblSaleTotGrand = 0
' priming read
Call ReadSalesRecord
' main outer processing loop
Do Until mblnEOF
' set up for month break
strPrevMonth = mstrYearMonth
' make sure we have enough room to print the month/year heading line
' plus at least one detail line
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
' print the month heading line
Printer.FontBold = True
Printer.Print Tab(mintLINE_START_POS); _
UCase$(MonthName(CLng(Right$(mstrYearMonth, 2)))) _
& ", " _
& Left$(mstrYearMonth, 4)
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 2
dblExtPriceMonth = 0
dblDiscAmtMonth = 0
dblSaleTotMonth = 0
' inner loop 1 for month break
Do Until (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
' set up for date break
strPrevDate = mstrOrderDate
dblExtPriceDate = 0
dblDiscAmtDate = 0
dblSaleTotDate = 0
blnNewDate = True
' inner loop 2 for date break
Do Until (strPrevDate <> mstrOrderDate) _
Or (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
' set up for order break
strPrevOrderNbr = mstrOrderNbr
dblExtPriceOrd = 0
dblDiscAmtOrd = 0
dblSaleTotOrd = 0
blnNewOrder = True
' inner loop 3 for order number break
Do Until (strPrevOrderNbr <> mstrOrderNbr) _
Or (strPrevDate <> mstrOrderDate) _
Or (strPrevMonth <> mstrYearMonth) _
Or (mblnEOF)
' if the number of lines printed so far exceeds the
' maximum number of lines allowed on a page, invoke
' the PrintHeadings sub to do a page break
If mintLineCtr > intLINES_PER_PAGE Then
Call PrintHeadings
' force "group indicated" info to be printed
' if we've had a page break
blnNewDate = True
blnNewOrder = True
End If
If blnNewDate Then
strPrintDate = mstrOrderDate
blnNewDate = False
Else
strPrintDate = " "
End If
If blnNewOrder Then
strPrintOrderNbr = mstrOrderNbr
strPrintCustID = mstrCustID
blnNewOrder = False
Else
strPrintOrderNbr = " "
strPrintCustID = " "
End If
dblExtPriceDtl = msngUnitPrice * mintQty
dblDiscAmtDtl = dblExtPriceDtl * msngDiscountPct
dblSaleTotDtl = dblExtPriceDtl - dblDiscAmtDtl
' print a line of data
Printer.Print Tab(11 + mintLINE_START_POS); _
strPrintDate; _
Tab(23 + mintLINE_START_POS); _
strPrintOrderNbr; _
Tab(30 + mintLINE_START_POS); _
strPrintCustID; _
Tab(37 + mintLINE_START_POS); _
mstrProdDesc; _
Tab(70 + mintLINE_START_POS); _
Format$(Format$(msngUnitPrice, "##0.00"), "@@@@@@"); _
Tab(79 + mintLINE_START_POS); _
Format$(mintQty, "@@@"); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDtl, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDtl, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDtl, "Currency"), String$(11, "@"))
' increment the line count
mintLineCtr = mintLineCtr + 1
' "roll" the detail amounts to the order totals
dblExtPriceOrd = dblExtPriceOrd + dblExtPriceDtl
dblDiscAmtOrd = dblDiscAmtOrd + dblDiscAmtDtl
dblSaleTotOrd = dblSaleTotOrd + dblSaleTotDtl
' read the next record
Call ReadSalesRecord
Loop
' end of inner loop 3
' we have our break on order number, so print the order totals
' make sure we have enough room to print the lines for the date totals
If mintLineCtr > (intLINES_PER_PAGE - 3) Then
Call PrintHeadings
End If
Printer.Print Tab(84 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(96 + mintLINE_START_POS); _
String$(11, "-"); _
Tab(108 + mintLINE_START_POS); _
String$(11, "-")
Printer.FontBold = True
Printer.Print Tab(53 + mintLINE_START_POS); _
"TOTALS FOR ORDER NUMBER " & strPrevOrderNbr & ":"; _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceOrd, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtOrd, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotOrd, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 3
' "roll" the order totals to the date totals
dblExtPriceDate = dblExtPriceDate + dblExtPriceOrd
dblDiscAmtDate = dblDiscAmtDate + dblDiscAmtOrd
dblSaleTotDate = dblSaleTotDate + dblSaleTotOrd
Loop
' end of inner loop 2
' we have our break on date, so print the date totals
' make sure we have enough room to print the lines for the date totals
If mintLineCtr > (intLINES_PER_PAGE - 2) Then
Call PrintHeadings
End If
Printer.FontBold = True
Printer.Print Tab(61 + mintLINE_START_POS); _
Format$("TOTALS FOR " & strPrevDate & ":", String$(22, "@")); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceDate, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtDate, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotDate, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 2
' "roll" the date totals to the month totals
dblExtPriceMonth = dblExtPriceMonth + dblExtPriceDate
dblDiscAmtMonth = dblDiscAmtMonth + dblDiscAmtDate
dblSaleTotMonth = dblSaleTotMonth + dblSaleTotDate
Loop
' end of inner loop 1
' we have our break on month, so print the month totals
' make sure we have enough room to print the lines for the month totals
If mintLineCtr > (intLINES_PER_PAGE - 2) Then
Call PrintHeadings
End If
Printer.FontBold = True
Printer.Print Tab(56 + mintLINE_START_POS); _
Format$("TOTALS FOR " & _
UCase$(MonthName(CLng(Right$(strPrevMonth, 2)))) & ", " & _
Left$(strPrevMonth, 4) & ":", String$(27, "@")); _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceMonth, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtMonth, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotMonth, "Currency"), String$(11, "@"))
Printer.FontBold = False
Printer.Print
mintLineCtr = mintLineCtr + 2
' "roll" the month totals to the grand totals
dblExtPriceGrand = dblExtPriceGrand + dblExtPriceMonth
dblDiscAmtGrand = dblDiscAmtGrand + dblDiscAmtMonth
dblSaleTotGrand = dblSaleTotGrand + dblSaleTotMonth
Loop
' end of outer processing loop - time to print the grand totals
' make sure we have enough room to print the lines for the grand total
If mintLineCtr > (intLINES_PER_PAGE - 2) Then
Call PrintHeadings
End If
Printer.Print
Printer.FontBold = True
Printer.Print Tab(70 + mintLINE_START_POS); _
"GRAND TOTALS:"; _
Tab(84 + mintLINE_START_POS); _
Format$(Format$(dblExtPriceGrand, "Currency"), String$(11, "@")); _
Tab(96 + mintLINE_START_POS); _
Format$(Format$(dblDiscAmtGrand, "Currency"), String$(11, "@")); _
Tab(108 + mintLINE_START_POS); _
Format$(Format$(dblSaleTotGrand, "Currency"), String$(11, "@"))
Printer.FontBold = False
' close the input file
Close #mintSalesFileNbr
' Important! When done, the EndDoc method of the Printer object must be invoked.
' The EndDoc method terminates a print operation sent to the Printer object,
' releasing the document to the print device or spooler.
Printer.EndDoc
cmdExit.SetFocus
Exit Sub
cmdPrint_Click_Error:
If MsgBox("Error # " & Err.Number & " - " & Err.Description & vbNewLine & _
"Do you want to continue?", _
vbYesNo + vbQuestion, _
"Error") = vbYes Then
Resume Next
Else
End
End If
End Sub
Download the project files for Sample Program 4 here.
A Final Note (Regarding Sorting)
The first statement in this article begins "Control break processing is a technique used to process a sorted sequential file that contains groups of records ...". It must be emphasized that in order to process a file or set of data using the control break techniques discussed in this article, that file or set of data must be sorted on the key field or fields on which you are going to be breaking. For example, the comma-delimited file used in all of the examples in this article was pre-sorted on year-month, then date, then order number.
If you were to adapt the techniques learned in this article to processing a recordset that was the result of a database query, your query would specify the ORDER BY clause on the required fields. If you wanted to process a sequential file like the one used in the examples, and the file was not already sorted in the required sequence, you would have to look into ways of getting sorted prior to running your program. One option, if the file was in comma-delimited format, would be to import it into Excel, use Excel's Data Sort feature, then re-save the sorted results to a new file.
The bottom line is that control break processing requires that the input data is sorted on the control fields.