Detail and Summary from same Report ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

You don’t need to design two separate reports—one for a detailed listing of records with group-wise totals and another for group-wise totals alone. We can use a simple trick to generate both outputs from the same report, depending on the user’s choice.

Recommended reading before proceeding with this topic:

The `Report.MoveLayout` Property.

There are different methods for hiding Report Lines conditionally. For example, the following VBA Code (instead of the earlier simple method we have tried) can give you the same result for hiding Detail Section Report Lines:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  If [OrderID] = 10280 Or [OrderID] = 10297 Then
      Report.MoveLayout = False
      Report.NextRecord = True
      Report.PrintSection = False
  Else
      Report.MoveLayout = True
     Report.NextRecord = True
      Report.PrintSection = True
  End If

End Sub

We must set all three Report-Property Values shown above in different combinations to get the same result. We have already explored the PrtDevMode and PrtMIP Report Properties, and learned how to change Paper Size and Page Orientation, Margin Settings, and Column Settings through the Program, while previewing or sending the Report to a Network Printer.

If you would like to know more details about the above Report-Property settings, you may search the VBA Help Documents. You can get the Help Document related to this topic quickly if you open any Code Module and Type 'Report.MoveLayout' and press F1 while the cursor is next to the Text or in the Text.

We will continue with our new trick. For this experiment, we will use a copy of last week’s sample report, Order_Details2. If you already have a report with group-wise sub-totals, you can use that instead. If you are using your own report, make sure that the group item value or description is displayed along with the “Sub-Total” label on the left side of the sub-total value control in the Group Footer.

Sample Report Design View.

We will hide the Group Header and Detail Sections when the User opens the Report for Summary Print Preview or Print. A sample Report Image in Design View is given below.

Right-click to open Large Image in New Tab/Window.

We will replace the Code written for an earlier example with a new Program, so it is better to make a copy of that Report for our trial run now.

Make a Copy of the Order_Details2 Report and paste it with the name Order_Details3.
Open the Report in Design View.
Write the Expression =Sum([Quantity]) in the empty Text Box (we have removed this for our earlier example) in the CustomerID Group Footer below the Quantity field in the Detail Section.
Write the same expression in the Report Footer empty Text Box to take the Quantity Report level Total.
Display the Code Module of the Report (View ->Code).
Delete the existing VBA Code from the Code Module (Class Module is the correct term for Report and Form Modules).
Copy and Paste the following VBA Code into the Module and Save the Report.

The Report Class Module Code.

Option Compare Database
'Global declarations
Dim x_opt As Integer

Private Sub Report_Open(Cancel As Integer)
   If IsLoaded("MainSwitchBoard") Then
       x_opt = Forms![MainSwitchBoard]![Opt]
   Else
       x_opt = 1
   End If
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If x_opt = 2 Then
        Cancel = True
    Else
        Cancel = False
    End If
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    If x_opt = 2 Then
       Cancel = True
    Else
       Cancel = False
    End If
End Sub

As you can see, the code above is not hard to understand. We read the report option settings from the MainSwitchBoard in the Report_Open() event procedure and store the value in the global variable x_opt (defined at the top of the module, below the Global default declaration Option Compare Database). Use the IsLoaded() function to check whether the MainSwitchBoard form is open before attempting to read the value from the option group control on the form. If it isn’t open, the report opens normally for a detailed print preview.

The IsLoaded() Function.

Copy the following Code for the IsLoaded() Function and paste it into a Global Module (Standard Module) of your Project and save it:

Public Function IsLoaded(ByVal strForm As String) As Boolean
'---------------------------------------------------------
'Checks through the Forms Collection and if the Form is
'loaded in Memory then Returns TRUE else FALSE
'---------------------------------------------------------
Dim varFrm As Form

On Error GoTo IsLoaded_Err

IsLoaded = False

For Each varFrm In Forms
  If varFrm.Name = strForm Then
   IsLoaded = True
  End If
Next

IsLoaded_Exit:
Exit Function

IsLoaded_Err:
IsLoaded = False
Resume IsLoaded_Exit
End Function

The IsLoaded() function checks the list of all open forms for the MainSwitchBoard form. If the form is open, it returns the result: TRUE; otherwise, it returns FALSE.

If the report output option on the MainSwitchBoard is set to 1, the report prints normally, with all sections, the Group Header, and Footer sections.

If the option setting is 2, the Format event of the CustomerID Group Header and the Detail section is canceled (these sections are hidden), so only the Page Header/Footer, CustomerID Group Sub-Totals, and Report Total are shown in Print Preview or when printing.

The Report Option Group

We will create a Report Option Group on the MainSwitchBoard form (or you design a new sample form as shown below) to set and launch our report, using one of the options provided for Detail and Summary views.

Right-click to open Large Image in New Tab/Window.

Open a new Form or your Main Switchboard (Control Screen) Form in Design View.
Check whether the Control Wizard (with the magic wand icon) on the ToolBox is in the selected state; if not, select it.
Select the Option Group Tool from the Toolbox.
Draw a rectangle on the Form as shown above. The Options Group Wizard will open up.
Type Detail Report, press TAB Key, and Type Summary Report for two options, and click Next.
Accept the Detail Report as the default choice and click Finish.
Drag and position the Child Label attached to the Options Group as shown in the design view above, and modify the Label Caption to Report Options.

Click on the Option Group outer frame to select it and display the Property Sheet (View -> Properties).
Change the Name Property Value to Opt. (no dot at the end)
Select the Command Button Tool from the Toolbox and draw a Command Button below the Option Group Control.
Display the Property Sheet of the Command Button.
Change the Caption Value to Report Preview.
Set the Hyperlink SubAddress Property value to Report Order_Details3. Don't forget to leave a space between the word Report and your Report Name.
Save the MainSwitchBoard Form.

The Demo Run.

Open it in a normal view. Click on the Report Preview Command Button to open the Report after setting the Detail Report or Summary Report Option in the Option Group Control.

NB: Don't forget to close the earlier Report Preview before attempting to open it for different Options.

Next, we will explore how to prepare and display Page Totals on each page of the Report.

LEARN MS-ACCESS TIPS AND TRICKS