Access Cookbook - Ken Getz [80]
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
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.
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.
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.
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
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