Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 3.15 Customize a Report's Grouping and Sorting at Runtime

3.15.1 Problem

You have a report that has several
different grouping and sorting fields that you need to rearrange
every time you run the report. To do this, you've
created five or six different versions of the same report, changing
only the order of the fields and which fields are sorted or grouped.
This is a maintenance nightmare, especially when you want to change
some aspect of the report, which means having to change all the
variants of this same report. Is there any easier way to do this in
Access?

3.15.2 Solution

You can manipulate most aspects of a report's design
using VBA code. This solution shows you how to programmatically open
a report in design mode and manipulate several properties of controls
and groups. Using this technique and a driving form, you can create a
single report that can be customized using different sorting and
grouping fields every time it is run.

Load

03-15.MDB and open frm_rptCompaniesSetup,
which is shown in Figure 3-36.

Figure 3-36. The frm_rptCompaniesSetup form is used to set up the rptCompanies report

Select a grouping field and zero, one, two, or three other fields for
the report (any or all of which can be sorted). When
you're done, press the Preview or Print button and a
report matching the chosen sorting/grouping fields will be previewed
or printed for you. A sample report using the settings from Figure 3-36 is shown in Figure 3-37.

Figure 3-37. The rptCompanies report is customized every time it is run

To create a customizable report of your own, follow these steps:

1. Identify the table or query on which the report will be based. In our
   example, the report is based on the tblCompanies table. Decide which
   of the fields in this table or query you wish to allow to be
   selected, grouped, or sorted. In the sample database, we decided to
   use all of the fields from tblCompanies.

2. Create a table
   with one field, ReportFieldName, with a data type of Text. Make this
   field the primary key of the table. Save the tablein the
   example, we named it zstbl_rptCompaniesFieldsand switch to
   datasheet view, adding a record for each field identified in Step 1.

3. Create a new unbound form. Add one
   unbound combo box for each field you want to be able to customize at
   runtime. For example, in the frm_rptCompaniesSetup form, we allow for
   one grouping field and up to three sorting fields (see Figure 3-36). The names of the combo box fields and their
   RowSource properties are listed in Table 3-13. All
   other properties are set to the default values.

   Change
   "zstbl_rptCompaniesFields" to the
   name of the table from Step 2. Change
   "frm_rptCompaniesSetup" to the name
   of your form. Create additional combo boxes as needed, following the
   pattern of Name and RowSource properties from Table 3-13.

4. For all but the last combo box
   created in Step 3, create an event procedure attached to the
   AfterUpdate event of the control containing code similar to the
   following:

   Private Sub cboField1_AfterUpdate( )
   Me.cboField2.Requery
   Call FixUpCombos(Me.cboField1)
   End Sub

   Replace "cboField1" with the name
   of the first combo box and
   "cboField2" with the name of the
   next combo box. Add the following code to the end of the first combo
   box's event procedure:

   ' Enable the buttons once you've chosen the group field.
   If Not IsNull(Me.cboField0) Then
   Me.cmdPrint.Enabled = True
   Me.cmdPreview.Enabled = True
   End If

   Don't create an AfterUpdate
   event procedure for the last combo box.

5. Add one option group control
   alongside each combo box, as listed in Table 3-14.
   If you have more than four fields, add additional option groups,
   following the same naming pattern and assigning default values of 1
   to each additional option group.

   For each option group, add three option buttons, as listed in Table 3-15. The names of the option buttons
   don't matter.

6. Add a command button named cmdPreview with the caption
   "Preview" to the form. Attach the
   following code to its AfterUpdate event:

   Private Sub cmdPreview_Click( )
   Call HandlePrinting(acbcReport, acPreview)
   End Sub

7. Add a command button named cmdPrint with the caption
   "Print" to the form. Attach the
   following code to its AfterUpdate event:

   Private Sub cmdPrint_Click( )
   Call HandlePrinting(acbcReport, acNormal)
   End Sub

