Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Detail and Summary from same Report


You don't have to design two different Reports; one for a 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:

  1. Hiding Report Lines Conditionally
  2. Hiding Records and Group Footer Calculations
  3. Hiding Group Header/Footer and Detail Sections

The Report.MoveLayout Property.

For hiding 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
      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, 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 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 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 Preview or Print. A sample Report Image in Design View is given below.

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.

  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.

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]
       x_opt = 1
   End If
End Sub

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

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    If x_opt = 2 Then
       Cancel = True
       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). Use 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 a Detail 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

Exit Function

IsLoaded = False
Resume IsLoaded_Exit
End Function

The IsLoaded() Function 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 canceled (these Sections are hidden) and shows only the Page Header/Footer, CustomerID Group Sub-Totals, and Report Total in Print Preview or Print.

The Report Option Group.

We will create a Report Option Group in the MainSwitchBoard Form (or you may create a new sample Form as shown below) to set and launch our Report by using one of the options provided for Detail and Summary Views.

  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 the 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 Options 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 the default choice and click Finish.

  7. You may drag and position the Child Label attached to the Options 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. The Demo Run.

  16. 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 show Page Totals on each page of the Report.


1 comment:

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code