Detail and Summary from same Report

You don't have to design two different Reports; one for Detail Listing of records with Group-wise Totals and another one for Group-wise Totals alone. We can play a small trick to get both outputs from the same Report depending on the User's choice.


Recommended reading before proceeding with this topic:

Hiding Report Lines Conditionally
Hiding Records and Group Footer Calculations
Hiding Group Header/Footer and Detail Sections

For hiding of Report Lines conditionally there are other methods too. 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 the three Report Property Values shown above in different combinations to get the same result. We have already explored the PrtDevMode, PrtMIP Report Properties and learned how to change Paper Size and Page Orientation, Margin Settings and Column Settings through 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 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. We will use a copy of last week's sample Report Order_Details2 for this experiment. If you already have a Report with Group-wise Sub-Totals you may use that too. If you are using your own Report then ensure that you are displaying the Group Item Value or Description along with the label Sub-Total at the left side of the sub-total value control in the Group Footer.


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






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

1. Make a Copy of the Order_Details2 Report and Paste it with the name Order_Details3.


2. Open the Report in Design View.


3. 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.



4. Write the same expression in the Report Footer empty Text Box to take Quantity Report level Total.



5. Display the Code Module of the Report (View - ->Code).


6. Delete the existing VBA Code from the Code Module (Class Module is the correct Term for Report and Form Modules).



7. Copy and Paste the following VBA Code into the Module and Save the Report.

Option Compare Database
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 above Code is not that complicated to understand. We are reading the Report Option settings from the MainSwitchBoard in the Report_Open() Event Procedure and loading that value into a Global Variable x_opt (defined at the top of the Module below the Global default declaration Option Compare Database). Using IsLoaded() Function to check whether the MainSwitchboard Form is open or not before attempting to read the Value from the Option Group control from the Form otherwise the Report is open normally for Detail print preview.


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() Functions checks through the list of all Open Forms looking for the MainSwitchBoard Form and if it is open then returns TRUE otherwise FALSE.

If the Report output option setting on the Main Switchboard is 1 then the Report will print normally with all Report Sections including the Group Header/Footer Sections.


If the option setting is 2 then the Format Event of Report CustomerID Group Header and Detail Section is cancelled (these Sections are hidden) and shows only the Page Header/Footer, CustomerID Group Sub-Totals and Report Total in Print Preview or Print.


We will create a Report Option Group on the MainSwitchBoard Form (or you may create a new sample Form as shown below) to set and launch our Report from there for Detail and Summary Options.

1. Open a new Form or your Main Switchboard (Control Screen) Form in Design View.


2. Check whether the Control Wizard (with the magic wand icon) on the ToolBox is in selected state, if not then select it.


3. Select the Option Group Tool from the ToolBox.



4. Draw a rectangle on the Form as shown above. The Option Group Wizard will open up.


5. Type Detail Report, press TAB Key and Type Summary Report for two options and Click Next.



6. Accept Detail Report as default choice and Click Finish.



7. You may drag and position the Child Label attached to the Option Group as shown on the design above and change the Label Caption as Report Options.



8. Click on the Option Group outer frame to select it and display the Property Sheet (View - -> Properties).



9. Change the Name Property Value to Opt. (no dot at the end)



10. Select the Command Button Tool from the ToolBox and draw a Command Button below the Option Group Control.



11. Display the Property Sheet of the Command Button.



12. Change the Caption Value to Report Preview.



13. 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.



14. Save the MainSwitchBoard Form.



15. Open it in normal view. Click on the Report Preview Command Button to open the Report after setting 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 Option.


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

---

InputBox and Simple Menus
Change Form Modes On User Profile
Positioning POPUP Forms
Synchronized Floating Popup Form
Forms and Custom Properties