Access Cookbook - Ken Getz [81]
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 order—hence, 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: