Hiding Report Lines Conditionally

Introduction.

Hiding report lines or report sections conditionally at runtime may not be an everyday requirement. However, when it becomes necessary, it’s interesting to know how to do it with the help of VBA programs. One feature I particularly like about MS Access reports is the ability to write programs directly in the report itself—especially for highlighting critical information so that users’ attention is drawn to specific details. You can see an interesting example in an earlier post titled Highlighting Reports.

Normally, to suppress something from printing on a report, we would use a query to filter out unwanted items from the source data before previewing or printing. Frankly, using a query is the best method compared to what I’m showing here. To achieve the same result on the report without filtering the source data, we need to use a few tricks—after all, this is all about Tips & Tricks, right?

If you enjoy working with MS Access programs, read on. The code itself is not complicated; a few simple lines are enough, and even a VBA beginner can understand them easily.

It’s enjoyable to do something different, not only to break the monotony of repeating the same tasks but also to gain better insight into doing things more efficiently next time.

Design a Sample Report.

We will create a sample Report quickly to try out this Trick.

1. Import the Orders and Employees tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.

   We won’t be using the Employees table directly in the report. Without the Employees table in the database, only the Employee IDs will be displayed when designing the report using the Orders table. There is a reference to the Employees table in the EmployeeID field of the Orders table (for example, the combo box uses the Employees table).

   To create the report:

   1. Click on the Orders table and select Report from the Insert menu.

   2. Choose Report Wizard from the displayed list and click OK.

   3. From the Available Fields list, select the following data fields and move them one by one to the report (fields list continues…).

      Selected Fields List:

      • OrderID
      • CustomerID
      • EmployeeID
      • OrderDate
      • RequiredDate

   4. Click Next, select CustomerID as Grouping Level, and move it to the right. Click Next Command Button.

   5. Select OrderID for Sorting in the first Sort Control and click Finish to create the sample Report.

      An image of the sample Report is given below:

      

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

   6. Open the Report in Print Preview and check the Order Numbers appearing under the first three Customer Groups on the Report.

      We will attempt to hide three different Orders (Order IDs 10702, 10625 & 10573 from ALFKI, ANATR & ANTON Customer#39 List, respectively) from appearing on the Report with the following simple lines of Code:

      Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      If [OrderID] = 10702 Or [OrderID] = 10625 Or [OrderID] = 10573 Then
            Report.Section(acDetail).Visible = False
      Else
         Report.Section(acDetail).Visible = True
      End If
      End Sub

      The OnFormat() Event Procedure

      If you look at the above code, you can see that the code runs under the On Format Event Procedure in the Detail Section of the Report.

   7. Open your Report in Design View and display the Code Module (View -> Code).

   8. Copy and Paste the above Code into the Report Module.

   9. Select Close and Return to Microsoft Office Access from the File Menu to close the VBA Window and return to the Design View of the Report.

      Or you can press Alt+F11 to Toggle between the Database Window and the VBA Window. Visit the Page Keyboard Shortcuts to learn more about Keyboard Shortcuts.

   10. Click on the Detail Section of the Report and display the Property Sheet (View -> Properties).

   11. Check the On Format Event Property, and you can see that the entry [Event Procedure] is appearing there, indicating that the Code that we have pasted Runs on the Format Event of the Report.

       There are two more Events associated with the Report's Printing or Previewing action: Print Event and Retreat Event.

       The Report Formatting Passes

       MS Access makes two passes over a report before it is actually displayed or printed. The first, the Format pass, lays out the contents of each report section and performs any necessary calculations for summary information (we’ll cover this in more detail in forthcoming articles). The second pass, the Print pass, prints the report’s contents before previewing or sending it to the printer.

       The Retreat event occurs when you move the page preview to an earlier page. During this event, the Format action runs a second time for the retreated page and the FormatCount parameter. 

       Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
       

       is incremented by one. This can have unintended effects if calculations are being performed in this procedure. We will examine this aspect further in upcoming articles.

   12. Save the Report and open it in Print Preview.
   13. Check whether the Report Lines associated with OrderIDs 10702, 10625 & 10573 are still appearing on the Report or not.

   The Parameter Table

   We were using Constant values for OrderIDs in the Program to check and suppress the Report lines from appearing on the Report. We cannot expect the User to open the Report in Design View and change the Program every time to change the OrderIDs, if it becomes necessary, before printing the Report. Besides, there may be more than two or three Orders to hide this way. We must provide a much simpler method to the User to input the OrderIDs as parameters to the Program and run the Report based on that.

   We will create a parameter table with the key in OrderIDs, read it in the program, compare it to the Report Order IDs, and skip those lines on the Report for matching cases.

   1. Create a Table with the name OrderParam with a single Field with the name OID with Data Type Number and Field Size Long Integer.
   2. Select Primary Key from the Edit Menu to define this field as a Primary Key Field. This will prevent duplicate values from going into the Parameter Table and make it easier to cross-check OrderIDs from the Report.

      Save the Table and open it in Data Sheet View.

   3. Key in the OrderIDs 10702, 10625, and 10573 (or any other OrderIDs you would like to hide) in the OrderParam Table.

      Design a Continuous Form for the Table and create a Command Button at the Footer Section of the Form with HyperLinks to the Orders Report so that the User can launch the Report from the Parameter Screen itself.

      The VBA Code

   4. Copy and paste the following Code in Report#39's Code Module, replacing the earlier Code:

      'Global declarations
      Dim cdb As Database, rst As Recordset
      
      Private Sub Report_Open(Cancel As Integer)
           Set cdb = CurrentDb
          Set rst = cdb.OpenRecordset("OrderParam")
          rst.Index = "PrimaryKey"
      End Sub
      
      Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
      rst.Seek "=", [OrderID]
      If Not rst.NoMatch Then
         Report.Section(acDetail).Visible = False
      Else
          Report.Section(acDetail).Visible = True
      End If
      End Sub
      
      Private Sub Report_Close()
         rst.Close
         Set rst = Nothing
         Set cdb = Nothing
      End Sub

   5. Save the Report with the new Code and open it in Print Preview.
   6. Look for the OrderIDs in the OrderParam Table to check whether they are really suppressed from the Report or not.

   Let us examine what we did in the above Code.

   The VBA Code Review

   • We have declared the Database and Recordset objects at the global level of the report module so they can be referenced in all other subroutines.

   • In the report’s Open event procedure, we open the OrderParam table, activate its primary key index, and keep it in memory.

   • During the Detail Section Format event procedure, we cross-check each OrderID against the contents of the OrderParam table. If an OrderID matches a value in the table, the corresponding report line is hidden. This process continues for all records in the report.

   • The OrderParam recordset is closed in the report’s Close event procedure.

   Because this checking process references an external table for each record in the report’s source data, there may be a slight delay before the preview or print action completes.

   Next, we will explore how this method works with Customer Level Summary Totals in the Customer Footer Section of the Report.

   • Synchronized Floating Popup Form
   • Forms and Custom Properties
   • Control Tip Text and Time Delay
   • External Files List in Hyperlinks
   • Combo Box Column Values

Network And Print Page Setup-3

Introduction

Shared MS Access Reports are typically designed for a specific printer on the network. When users attempt to print the report to a different printer, the report automatically adopts that printer’s default page settings—such as paper size, page orientation, and margins—which may cause the report to print incorrectly.

In earlier posts, we learned how to modify page setup values (paper size, page orientation, and margin settings) of reports through VBA code before printing them on network-based printers.

However, the topic remains incomplete without discussing how to change column settings (accessible via File → Page Setup → Columns) programmatically for multi-column reports.

The column settings defined under the Columns tab in the Page Setup dialog are controlled by the PrtMip property of the report. If you followed the previous tutorial on margin settings, you have already created the user-defined data types needed to copy and modify the PrtMip property values in memory. In that case, all you need now is a new Subroutine that adjusts the report’s column settings for a multi-column layout and opens it in Print Preview.

Readers have not visited the earlier Pages

For the benefit of readers who have arrived directly on this page, I will reproduce the first part of the code below—the segment that declares the two user-defined data types.

Open the VBA Editor (press Alt + F11), insert a new Standard Module (Global Module) by selecting Insert → Module, and then copy and paste the following code (from both code windows) into the module, immediately below the global declaration line:

Option Compare Database

If you have already copied this first part while working on the Margin Settings example, then simply take the code from the second code window below and paste it into the same module where you previously placed the PrtMip property data type declarations.

Alternatively, you may paste it into a different Global Module if you prefer to keep the code segments separate.

Private Type str_PRTMIP
    strRGB As String * 28
End Type

Private Type type_PRTMIP
    xLeftMargin As Long
    yTopMargin As Long
    xRightMargin As Long
    yBotMargin As Long
    fDataOnly As Long
    xWidth As Long
    yHeight As Long
    fDefaultSize As Long
    cxColumns As Long
    yColumnSpacing As Long
    xRowSpacing As Long
    rItemLayout As Long
    fFastPrint As Long
    fDatasheet As Long
End Type

Public Sub PageColumns(ByVal strName As String)

    Dim PrtMipString As str_PRTMIP
    Dim PM As type_PRTMIP
    Dim rpt As Report
    Const PM_HORIZONTALCOLS = 1953
    Const PM_VERTICALCOLS = 1954
    Const TWIPS = 1440

   ' Open the report.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)
    PrtMipString.strRGB = rpt.PrtMip
    LSet PM = PrtMipString

    ' Create two columns.
    PM.cxColumns = 2

    ' Set 0.25 inch between rows.
    PM.xRowSpacing = 0.25 * TWIPS

   ' Set 0.5 inch between columns.
    PM.yColumnSpacing = 0.5 * TWIPS
    PM.rItemLayout = PM_VERTICALCOLS

    ' Update property.
    LSet PrtMipString = PM
    rpt.PrtMip = PrtMipString.strRGB

    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview

    Set rpt = Nothing

End Sub

Create a Sample Two-Column Labels Report.

The next step is to create a multi-column sample report to test our program. We’ll design an Address Labels report using the Employees table imported from the MS Access sample database Northwind.mdb.

1. Import the Employees Table:
   Go to File → Get External Data → Import and import the Employees table from:

   C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb
   

2. Create the Report:
   Click on the Employees table, then select Report from the Insert menu.

3. Choose the Design View:
   From the list of displayed options, select Design View.

4. Set Up the Report Layout:
   We will design the address labels manually. However, you may use the Label Wizard instead.

5. Remove Page Header/Footer:
   From the View menu, uncheck the Page Header/Footer option to remove those sections from the report design.

6. Design the Label:
   Create a sample label layout in the Detail section of the report, as shown in the image below.

1. Sample Label and Page Setup in one Image

   

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

   
   

   The Design Task

2. Create four text boxes, each approximately 2.75 inches wide and 0.25 inches high, and arrange them close together as shown in the design layout.

   Open the Property Sheet for each text box (View → Properties) and do the following:

   • In the first and third text boxes, enter the appropriate expressions.

   • In the second and fourth text boxes, set the Control Source to the Address and Country fields, respectively.

3. Click outside the Text boxes and drag the mouse over them to select all four Text Boxes together.

4. Display the Property Sheet of the TextBoxes (View -> Properties).

5. Change the Border Color Property value to 0.

6. Open the Page Setup Dialog Box from the File Menu and select the Columns Tab.

7. Change the values on the controls as shown in the image above.

   Look closely at the Columns Tab settings to understand how these values affect the printed layout of the labels.

   The labels will be printed in two columns, with half an inch of horizontal spacing between them. The vertical spacing between labels is set to a quarter of an inch. Each label is 3.25 inches wide and 1.4 inches high, including the blank space surrounding the text boxes.

   The Column Layout setting—Across, then Down, as it determines how the labels are arranged on the report. With the current setting, labels are placed across the page first, and then continue down to the next row. However, we prefer the opposite layout—Down, then Across—so that the labels fill the left column first before continuing into the second column. We’ll modify this behavior through our program.

8. You may set the Margin Values to 0.5 Inches on all four sides of the Margins Tab.

9. Select A4 Paper Size and Orientation to Portrait on the Page Tab and close the Page Setup Dialog Box.

10. Click on the Sorting and Grouping Toolbar Button above. You can find this icon at the top right end (next to the Toolbox Button icon) in the above image.

11. Select FirstName in the Field/Expressions Column and Ascending in the Sort Order Column.

12. Save the Report with the name MyLabels or any other name you prefer.

13. Open the Report in Print Preview and check the arrangement of Labels and values in them. The Employee Names were sorted in the First Name order on the Report. The Label arrangement is now Across, Down order.

    Preparing for Test Run of Program

14. Close the Report Preview and open it in Design View.

15. Open the Page Setup Dialog Box and select the Columns Tab.

16. Change the Row Spacing Value to 0 Inches and the Column Spacing Value to 0.1 Inches.

17. Leave the Column Layout Value (Across, then Down) setting.

    The Column Layout Value and the Column & Row Spacing Values will change through the Program.

18. You can run the Program PageColumns() directly from the Debug Window (Immediate Window) for testing. Press Alt+F11 Keyboard Shortcut to display the VBA Editing Window, and press Ctrl+G to bring up the Debug window.

19. Type PageColumns "MyLabels" in the Debug Window and press Enter Key.

Using the PageColumns() program, we have now changed the report’s column layout to Down, Across, and restored the column and row spacing of the labels to the original values specified earlier in the Page Setup dialog box.

You can run the Program through a Button Click Event Procedure from your Main Switchboard by adding the following lines of sample code:

Private Sub cmdPreview_Click()
 PageColumns "YourReportName"
End Sub

You can call the PageColumns() Program and pass the Report Name as a parameter when the user attempts to Print Address Labels. This will ensure that the Address Labels are printed with the correct settings on any Printer on the Network.

NB: The User must select the printer (if more than one Printer is installed on the user's machine) and set it up as the Default Printer before attempting to print the Report.

• Forms and Custom Properties
• Control Tip Text and Time Delay
• External Files List in Hyperlinks
• Combo Box Column Values
• Drill-Down Inquiry Screen-2

Network And Print Page Setup-2

Continued from Last Week's Page.

In an earlier article, we discussed how to automatically change the paper size and page orientation of MS Access reports through VBA code for any printer on the network. We achieved this by copying the PrtDevMode property values into memory, modifying them to match the desired paper size and page orientation, and then updating them back into the report’s page settings before printing it on the user’s default printer.

Working with the PrtMip Property

We will have a similar exercise to change the Margin Settings of the MS-Access Report through the program. This time, we need to work with the PrtMip Property of the Report to change the Margin Values.

The procedure is similar to the previous example. The steps taken in the Program are as follows:

• Open the Report in Design View.

• Copy the PrtMip Property Values of the Report into a 28-byte-long, String Variable and move it into a redefined structured data area for modification.

• Change the required Margin Values in Memory.

• Update them back into the Report's PrtMip Property.

• Save the Report with the changes and open it in Print Preview.

Prepare for a Demo Run

So let us start.

1. Open one of your Databases with Reports in it.

2. Display the Visual Basic Editing Window (Alt+Fll).

3. Insert (Insert -> Module) a new Standard Module (Global Module).

4. Copy and paste the following code into the new Module and save it. 

   Private Type str_PRTMIP
       strRGB As String * 28
   End Type
   
   Private Type type_PRTMIP
       xLeftMargin As Long
       yTopMargin As Long
       xRightMargin As Long
       yBotMargin As Long
       fDataOnly As Long
       xWidth As Long
       yHeight As Long
       fDefaultSize As Long
       cxColumns As Long
       yColumnSpacing As Long
       xRowSpacing As Long
       rItemLayout As Long
       fFastPrint As Long
       fDatasheet As Long
   End Type
   
   Public Sub SetMargins(ByVal strName As String)
   
   Dim PrtMipString As str_PRTMIP
   Dim PM As type_PRTMIP
   Dim rpt As Report
   Const TWIPS As Long = 1440
       ' Open the report.
       DoCmd.OpenReport strName, acDesign
       Set rpt = Reports(strName)
       PrtMipString.strRGB = rpt.PrtMip
       LSet PM = PrtMipString
   
       ' Set margins.
       PM.xLeftMargin = 0.75 * TWIPS
       PM.yTopMargin = 0.5 * TWIPS
       PM.xRightMargin = 0.5 * TWIPS
       PM.yBotMargin = 0.5 * TWIPS
   
      ' Update property.
       LSet PrtMipString = PM
       rpt.PrtMip = PrtMipString.strRGB
   
       DoCmd.Close acReport, strName, acSaveYes
       DoCmd.OpenReport strName, acViewPreview
   
       Set rpt = Nothing
   
   End Sub

5. Open one of your existing Reports in Design View.

6. Select File -> Page Setup -> Margins.

7. Change all four sides (Left, Right, Top, and Bottom) of the Margin settings to 1 Inch.

8. Save and Close the Report.

9. Open the Main Switchboard Form of your Application or create a new Form.

10. Create a new Command Button on the Form.

11. While the Command Button is in the selected state, display the Property Sheet (View ->Properties).

12. Change the Name Property Value to cmdPreview and change the Caption Property Value to Print Preview.

13. Display the Code Module of the Form (View -> Code).

14. Copy and paste the following lines into the Code Module of the Form. 

    Private Sub cmdPreview_Click()
      SetMargins "MyReport"
     End Sub

15. Replace the name MyReport with your own Report Name.

16. Save and close the Form.

17. Open the Form in normal view and click on the Command Button to run the Program and change all four margins of the Report to new values, and open the Report in Print Preview.

Close the Report and open it again in Design View and check whether the margin settings have really changed through the program or not.

Note: If any value falls below the allowable range defined by the printer driver settings, the printer may automatically adjust it to the minimum acceptable value. As a result, you might notice that some settings appear higher than the values you originally specified.

Running SetMargins() Program from PaperandOrient() Sub-Routine

You can do a sample run of the Program by typing SetMargins "YourReportName" in the Debug Window directly, without the use of a Form or a Command Button.

You can run this program from within the earlier PaperAndOrient() Program to change the Margins also along with the Paper Size and Page Orientation. All three sets of values can be changed by calling the PaperAndOrient() Program alone.

The modified PaperAndOrient() Program is given below:

Public Sub PaperAndOrient(ByVal strName As String)
    Const DM_PORTRAIT = 1
    Const DM_LANDSCAPE = 2
    Const DM_PAPERSIZE = 9
    Dim DevString As str_DEVMODE
    Dim DM As type_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report

   ' Opens report in Design view.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)

    If Not IsNull(rpt.PrtDevMode) Then
        strDevModeExtra = rpt.PrtDevMode
        DevString.RGB = strDevModeExtra
        LSet DM = DevString
        DM.lngFields = DM.lngFields Or DM.intOrientation

        ' Initialize fields.
        DM.intPaperSize = DM_PAPERSIZE
        If DM.intOrientation = DM_PORTRAIT Then
            DM.intOrientation = DM_LANDSCAPE
        End If

       ' Update property.
        LSet DevString = DM
        Mid(strDevModeExtra, 1, 94) = DevString.RGB
        rpt.PrtDevMode = strDevModeExtra
    End If
    SetMargins strName
    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview
    Set rpt = Nothing

End Sub

 

Public Sub SetMargins(ByVal strName As String)
    Dim PrtMipString As str_PRTMIP
    Dim PM As type_PRTMIP
    Dim rprt As Report
    Const TWIPS As Long = 1440

    Set rprt = Reports(strName)
    PrtMipString.strRGB = rprt.PrtMip
    LSet PM = PrtMipString

   ' Set margins.
    PM.xLeftMargin = 0.75 * TWIPS
    PM.yTopMargin = 0.5 * TWIPS
    PM.xRightMargin = 0.5 * TWIPS
    PM.yBotMargin = 0.5 * TWIPS

   ' Update property.
    LSet PrtMipString = PM
    rprt.PrtMip = PrtMipString.strRGB

    Set rprt = Nothing

End Sub

The Measurement Unit is Twips.

The measurements of reports and their objects are handled internally in twips, rather than in inches or millimeters. Although you can specify measurements in standard units such as inches, centimeters, or other regional formats on the property sheets of reports, forms, or controls, MS Access automatically converts these values into twips internally. However, when working in VBA, this conversion must be done manually before changing the property values of objects.

• 1 Inch = 1440 Twips
• 1 Inch = 72 Points
• 1 Point = 20 Twips OR 1 Twip = 1/20 Point

For simplicity, we have used constant values in the program for page size, orientation, and margins. However, you can modify the code to pass these values as parameters—along with the report name—when calling the program for each report. This approach offers greater flexibility, allowing the same program to handle reports with different page settings.

Next, we’ll explore how to modify the values on the Columns tab of the Page Setup dialog box, available from the File menu.

Earlier Post Link References:

• Control Tip Text and Time Delay
• External Files List in Hyperlinks
• Combo Box Column Values
• Drill-Down Inquiry Screen-2
• Drill-Down Inquiry Screen

Network And Report Page Setup

Introduction

When an MS-Access Application is installed on a network, security is one of the major issues that the Database Developer has to tackle. This includes the security of data and objects within the Database and the Database file itself. To learn more about securing a database on a Network, visit the following link:

Microsoft Access Security.

We’re now going to address another common issue often faced by users, which is usually resolved temporarily through alternative methods. When MS Access reports are designed for a specific printer on the network and all users share that printer, there are generally no problems. However, if users attempt to print the report on a different printer—either another network printer or a local printer—the report may not print correctly. Differences in default paper size, page orientation, or margin settings between printers can cause the report layout to appear incorrect when printed.

To work around this issue, users typically need to preview the report, open the Page Setup menu if necessary, and manually adjust the paper size, page orientation (portrait or landscape), and margins before printing. This option is only available if the Report Page Setup command is accessible to the user. If the application uses customized menus and toolbars, this option may not be visible, complicating the process further.

For more details on Customized Menus and Toolbars, visit the following Links:

• Calendar and Toolbars
• Custom Menus and Toolbars
• Custom Menus and Toolbars-2

Reports PrtDevMode Property

To make printing easier for users, we can modify the PrtDevMode property of a report through a program to automatically adjust critical parameters—such as paper size, page orientation (portrait or landscape), and margins—before sending the report to the printer. This ensures that the report prints correctly on any printer.

The PrtDevMode property is a 94-byte structure containing multiple parameters that can be modified via code to control how the printer behaves.

For this example, we’ll focus on two simple parameters for our report. Our sample report is designed in landscape mode and is to be printed on A4 paper (210 × 297 mm). We must modify the following member parameters of the PrtDevMode property of the default printer:

• Orientation - Valid Values: 1 = Portrait, 2 = Landscape.
• PaperSize  9 = A4 (210 x 297 mm)

Working with the Report.PrtDevMode Property Values

The above options (Orientation and Paper size) appear on the Page tab in the Page Setup Dialog box in the File Menu. We are trying to change these values at run-time through the Program.

Open a new Standard Module (Global Module) in your Database and copy the following code into the module and save it.

Private Type str_DEVMODE
    RGB As String * 94
End Type

Private Type type_DEVMODE
    strDeviceName As String * 16
    intSpecVersion As Integer
    intDriverVersion As Integer
    intSize As Integer
    intDriverExtra As Integer
    lngFields As Long
    intOrientation As Integer
    intPaperSize As Integer
    intPaperLength As Integer
    intPaperWidth As Integer
    intScale As Integer
    intCopies As Integer
    intDefaultSource As Integer
    intPrintQuality As Integer
    intColor As Integer
    intDuplex As Integer
    intResolution As Integer
    intTTOption As Integer
    intCollate As Integer
    strFormName As String * 16
    lngPad As Long
    lngBits As Long
    lngPW As Long
    lngPH As Long
    lngDFI As Long
    lngDFr As Long
End Type

Public Sub PaperAndOrient(ByVal strName As String)
    Const DM_PORTRAIT = 1
    Const DM_LANDSCAPE = 2
    Const DM_PAPERSIZE = 9
    Dim DevString As str_DEVMODE
    Dim DM As type_DEVMODE
    Dim strDevModeExtra As String
    Dim rpt As Report

   ' Opens report in Design view.
    DoCmd.OpenReport strName, acDesign
    Set rpt = Reports(strName)

    If Not IsNull(rpt.PrtDevMode) Then
        strDevModeExtra = rpt.PrtDevMode
        DevString.RGB = strDevModeExtra
        LSet DM = DevString
        DM.lngFields = DM.lngFields Or DM.intOrientation
        'Initialize fields.
        DM.intPaperSize = DM_PAPERSIZE
        If DM.intOrientation = DM_PORTRAIT Then
            DM.intOrientation = DM_LANDSCAPE
        End If

        ' Update property.
        LSet DevString = DM
        Mid(strDevModeExtra, 1, 94) = DevString.RGB
        rpt.PrtDevMode = strDevModeExtra
    End If
    DoCmd.Close acReport, strName, acSaveYes
    DoCmd.OpenReport strName, acViewPreview
    Set rpt = Nothing

End Sub

The User-Defined Types str_DEVMODE and Type_DEVMODE

At the beginning of the code, two new user-defined data types, str_DEVMODE and type_DEVMODE, are declared. The report’s PrtDevMode property value is copied into this structured data area, allowing us to modify specific elements and update them back into the report before printing.

Within str_DEVMODE, a member named RGB is defined as a 94-byte string. This 94-byte area contains 26 different parameters of various data types and sizes, which are individually defined under the type_DEVMODE structure. By transferring the data from str_DEVMODE (a single block of 94 characters) into type_DEVMODE, we can modify individual parameter values before writing them back into the report’s Page Setup.

NB: If the Database is implemented with Microsoft Access Security, then all Users must have the Report Design Change Authority to run this procedure.

Preparing for a Trial Run

1. To try out our Program, open one of your Reports with Landscape Page Orientation in Design View.

2. Select Page Setup from the File Menu.

3. Select the Page Tab on the Dialog Box.

4. Change Orientation to Portrait.

5. Change Paper Size to A6.

6. Save the Report and open it in Print Preview to check how it looks with the change.

7. Close the Report after viewing.

8. Create a Command Button on an existing Form or on a new Form and keep the Form in Design View.

9. Display the Property Sheet of the Command Button (Alt+Enter or View -> Properties).

10. Change the Name Property Value to cmdPreview.

11. Copy and paste the following code into the Code Module (View -> Code) of the Form.

    Private Sub cmdPreview_Click()
         PaperAndOrient "MyReport"
    End Sub
    

12. Replace "MyReport" with your own Report Name.

13. Save the Form and open it in a normal view.

14. Click the cmdPreview button to run the program, to change the Page setup correctly, and open it in Print Preview.

Type PaperAndOrient "MyReport" in the Debug Window (Ctrl+G) and press Enter to run the Program directly without the Form and Command Button.

Open the Report again in design view and check whether the erraneous changes that you have made manually in the Page Setup Dialog Box, to test the program, have now been corrected through the program or not.

Next, we will see how to change the values on the Margins tab of the Page Setup Dialog Box through the Program.

Earlier Post Link References:

• External Files List in Hyperlinks
• Combo Box Column Values
• Drill-Down Inquiry Screen-2
• Drill-Down Inquiry Screen
• Command Button Animation-2