Automating Reports

In a typical application, you’ll probably spend 80 to 90 percent of your coding effort in event procedures for your forms. That doesn’t mean that there aren’t many tasks that you can automate on reports. This last section shows you just a few of the possibilities.

Allowing for Used Mailing Labels

Have you ever wanted to create a mailing label report and come up with a way to use up the remaining labels on a partially used page? You can find the answer in the Conrad Systems Contacts sample application (Contacts.accdb). Let’s say you want to send a promotional mailing to all contacts who own the Single User product offering them an upgrade to Multi-User. Open the main switchboard form (frmMain), click Contacts, and then click Search in the Select Contacts pop-up window. Perform a search for all contacts who own the Single User product-you should find eight records in the original sample data. (Click No when the application asks you if you want to see a summary list first.) Click the Print button on the frmContacts form, select Avery 5163 Labels (2"× 4"), ask for the report to include the Displayed Contacts, and specify that your first page of labels is missing three used ones. Your screen should look like Figure 20–27 at this point.

image from book
Figure 20–27: You can request mailing labels and specify that some labels have already been used on the first page.

Click the Print button in the dialog box, and you should see the labels print-but with three blank spaces first to avoid the used ones-as shown in Figure 20–28.

image from book
Figure 20–28: The labels print and avoid the used ones.

You can find some interesting code in the AfterUpdate event of the option group to choose the report type in the FdlgContactPrintOptions form. The code is as follows:

 Private Sub optReportType_AfterUpdate()    ' Figure out whether to show the "used labels" combo    Select Case Me.optReportType      Case 1        ' Show the used labels combo        Me.cmbUsedLabels.Visible = True        ' Hide the number of days option group        Me.optDisplay.Visible = False        ' up to 29 used labels on 5160        Me.cmbUsedLabels.RowSource = "0;1;2;3;4;5;6;7;8;9;10;11;12;13;" & _          "14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29"      Case 2        ' Show the used labels combo        Me.cmbUsedLabels.Visible = True        ' Hide the number of days option group        Me.optDisplay.Visible = False        ' up to 9 used labels on 5163        Me.cmbUsedLabels.RowSource = "0;1;2;3;4;5;6;7;8;9"      Case 3, 4        ' Don't need the combo for Envelopes and contact list        Me.cmbUsedLabels.Visible = False        ' .. or the number of days filter        Me.optDisplay.Visible = False      Case 5, 6        ' Don't need the used labels combo for contact events or products        Me.cmbUsedLabels.Visible = False        ' Do need the day filter        Me.optDisplay.Visible = True    End Select  End Sub

You can have up to 29 used labels when printing on Avery 5160 (1" × 2.625") label paper. You can have up to 9, used labels when printing on Avery 5163 (2" × 4") label paper. The combo box that you can use to indicate the number of used labels has a Value List as its row source type, so the code sets up the appropriate list based on the label type you choose.

However, the real trick to leaving blank spaces on the report is in the query that is the record source for the rptContactLabels5163 report. In the sample database, you can find a table, ztblLabelSpace, that has 30 records, and each record has one field containing the values 1 through 30. The SQL for the query is as follows:

 PARAMETERS [Forms]![fdlgContactPrintOptions]![cmbUsedLabels] Long;  SELECT "" As Contact, "" As CompanyName, "" As Address, "" As CSZ,   Null As ContactID, "" As Zip, "" As LastName, "" As FirstName,   "" As ContactType, "" As WorkCity, "" As WorkStateOrProvince,   "" As HomeCity, "" As HomeStateOrProvince, 0 As Inactive  FROM ztblLabelSpace  WHERE ID <= [Forms]![fdlgContactPrintOptions]![cmbUsedLabels]  UNION ALL  SELECT ([tblContacts].[Title]+" ") & [tblContacts].[FirstName] & " " &   ([tblContacts].[MiddleInit]+". ") & [tblContacts].[LastName] &   (", "+[tblContacts].[Suffix]) AS Contact,   Choose([tblContacts].[DefaultAddress], qryContactDefaultCompany.CompanyName,   Null) As CompanyName,   Choose([tblContacts].[DefaultAddress],[tblContacts].[WorkAddress],  [tblContacts].[HomeAddress]) AS Address,   Choose([tblContacts].[DefaultAddress],[tblContacts].[WorkCity] & ", " &   [tblContacts].[WorkStateOrProvince] & "  " & [tblContacts].[WorkPostalCode],  [tblContacts].[HomeCity] & ", " & [tblContacts].[HomeStateOrProvince]   & "  " & [tblContacts].[HomePostalCode]) AS CSZ,   tblContacts.ContactID,   Choose([tblContacts].[DefaultAddress],[tblContacts].[WorkPostalCode],  [tblContacts].[HomePostalCode]) AS Zip,   tblContacts.LastName, tblContacts.FirstName, tblContacts.ContactType,   tblContacts.WorkCity, tblContacts.WorkStateOrProvince, tblContacts.HomeCity,   tblContacts.HomeStateOrProvince, tblContacts.Inactive  FROM tblContacts   LEFT JOIN qryContactDefaultCompany   ON tblContacts.ContactID = qryContactDefaultCompany.ContactID;

