This month, Ken takes on just two topics, both centered on reports: creating dot leaders for two-column reports and creating a table of contents (or index) for a report. Both topics work equally well in Access 2 and in Access 95, and he has prepared the examples in both.
When I print a document in Microsoft Word, I can use tabs and tab leaders to print two columns of text with dots separating them. This makes it easy to read across the report and provides the look I want. I can’t figure out how to do the same sort of thing in an Access report, however. It doesn’t appear that Access supports tab leaders like Word does, but can I emulate this behavior in Access?
Creating tab leaders is another one of those tasks that Access doesn’t support directly, but it isn’t very hard to make it happen with a little bit of programming. In general, the concept involves printing some text, then, immediately to the right of the text, printing a series of periods (or any other character) leading up to the beginning of the second column of text. If the right column is to be left-justified, it’s simple — just print the text. If the right column is to be right-justified, then you’ll have to do some work to know where to end the series of periods. Figure 1 shows a sample report that uses dot leaders with a right-justified right-hand column.
Figure 1
Many people have suggested solutions to this problem using a low-tech method (which works only if the right-hand column is to be left-justified): rather than just printing the left-hand text, print the text followed by enough dots to go past the edge of the right-column text. Then, make sure the text box containing the right-column text overlaps and lays on top of the text box for the left column text, and you should be all set. Unfortunately, this method has several drawbacks: you can’t right-justify the right-hand column, and unless you print the left-hand column in a mono-spaced font, the periods won’t line up from row to row. This looks very odd, and won’t do in cases where your report has to look just right.
The solution suggested here uses the TextWidth property of a report to calculate the width of the text to be printed on each row. As you print each row of the report, calculate the width of the left-hand text and make sure its text box is just wide enough to display the text. If you want the right-hand column left justified, there’s nothing more to do. If you want it right-justified, you’ll need to also find the width of the text in that column, size the text box accordingly, and position it so it lines up with the right margin. “Then where do the leader characters come from,” you ask? Easy: it’s just a label, sized from the left edge of the left-hand text box to the right edge of the right-hand text box, filled with the leader character you want to use. Once you’ve used the Format, Send to Back menu item, the two text boxes will float on top of it, and resizing the text boxes will give the effect you want.
Finally, then, how do you effect this magic? It’s actually quite simple, especially if you don’t care to right-justify the right-hand text box’s text. The technique centers on the TextWidth property of the report: you send TextWidth a piece of text and, based on the current font and style, it calculates the width the text will take up on the report, measured in twips. Once you know the correct size, you can set the text box’s Width property accordingly. There’s one caveat, however — Access seems to be somewhat parsimonious in the widths it returns. I’ve found that I need to add a space character to the width of any text I pass to TextWidth in order to get a width that allows all the characters to be seen in the text box. (If you don’t believe me, try removing the ” ” characters in the code. If your situation matches mine, your text will be truncated.) Code like this, called from the Detail section’s Format event, can calculate the width for the left-hand text box (named txtContactName in this example):
Me!txtContactName.Width = _ Me.TextWidth(Me!txtContactName & " ")
If you left-justify the right-hand text box, you’re done. If you want it to be right-justified, you’ve got to do a bit more work. You’ll need to calculate and set its width, and then move it to the right so that it lines up with the right margin. The example uses code something like this:
Set ctl = Me!txtPhone ' Set the column width sngWidth = Me.TextWidth(ctl.Value & " ") ctl.Width = sngWidth ' Back it up to the right margin, at 3 inches. ' The actual code uses constants for these ' hard-coded values. ctl.Left = (3 * 1440) - sngWidth
To use this technique in your own reports, follow these steps:
1. Place the two fields you want displayed on your report onto the design surface, as text boxes, at the same horizontal location.
2. Remove the labels for the text boxes (or move the labels to the page header).
3. If you want the right-hand text box right-justified, set the Alignment property correctly.
4. Create a label control, stretching from the left edge of the left-hand text box, to the right edge of the right-hand text box, filled with your leader character. Figure 2 shows the report at this point.
5. Move the label control so it’s at the same horizontal position as the other two text boxes.
6. With the label selected, use the Format, Send to Back menu item to place it behind the two text box controls.
7. Import the module basLeader into your database.
8. In the detail section’s Format event procedure, add this single line of code, replacing the two text box names with the names of your own left- and right-hand text boxes. Set the last parameter to True to cause the right-hand text box to be right-justified; otherwise, set it to False:
Call HandleLeader(Me, Me![LeftHandTextBox], _ Me![RightHandTextBox], True)
Figure 2
That’s it! Check out the example report (rptDotLeader in download file _GETZ29.EXE) for an example of this technique.
I’d like to be able to create a table of contents, or an index, for a report. There’s no way to know ahead of time on which page any given person will print, and I need to be able to automate the process of creating the table of contents. I know that I could do this in Word, but I’d like to do it all in Access. Is this possible in Access 2? In Access 95?
This is possible, both in Access 2 and in Access 95. It’s not pretty and requires a bit of extra work on your part, but it can be done. To step back a moment, let’s think through some of the issues. First of all, the Table of Contents (TOC) report will need to be a separate report. You can’t gather all the information for this report until you’ve finished printing your entire data set, and by that time, it’s too late to set up something inside the current report to print the TOC. In addition, all reports must be based on a real table or query (that is, you can’t build a report based on information in memory), so you’ll need to create a table to hold the contents of the report, while you’re printing the main report.
Another issue: Access provides events, as it formats and prints each section, to which you can attach code. The OnFormat event property procedure will run every time Access formats a section, and the OnPrint event property procedure will run every time Access actually prints a section. You won’t be able to build the TOC during the Format event, because Access isn’t sure, at that point, whether or not it’s going to be printing the current section on the current page — it’s just laying out the sections and may need to move the section onto the next page in order to print it. If you were building the TOC in the Format event procedure, your output table would possibly contain too many entries as Access tries, and retries, to lay out your report.
The correct place to create the TOC entries is in your section’s Print event: by the time Access runs the code attached to the Print event, it can be sure that the current section will be printed on the current page. Once you’ve made this decision, it’s just a matter of writing the code that will add an item to a table created specifically for this purpose, each time Access prints the section.
Finally, once you’ve built up the table containing one row for each TOC entry, you can print a report based on that table. The example here, shown in Figure 3, prints a two-column report with dot leaders (as explained in the previous answer).
Figure 3
Back to the beginning: Your first step is to write code that creates the TOC table — the table that will contain one row for each TOC entry. Your code will need to create the table if it doesn’t already exist, or clear it out if it does. I find it simplest to just delete the table each time I run the report, and re-create it using a SQL statement. (This technique is simple to code, but will cause your database to grow as you add and delete the table. If you run the report often and don’t care to compact the database regularly, you might want to consider the more cumbersome, but more diskwise-frugal, method of checking to see if the table exists, and if so, deleting the rows.) When you open the report, you’ll need to build the table with a call to the BuildTOCTable(), as shown in the following code. You’ll find this (and the other procedures in this answer) in basBuildTOC. This procedure uses a SQL Data Definition Language (DDL) statement to create the table, and it returns a logical value indicating its success in creating the table. Call this procedure from your report’s Open event, and set the Cancel parameter to True (thereby canceling the report), if you’re unable to create the TOC table:
' In the report's Open event procedure
Private Sub Report_Open(Cancel As Integer)
' If BuildTOCTable() fails, set Cancel to True.
Cancel = Not BuildTOCTable()
End Sub
' In the basBuildTOC module
' Module-level declarations.
Const sacTOCTable = "zstblTOC"
Dim mrst As Recordset
Dim mdb As Database
Function BuildTOCTable() As Boolean
' Build the TOC table. Delete an existing
' table, and build a new one.
On Error GoTo BuildTOCTableErr
Const sacErrNameNotFound = 3265
Dim db As Database
Dim fDeleting As Boolean
' Assume failure.
BuildTOCTable = False
Set db = CurrentDb()
fDeleting = True
' Delete the old table
db.TableDefs.Delete sacTOCTable
fDeleting = False
' Create a new table for the TOC
db.Execute "CREATE TABLE " & sacTOCTable & _
" (ITEM TEXT(255), PAGENUMBER SHORT)"
' Open the recordset, and leave it open.
Set mrst = mdb.OpenRecordset(sacTOCTable, _
dbOpenDynaset, dbAppendOnly) BuildTOCTable = True
BuildTOCTableExit:
Exit Function
BuildTOCTableErr:
Select Case Err
Case sacErrNameNotFound
If fDeleting Then
' Table wasn't there for deleting.
' Just keep going.
Resume Next
Else
MsgBox "Error: " & Error & " (" & Err & ")" _
, , "Build TOC Table"
Resume BuildTOCTableExit
End If
Case Else
MsgBox "Error: " & Error & " (" & Err & ")", _
, "Build TOC Table"
Resume BuildTOCTableExit
End Select
End Function
Once you’ve got your TOC table (zstblTOC in download file _GETZ29.EXE) built, you’re ready to roll. From the Print event of the detail section, you’ll call code that adds a new row to the TOC, each time Access prints the detail section. This code, shown here, uses the previously open recordset based on the TOC table, and adds a new row:
Function AddTOCItem(ByVal varItem As Variant, _
ByVal intPage As Integer) As Integer
' Add item (varItem) to the TOC,
' listing intPage as the page number.
On Error GoTo AddTOCItemErr
' Assume failure
AddTOCItem = False
mrst.AddNew
mrst!Item = varItem
mrst!PageNumber = intPage
mrst.Update
AddTOCItem = True
AddTOCItemExit:
Exit Function
AddTOCItemErr:
Select Case Err
Case Else
MsgBox "Error: " & Error & " (" & Err & ")", _
, "Add TOC Item"
Resume AddTOCItemExit
End Select
End Function
Finally, once you’ve printed your report, you’ll need to close the recordset you’ve been creating as the report was printing. To do so, call the CloseTOC() function from the report’s Close event, as shown here. That procedure will simply close the module-level recordset variable, which you’ve left open while the report was printing to save the time it would take to open and close the recordset for each row:
Function CloseTOC() ' Close the recordset. ' Call this from the report's Close event. mrst.Close End Function
To create the TOC report, create a new report based on zstblTOC. The sample report, rptTOC, (see Figure 3) uses the dot leader technique shown in the previous answer to separate the two fields, Item and PageNumber, from zstblTOC.
Because the code that does the work is called from the detail section’s Print event, the only way to get the code to run to completion is to actually print each page (or preview the pages, one at a time). If you could call the code from the section’s Format event, you could trick Access into formatting the report once before it printed the report (using a reference to the report’s Pages property in the footer, for example). You can’t use the Format event, however, for the reasons stated earlier. For best results, use this technique when you intend to print the report, and then print the TOC report.
How do you use this technique in your own reports? Follow these steps:
1. Import the module basBuildTOC into your application.
2. From your report’s Open event, call the code that builds the TOC table:
Private Sub Report_Open(Cancel As Integer) ' If BuildTOCTable() fails, set Cancel to True. Cancel = Not BuildTOCTable() End Sub
- From your report’s Close event, call the code that will close the open recordset:
Private Sub Report_Close() Call CloseTOC End Sub
- From the Print event of your Detail section, call the code that adds the item to the TOC table:
Private Sub Detail1_Print(Cancel As Integer, _ PrintCount As Integer) ' Disregard the return value. Call AddTOCItem(Me!ContactName, Me.Page) End Sub
- Create a new report based on zstblTOC, showing the items and their page numbers.6. Print the report, or view each page, sequentially, in Print Preview.7. Print the TOC report.This example adds an item to the TOC for each detail row. If you’d rather create a TOC that lists the page number for each group header, then call the appropriate code from the header section’s Print event, rather than from the detail section’s Print event. Don’t forget, however, that to build the index table’s items, you must print each page of the report once. You can either print it to the printer or preview each page sequentially. Without this step, your index table won’t contain the correct rows.
Your download file is called Getz_DotLeaderReport.accdb