8. Edit the form's module
   (click on the Code button on the Report Design toolbar or choose the
   View Code menu option) and enter the following lines of
   code in the module's declarations section:

   Const acbcReport As String = "rptCompanies"
   Const acbcTemp As String = "rptTemp"
   Const acbcNoSort = 1
   Const acbcMaxGroupFields = 1
   Const acbcMaxSortFields = 3

9. With the form's module still open, add the following
   two procedures to the module (or copy them into your
   form's module from the sample database):

   Private Sub FixUpCombos(ctlCalling As Control)
   Dim intIndex As Integer
   Dim intI As Integer
   ' Grab the last character of the calling
   ' control's name and convert to an integer
   intIndex = CInt(Right(ctlCalling.Name, 1))
   ' Enable the next control if and only if the
   ' value of the calling control is non-null
   If intIndex < acbcMaxSortFields Then
   With Me("cboField" & intIndex + 1)
   .Value = Null
   .Enabled = (Not IsNull(ctlCalling))
   End With
   Me("grpSort" & intIndex + 1).Enabled = (Not IsNull(ctlCalling))
   End If
   ' Disable all controls after the next one
   If intIndex < acbcMaxSortFields - 1 Then
   For intI = intIndex + 2 To acbcMaxSortFields
   With Me("cboField" & intI)
   .Value = Null
   .Enabled = False
   End With
   With Me("grpSort" & intI)
   .Value = acbcNoSort
   .Enabled = False
   End With
   Next intI
   End If
   End Sub
   Public Sub HandlePrinting(strReport As String, ByVal intPrintOption As Integer)
   Dim intI As Integer
   Dim intFieldCnt As Integer
   Dim avarFields(0 To acbcMaxSortFields) As Variant
   Dim aintSorts(0 To acbcMaxSortFields) As Integer
   Dim rpt As Report
   Dim varGroupLevel As Variant
   On Error GoTo HandleErr
   DoCmd.Hourglass True
   ' Count up the non-null grouping/sorting fields
   ' and the sort property fields and store them in
   ' two arrays
   intFieldCnt = -1
   For intI = 0 To acbcMaxSortFields
   If Not IsNull(Me("cboField" & intI)) Then
   intFieldCnt = intFieldCnt + 1
   avarFields(intFieldCnt) = Me("cboField" & intI)
   aintSorts(intFieldCnt) = Me("grpSort" & intI)
   End If
   Next intI
   ' Delete old temp copy of report
   On Error Resume Next
   DoCmd.DeleteObject acReport, acbcTemp
   On Error GoTo HandleErr
   DoCmd.CopyObject , acbcTemp, acReport, strReport
   ' Turn off screen updating and open the report in
   ' design mode where it will be manipulated
   Application.Echo False
   DoCmd.OpenReport acbcTemp, View:=acDesign
   ' Set up a report object to point to the report
   Set rpt = Reports(acbcTemp)
   ' Always have a single grouping field.
   ' First set the properties of the group
   rpt.GroupLevel(0).ControlSource = avarFields(0)
   rpt.GroupLevel(0).SortOrder = aintSorts(0)
   ' Set the first label and text box to match
   ' the grouping properties
   rpt("txtField0").ControlSource = avarFields(0)
   rpt("lblField0").Caption = avarFields(0)
   ' Already used GroupLevel(0) for the grouping field,
   ' so now work through the remaining fields
   For intI = 1 To intFieldCnt
   ' Set the text box to be visible
   ' and bind to the chosen field
   With rpt("txtField" & intI)
   .Visible = True
   .ControlSource = avarFields(intI)
   End With
   ' Set the label to be visible with its caption
   ' equal to the name of the field
   With rpt("lblField" & intI)
   .Visible = True
   .Caption = avarFields(intI)
   End With
   ' Now create each sorting field group
   If aintSorts(intI) <> acbcNoSort Then
   varGroupLevel = CreateGroupLevel(rpt.Name, _
   avarFields(intI), False, False)
   rpt.GroupLevel(varGroupLevel).SortOrder = aintSorts(intI)
   End If
   Next intI
   ' Make any unneeded fields invisible
   For intI = intFieldCnt + 1 To acbcMaxSortFields
   rpt("txtField" & intI).Visible = False
   rpt("lblField" & intI).Visible = False
   Next intI
   ' Save changes to the new report, then open the temporary report:
   DoCmd.Save acReport, acbcTemp
   DoCmd.OpenReport acbcTemp, View:=intPrintOption
   ExitHere:
   DoCmd.Hourglass False
   Application.Echo True
   Exit Sub
   HandleErr:
   Resume ExitHere
   End Sub

   Save the form. The complete frm_rptCompaniesSetup sample form is
   shown, in design view, in Figure 3-38. Close the
   form.

Figure 3-38. The sample form in design view

10. Create a new report. Add one
    sorting/grouping field to the report. The actual field you choose
    doesn't matter because the code behind
    frm_rptCompaniesSetup will change the field name. What is important
    is that you set the GroupHeader and GroupFooter properties to Yes
    (which makes it a grouping field). Don't add any
    additional sorting fields.

11. Add a label control for each combo
    box field from frm_rptCompaniesSetup to the group header section of
    the report. Make all the labels the same size and give them names in
    the following style: lblField0, lblField1, and so on.

12. Add an unbound text box control for
    each combo box field from frm_rptCompaniesSetup to the detail section
    of the report. These fields should line up under the labels added in
    Step 13, should all be the same dimensions, and should have names
    like txtField0, txtField1, and so on.

13. Add any page and report headers and footers. Save the report and
    close it. The completed sample report is shown in Figure 3-39 in design view.

Figure 3-39. The rptCompanies report in design view

3.15.3 Discussion

The zstbl_rptCompaniesFields table holds the names of all the
possible fields in the report. This table supplies the row source for
the combo boxes on the driving form. Each record in this table
corresponds to one field that may be selected, sorted, or grouped. In
the sample database, we used all five fields from tblCompanies.

Most of the work in this solution is done
by the

driving form . This form
(frm_rptCompaniesSetup, in the sample database) drives the
report-customization process. For the person running the report to be
able to customize it, you must provide some user interface (UI)
mechanism for picking and choosing fields. The combo boxes and option
groups provide this mechanism.

Many of the solution steps (Steps 3 through 5 and the

FixUpCombos subroutine in Step 9) are used to
make the UI for the driving form as easy to use and as foolproof as
possible. For example, we created RowSource properties (listed in
Table 3-15) that make it difficult for the user to
select the same grouping/sorting field twice by refining the combo
box list for each field that eliminates any fields already chosen
from the list.

The RowSource properties make it
difficult to select the same field twice, but the code in the

FixUpCombos procedure makes doing so next to
impossible. When the form first opens, all of the controls except the
first combo box and the first option group are disabled. After you
have selected a field from a combo box, the code enables the next
combo box/option group while keeping controls that come after that
combo box/option group disabled. This takes care of forward movement.
However, the user can always back up and change a combo box field out
of orderhence, in addition to disabling the controls, the code
also nulls out any values that may have been entered into subsequent
combo boxes.

When the cmdPrint or cmdPreview buttons are pressed, the

HandlePrinting subroutine is called. This
subroutine takes all the data entered on the form, opens the report
in design mode, and customizes it prior to printing the form to the
screen or printer.

HandlePrinting begins by counting up the
non-null combo box controls on the form and storing their values and
the values of the associated option groups into two arrays:

intFieldCnt = -1
For intI = 0 To acbcMaxSortFields
If Not IsNull(Me("cboField" & intI)) Then
intFieldCnt = intFieldCnt + 1
avarFields(intFieldCnt) = Me("cboField" & intI)
aintSorts(intFieldCnt) = Me("grpSort" & intI)
End If
Next intI

Next, the code opens the report in design view (after suspending
most, but not all, screen updating) and adjusts the properties of the
first field, which makes up the one and only grouping field:

' Always have a single grouping field. First set the properties
' of the group.
rpt.GroupLevel(0).ControlSource = avarFields(0)
rpt.GroupLevel(0).SortOrder = aintSorts(0)
' Set the first label and text box to match the grouping properties.
rpt("txtField0").ControlSource = avarFields(0)
rpt("lblField0").Caption = avarFields(0)

The next stretch of code iterates
through the remaining fields, which are all sorting (or nonsorting
detail) fields. First, the unbound text box controls are made visible
and their control sources are set to the names of the fields selected
from the form. Next, the labels are made visible and their captions
are set to match the text boxes. The

CreateGroupLevel function is then called to
create any and all sorting fields based on the selection from the
option groups on the form. (The last two parameters of this function
tell Access whether you want a header or a footer. Because this code
is creating sorting fields only, both of these parameters are set to
False.) This chunk of

HandlePrinting is shown here:

For intI = 1 To intFieldCnt
' Set the text box to be visible and bind it to the chosen field.
With rpt("txtField" & intI)
.Visible = True
.ControlSource = avarFields(intI)
End With
' Set the label to be visible with its caption equal to
' the name of the field.
With rpt("lblField" & intI)
.Visible = True
.Caption = avarFields(intI)
End With
' Now create each sorting field group.
If aintSorts(intI) <> acbcNoSort Then
varGroupLevel = CreateGroupLevel(rpt.Name, _
avarFields(intI), False, False)
rpt.GroupLevel(varGroupLevel).SortOrder = aintSorts(intI)
End If
Next intI

Next, any unneeded fields are made invisible:

For intI = intFieldCnt + 1 To acbcMaxSortFields
rpt("txtField" & intI).Visible = False
rpt("lblField" & intI).Visible = False
Next intI

The code creates a temporary copy of the report, earlier in the
procedure:

On Error Resume Next
DoCmd.DeleteObject acReport, acbcTemp
On Error GoTo HandleErr
DoCmd.CopyObject , acbcTemp, acReport, strReport

This is necessary because the code makes design-time changes to the
report. Making a copy eliminates the chance that the user will save
the modified report over the original, which could mess things up the
next time the report is run. The code completes its work by saving
the new report and opening the report in the requested mode:

' Save changes to the new report, then open the temporary report:
DoCmd.Save acReport, acbcTemp
DoCmd.OpenReport acbcTemp, View:=intPrintOption

Making a temporary copy of the
report eliminates the possibility of the original report being left
in a state that makes it unusable the next time the report is run.
This is important because there is no programmatic way to remove sort
fieldsyou can't make a report that has been
saved with two sort fields into a report with one sort field. If the
user is allowed to save a modified version of the report, this is
exactly what might happen. Therefore, we made the decision to use a
temporary copy of the report (but only after trying numerous other
workarounds).

The sample report and accompanying code
assume that you want only one grouping field. We did this to simplify
the example, but you could extend it by including code to make
additional grouping fields (just like the code that now makes the
sorting fields). If you do this, you'll have to deal
with creating controls and placing them in the headers of the groups.
You can create controls using the

CreateReportControl function, which is described
in the Access online help.

Any technique that relies on programmatically making changes to a
report (or a form) while it's open in design view
won't work in an Access MDE or ADE, where design
changes aren't permitted. In those cases, however,
you can use a modified version of this solution. In a
report's Open event, you can't add
new grouping and sorting levels, but you can change the control
sources of existing ones. So, as long as you have enough grouping and
sorting levels in the saved report, you can modify them at runtime
rather than at design time with code like this:

rpt.GroupLevel(0).ControlSource = avarFields(0)

If necessary, you can create
"dummy" grouping levels in your
report, using a control source like =1, to make it
possible to avoid having to open the report in design view.