The first SELECT statement (up to the UNION ALL) creates dummy blank columns for each field used by the report and uses the ztblLabelSpace table and a filter on the combo box in the FdlgContactPrintOptions form (Figure 20–27) to return the correct number of blank rows. The query uses a UNION with the actual query that returns contact data to display information on the report.

Because this label report prints a logo and a label control containing the return address, there’s one final bit of code that keeps these from appearing on the blank labels in the rptContactLabels5163 report. The code is as follows:

 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)    ' Don't print the return logo and address if this is a "spacer" record    If IsNull(Me.ContactID) Then      Me.imgConrad Systems.Visible = False      Me.lblRtnAddr.Visible = False    Else      Me.imgConrad Systems.Visible = True      Me.lblRtnAddr.Visible = True    End If  End Sub

The Format event of the Detail section depends on the fact that the ContactID in the “spacer” rows is Null. When printing a blank row for spacing, the code hides the logo and the return address label.

Drawing on a Report

When you want to draw a border around a report print area, sometimes you’ll need to write some code to ask Access to draw lines or a border after placing the data on the page. This is especially true if one or more controls on the report can grow to accommodate a large amount of data.

We used the Report Wizard to create the basic rptContacts report using the Justified format. (We customized the report after the wizard finished.) The wizard created a fairly decent layout with a border around all the fields, but it didn’t make the text box to display notes large enough to display the text for all contacts. Figure 20–29 shows you the report displaying John’s contact record from the database. You can see that the notes about John are cut off at the bottom.

image from book
Figure 20–29: This report uses a border around the data, but one of the text boxes isn’t large enough to display all the text.

It’s simple enough to change the Can Grow property of the text box to Yes to allow it to expand, but the rectangle control used to draw the border around all the text doesn’t also have a Can Grow property. The solution is to remove the rectangle and use the Line method of the Report object in the report’s Format event of the Detail section to get the job done. Below is the code you can find in this event procedure in the rptContacts-ExpandNotes report.

 Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)  Dim sngX1 As Single, sngY1 As Single  Dim sngX2 As Single, sngY2 As Single, lngColor As Long    ' Set coordinates    sngX1 = 120    sngY1 = 120    sngX2 = 9120    ' Adjust the height if Notes has expanded    sngY2 = 7680 + (Me.Notes.Height - 2565)    ' Draw the big box around the data    ' Set width of the line    Me.DrawWidth = 8    ' Draw the rectangle around the expanded fields    Me.Line Step(sngX1, sngY1)-Step(sngX2, sngY2), RGB(0, 0, 197), B  End Sub

The Line method accepts three parameters:

  1. The upper-left and lower-right corners of the line or box you want to draw, expressed in twips. (There are 1440 twips per inch.) Include the Step keyword to indicate that the coordinates are relative to the current graphics position, which always starts at 0, 0. When you use Step for the second coordinate, you provide values relative to those you specified in the first set of coordinates.

  2. The color you want to draw the line or box, expressed as a red-green-blue (RGB) value. (The RGB function is handy for this.)

  3. An indicator to ask for a line, a rectangle, or a filled rectangle. No indicator draws a line. Include the letter B, to ask for a rectangle. Add the letter)F to ask for a filled rectangle.

Before you call the Line method, you can set the DrawWidth property of the report to set the width of the line. (The default width is in pixels.)

The only tricky part is figuring out the coordinates. On the original report, the rectangle starts at 0.0833 inch in from the left and down from the top, so multiplying that value by 1440 twips per inch gave us the starting values of 120 down from the top and 120 in from the left edge. The width of the rectangle needs to be about 6.3333 inches, so the relative coordinate for the upper-right corner is 6.3333 × 1440, or about 9,120 twips. The height of the rectangle needs to be at least 5.3333 inches, or about 7,680 twips, and the height needs to be adjusted for the amount that the Notes text box expands. The Notes text box is designed to be a minimum of 1.7813 inches high, or 2,565 twips, so subtracting 2,565 from the actual height of the Notes text box when it’s formatted (the Height property is also in twips) gives you the amount you need to add to the original height of the rectangle. Trust us, we didn’t get it right on the first try!

If you open the rptContactsExpandNotes report and move to John’s record on page 18, you’ll see that the rectangle now expands nicely to fit around the Notes text box that grew to display all the text in John’s record. Figure 20–30 shows you the report with the rectangle drawn by the code behind the report.

image from book
Figure 20–30: Code in the rptContactsExpandNotes report draws a custom rectangle around expanded text.

Dynamically Filtering a Report When It Opens

