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

Print

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.