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.
- 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 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.
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 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.
- 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 selected state, if not then select it.
- Select the Option Group Tool from the ToolBox.
- Draw a rectangle on the Form as shown above. The Option Group Wizard will open up.
- Type Detail Report, press TAB Key and Type Summary Report for two options and Click Next.
- Accept Detail Report as default choice and Click Finish.
- 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.
- 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.
- 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.