Years ago, I received a client request that stumped me for a couple of days. The application I was working on managed subscriptions to government publications. Under the old system, subscribers were mailed a blank form, a catalog, and instructions for extracting their publication list from cryptic codes on the mailing label. Needless to say, this process had its faults.
The clients were pleased with an Access report that I created for them that contained both subscriber information and current subscription details. The information appeared not as a cryptic code but as line items with all the necessary information extracted from the catalog. Unfortunately, my clients weren’t happy that the report provided no blank lines to add new subscriptions. “No problem,” I said, “I’ll add two or three blank lines to each report.” No good, they said. The clients wanted the Access report to look exactly like the existing paper form—which had exactly 22 lines per page. This turned out to be a lot harder to do in Access than you might think.
Gathering requirements is an iterative process. While I thought I understood what the clients wanted, their rejection of my first prototype made it clear I hadn’t been listening carefully enough. Just to clarify the problem, these were the client-stipulated requirements (see Figure 1):
- The Access report should look exactly like the paper form in layout, content, and fonts.
- There should be exactly 22 rows of boxes to input subscription information.
- All current subscription details should print in the top-most rows.
It should be noted that while the existing data didn’t exceed 22 subscriptions per customer, the possibility of more than 22 subscriptions per customer existed. Supporting this scenario would require adding additional pages that would be identical to the first page, except that summary and totals boxes should show only on the last page. Even if it isn’t requested by the client, it’s not a bad idea allow for any option that represents desirable business growth.
Figure 1
The solution, part 1
My first thought was to use a query-based solution: Pad the recordset with blank subscription entries so each subscriber has exactly 22 subscription records. That proved more difficult than I first anticipated, and I quickly abandoned the idea. Now, years later and with more experience under my belt, I’d consider giving that route another look, so long as I could generate a solution that didn’t include temporary tables. Temporary table solutions in Microsoft Access often create more problems than they solve—loading and unloading temporary tables is expensive and causes your database to bloat over time.
Alternatively, if the recordset for each subscriber can’t be padded to 22 rows, empty detail lines will need to be added to the recordset programmatically. Since I had no idea how to do that, I instinctively turned to the Access 97 Developer’s Handbook and found all the code I needed, or so I thought. The first part of the solution requires the use of three related report property settings:
- MoveLayout—Specifies whether Microsoft Access should move to the next printing location on the page.
- NextRecord—Specifies whether a section should advance to the next record.
- PrintSection—Specifies whether a section should be printed.
These properties are reset to the default value of True each time the Detail_Format event of the report is fired. The default value in these properties produces the behavior typical of an Access report: Move to the next print location, get the next record, and print the data. By tweaking these properties, I should have been able to get the report to print my additional lines. However, there’s a catch. Once an Access report runs out of records, the report won’t print any more lines. The last record can be repeated as many times as you want to pad the rows. The problem is that this causes the last record’s data to be repeated. I tried to set the PrintSection property to False, but then the row with empty boxes didn’t print either.
I struggled with this issue for hours and only succeeded in printing the last record’s data over and over again. Finally, I asked myself, “What if I ‘printed’ the rows using ‘white ink’—that is, a white colored font?” The text would effectively disappear, leaving only empty boxes. This is, in fact, the clever solution alluded to at the outset of the article: I programmatically toggled each control’s ForeColor property to white when printing a blank row.
The complete code to produce the output looks like this:
Option Compare Database
Option Explicit
Const iLines As Integer = 22
Private iTotal As Integer
Private Sub Report_Open(Cancel As Integer)
' get total record count
iTotal = DCount("*", "qryData")
End Sub
Private Sub Detail_Format(Cancel As Integer, _
FormatCount As Integer)
' increment iLine on each detail format
Static iLine As Integer
iLine = iLine + 1
If iLine < iTotal Then
' do nothing ... print as usual
ElseIf iLine = iTotal Then
' if there are more lines to print, set the
' NextRecord property to false, preventing
' the report from exiting prematurely
If iLine < iLines Then Me.NextRecord = False
Else
' set font to white for padded row controls
Me!ItemCode.ForeColor = vbWhite
Me!StockNumber.ForeColor = vbWhite
Me!ItemName.ForeColor = vbWhite
Me!ExpDate.ForeColor = vbWhite
Me!Quantity.ForeColor = vbWhite
Me!Price.ForeColor = vbWhite
Me!Total.ForeColor = vbWhite
' prevent report from advancing past last row
' until all of blank lines has have printed
If iLine < iLines Then Me.NextRecord = False
End If
End Sub
The solution, part 2
Part 1 fulfills all of the clients’ requirements, but it doesn’t address the possibility that some day, a customer may order more than 22 subscriptions. With a few simple modifications, though, my code can provide this kind of flexibility. To allow records to span multiple pages, I needed to add the following logic:
- Given the maximum number of rows per page and the size of the recordset, calculate the total number of rows required.
- Insert page breaks at the appropriate positions.
- Move the summary information from the Page Footer to the Report Footer.
I added another variable, iRptLines, to store the total number of rows. Dividing the total data records by the number of lines per page as an integer division truncates the decimal portion—giving a “rounded down” count of the number of pages required. By adding 1 to this calculation, I ensured that there will be enough pages to accommodate all of the records and allow for blank rows at the end of the report:
' calculate the total number of lines required. iRptLines = ((iTotal \ iLines) + 1) * iLines
At this point in the solution, I needed another clever trick. How could I force a page break in the detail section at regular intervals? In Access, the solution is simple: Add a page break control to the page and programmatically set its Visible property. When the control’s Visible is set to False, no page break occurs. When the property is set to True, the control triggers a new page in the report:
If iLine Mod iLines = 0 Then If iLine <> iRptLines Then Me!ctlBreak.Visible = True Else Me!ctlBreak.Visible = False End If
Reader challenge
My solution met all of the client’s requirements, including the extra requirement that I threw in for flexibility’s sake. And, like all great solutions, it didn’t require much code—27 actual lines. The code executes quickly and is easily portable to other reports. I’ve always been proud of the tricks I came up with to solve this problem, and to this day I still think it’s a good solution.
All the same, I’m not ruling out the possibility that there’s an even more clever Access developer out there with an even more elegant solution. Feel free to play with my code, which is included in the Download file for this article. If you think you’ve got it, drop me an e-mail with your proposal and you may become a Smart Access author.
Other Pages You May Want To Read
Things about Reports that you probably dont know
Reports: Multiple Columns and Subreports
Printing Pesky ZIP Codes and Set the Start Page Number