The two most common ways to open a report filtered to print specific records are

  • Use the WhereCondition parameter with the DoCmd.OpenReport method (usually in code in an event procedure behind a form) to specify a filter.

  • Base the report on a parameter query that prompts the user for the filter values or references control values on an open form.

In some cases, you might design a report that you intend to open from several locations in your application, and you can’t guarantee that the form to provide filter values will always be open. Or, you might have multiple reports that need the same filter criteria, and you don’t want to have to design a separate filter form for each report. To solve these problems, you can add code to the report to have it open its own filter dialog box from the report’s Open event procedure. Let’s go back to the Housing Reservations application (Housing.accdb) to take a look at a report that uses this technique.

In the Housing Reservations application, both the rptFacilityOccupancy report and the rptFacilityRevenueChart report depend on a single form, fdlgReportDateRange, to provide a starting and ending date for the report. To see the rptFacilityOccupancy report, you can start the application by opening the frmSplash form, sign on as an administrator (Conrad, Jeff, Richins, Jack S., Schare, Gary, or Viescas, John L.), click the Reports button on the main switchboard, and then click the Reservations button in the Facilities category on the Reports switchboard. (You can also simply open the report directly from the Navigation Pane.) When you open the report, you’ll see a dialog box prompting you for the dates you want as shown in Figure 20–31.

image from book
Figure 20–31: A parameter dialog box opens from the report that you asked to view.

Unless you’ve reloaded the sample data, the database contains reservations from February 28, 2007 through September 26, 2007, so asking for a report for April, May, and June should work nicely. Enter the dates you want and click Go to see the report, as shown in Figure 20–32.

image from book
Figure 20–32: The Facility Occupancy report uses a shared filter dialog box to let you specify a date range.

The report has a parameter query in its record source, and the parameters point to the from and to dates on the fdlgReportDateRange form that you see in Figure 20–31. However, the code behind the Reservations button on the Reports switchboard opens the report unfiltered. It’s the code in the report’s Open event procedure that opens the dialog box so that the query in the record source can find the parameters it needs. The code is as follows:

 Private Sub Report_Open(Cancel As Integer)    ' Open the date range dialog    ' .. report record source is filtered on this!    DoCmd.OpenForm "fdlgReportDateRange", WindowMode:=acDialog  End Sub

This works because a Report object doesn’t attempt to open the record source for the report until after the code in the Open event finishes. So, you can place code in the Open event to dynamically change the record source of the report or, as in this example, open a form in Dialog mode to wait until that form closes or hides itself. The code behind the dialog form, fdlgReportDateRange, is as follows:

 Private Sub Form_Load()   ' If passed a parameter, reset defaults to last quarter   If Not IsNothing(Me.OpenArgs) Then     ' Set the start default to first day of previous quarter     Me.txtFromDate.DefaultValue = "#" & _       DateSerial(Year(Date), ((Month(Date) - 1) \ 3) * 3 - 2, 1) & "#"     ' Set the end default to last day of previous quarter     Me.txtToDate.DefaultValue = "#" & _       DateSerial(Year(Date), ((Month(Date) - 1) \ 3) * 3 + 1, 1) & "#"   End If End Sub Private Sub cmdGo_Click()   ' Hide me so report can continue   Me.Visible = False End Sub

The code in the form’s Load event checks to see if the report that is opening the form has passed a parameter in the OpenArgs property. If so, the code resets the default values for the two date text boxes to the start and end date of the previous quarter. If you look at the code behind the rptFacilityRevenueChart report, you’ll find that this report asks for the different default values. But it’s the code in the Click event of the Go command button that gets things rolling. The code behind the form responds to your clicking the Go button to hide itself so that the report can continue. It can’t close because the record source of the report depends on the two date parameters. As noted earlier, hiding this form opened in Dialog mode allows the code in the Open event of the report to finish, which lets the report finally load its record source. As you might suspect, there’s code in the Close event of the report to close the parameter form when you close the report or the report finishes printing.

As you’ve seen in this chapter, Visual Basic is an incredibly powerful language, and the tasks you can accomplish with it are limited only by your imagination. In the next chapter of this book, you’ll learn how to set startup properties, create custom menus, and build a main switchboard form for your application.

Microsoft Office Access 2007 Inside Out
MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
ISBN: 0735623252
EAN: 2147483647
Year: 2007
Pages: 234

Similar book on Amazon
Access 2007: The Missing Manual
Access 2007: The Missing Manual
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoftu00ae Office Access(TM) 2007 Step by Step (Step By Step (Microsoft))
Microsoft Office Access 2007 Forms, Reports, and Queries
Microsoft Office Access 2007 Forms, Reports, and Queries
Alison Balter's Mastering Microsoft Office Access 2007 Development
Alison Balter's Mastering Microsoft Office Access 2007 Development © 2008-2017.
If you may any questions please contact us: