June 2009 ~ LEARN MS-ACCESS TIPS AND TRICKS

Hiding Report Lines Conditionally

a.p.r. pillaiJune 26, 2009msaccess reporttricks, Reports 1 comment

Introduction.

Hiding Report lines or Report Sections conditionally at run time may not be an everyday requirement. But, if it becomes necessary, it is interesting to know the method to do it, with the help of Programs. One thing I like very much about MS-Access Report is its ability to write programs on the Report itself, especially for highlighting very critical information on the Report so that the attention of Users can be drawn to specific information on it. You can see an interesting and useful example in one of the earlier Posts with the Title: Highlighting Reports.

To suppress something on the Report from printing on the Report we normally think about using a Query to filter out unwanted items from Source Data before Previewing or Printing the Report. Frankly, Query is the best method compared to what I am trying to show you here. To get the same result on the Report without filtering the source data we have to play around with a few tricks (after all, this is all about Tips & Tricks, right?).

If you really enjoy working with MS-Access programs, then read on. As far as the program code is concerned, there is nothing complicated to deal with, all we need is a few lines of simple code and even a VBA beginner can easily understand them.

It is interesting to do something different, which not only kills the boredom of doing the same thing repeatedly but also gives you better insight into doing things better next time.

Design a Sample Report.

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

Import Orders and Employees, Tables from C:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb sample Database.
We will not be using the Employees Table directly on the Report. Without the Employees Table in the Database, only Employee IDs will be displayed on the Report when the Report is designed with Orders Table. There is a reference to the Employees Table (Combo Box uses Employees Table) in the EmployeeID Field in Orders Table.
Click on the Orders Table and select a Report from Insert Menu.
Select Report Wizard from the displayed list and click OK.
Select the following Data Fields from the Available Fields list and move them one by one to the Selected Fields List:
- OrderID
- CustomerID
- EmployeeID
- OrderDate
- RequiredDate
Click Next and select CustomerID as Grouping Level and move it to the right. Click Next Command Button.
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:
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;s 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.
Open your Report in Design View and display the Code Module (View - - >Code).
Copy and Paste the above Code into the Report Module.
Select Close and Return to Microsoft Office Access from 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 Database Window and VBA Window. Visit the Page Keyboard Shortcuts to learn more about Keyboard Shortcuts.
Click on the Detail Section of the Report and display the Property Sheet (View - - >Properties).
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 the Report before it is actually displayed or Printed. First, the Format pass lays out each Report Section's contents doing calculations, if needed, for Summary information (we will deal with this in the forthcoming Articles). The Print action takes place in the second pass, which prints the contents of the Report before Previewing or sending it to the Printer.
The Retreat Event takes place when you move the Page Preview to an earlier Page. At this time the Format action takes place a second time for the Retreated Page and the FormatCount Parameter value in the Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) is incremented by one. This has some negative impact if we are performing some calculations in the above Procedure. This aspect we will examine in the forthcoming Articles.
Save the Report and open it in Print Preview.
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 to the key in OrderIDs, read it in the program and compare it to the Report Order IDs and skip those lines on the Report for matching cases.

Create a Table with the name OrderParam with a single Field with the name OID with Data Type Number and Field Size Long Integer.
Select Primary Key from Edit Menu to define this field as a Primary Key Field. This will prevent duplicate values from going into the Parameter Table and makes it easier to cross-check OrderIDs from Report.
Save the Table and open it in Data Sheet View.
Key in the OrderIDs 10702, 10625, and 10573 (or any other OrderIDs you would like to hide) in the OrderParam Table.
You may even 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

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

Save the Report with the new Code and open it in Print Preview.
Look for the OrderIDs given 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 that we can refer to these objects in all other Sub-Routines.
Opening the OrderParam Table and activating the Primary Key Index on the Report's Open Event Procedure and keeping its memory.
We cross-check each OrderID with the OrderParam Table contents for the Report's Detail Section Format Event Procedure and hide the Report line for the Order Id that matches with the OrderParam value. This process continues till it reaches the end of the Report.
The OrderParam Recordset is closed in the Close Event Procedure of the Report.

Since the checking process needs referencing an external table for each record of the Report Source Data there will be a slight delay before the Previewing or Printing action takes place.

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

Network And Print Page Setup-3

a.p.r. pillaiJune 19, 2009msaccess reports, Reports No comments

Introduction

Shared MS-Access Reports are normally designed for a particular Printer on the Network. When Users attempt to print this Report on a different Printer the Report will change to the default Page Settings of that Printer, like Paper Size, Page Orientation, and the Report may not print correctly.

We have familiarized how to change Page Setup Values (Paper Size, Page Orientation, and Margin Settings) of Reports through VBA Programs before printing the Report on Network-based Printers in the earlier Posts.

This subject is not complete unless we touch on the topic of Columns Setting changes (File - - > Page Setup - - > Columns) through the Program for multi-column Reports.

The Columns Tab values of Page Setup Dialog Control are also modified through the PrtMip Property Values of Report. If you have gone through the earlier Post on Margin Settings then you have already created the User Defined data types to copy PrtMip Property Values into memory for modification. In that case, you need only a new Sub-Routine to modify the Report Column settings of a multi-column Report and open it in Print Preview.

For Readers not Visited earlier Pages

For the benefit of the readers who have landed on this page directly; I will reproduce the first part of the Code (declaring two User-defined data types segment) below.

Open the VBA Editing Window (Alt+F11); insert a new Standard Module (Global Module) - (Insert - - > Module), copy and paste the following Code (from both Code windows) into the Module immediately below the global declaration line Option Compare Database.

Those who have already copied the first part for the Margin Setting example take the code from the second Code window and paste it into the same Module where you have placed the data type declarations for PrtMip Property earlier. You can paste the code into a different Global Module if you prefer to do so.

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 can design a Report for Address Labels with the Imported Employees Table from the MS-Access sample database Northwind.mdb.

Import (File - - >Get External Data - - >Import) the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb Sample Database.
Click on the Employees Table and select Report from Insert Menu.
Select Design View from the displayed list of options.
We are going to design Address Labels manually. If you prefer the Label Wizard in the displayed list you may use that instead.
Remove the checkmark from the Page Header/Footer Option of the View Menu to remove the Page Header/Footer Sections from the Report Design.
Design a sample Label in the Detail Section of the Report as shown in the Image given below.

Sample Label and Page Setup in one Image

The Design Task
Create four Text Boxes about the size of 2.75 inches long and 0.25 Inches high each and arrange them close by as shown on the design.
Display the Property Sheet of each Text Box (View - ->Properties) and write the expressions in the top and third TextBoxes and insert the Field names Address and Country in seconds and fourth Text Boxes respectively.
Click outside the Text boxes and drag the mouse over them to select all the four Text Boxes together.
Display the Property Sheet of the TextBoxes (View - -> Properties).
Change the Border Color Property value to 0.
Open the Page Setup Dialog Box from File Menu and select Columns Tab.
Change the values on the controls as shown in the image above.
Look carefully at the settings in the Columns Tab to understand what would be the result of those values, when the Labels are printed.
The Labels will be printed in two columns and the spacing between both Columns will be half an inch wide. The vertical spacing between labels will be a quarter of an inch. The Width of a Label is 3.25 Inches and 1.4 Inches high, including the blank spaces around the text boxes

The Column Layout (Across, then Down) setting is very important to note because the arrangement of Labels on the Report depends on it. The current settings translate into arranging the labels across then down order rather than printing the labels at the left column fully then flowing out into the second column later (Down, then Across). We prefer the second option, which we will change through our Program.
You may set the Margin Values to 0.5 Inches on all four sides of the Margins Tab.
Select A4 Paper Size and Orientation to Portrait on the Page Tab and close the Page Setup Dialog Box.
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) on the above image.
Select FirstName in the Field/Expressions Column and Ascending in the Sort Order Column.
Save the Report with the name MyLabels or any other name you prefer.
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, then Down order.

Preparing for Test Run of Program
Close the Report Preview and open it in Design View.
Open the Page Setup Dialog Box and select the Columns Tab.
Change the Row Spacing Value to 0 Inches and the Column Spacing value to 0.1 Inches.
Leave the Column Layout Value (Across, then Down) setting as it is.
The Column Layout Value and the Column & Row Spacing Values will change through Program.
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.
Type PageColumns "MyLabels" in the Debug Window and press Enter Key.

Through the PageColumns() Program we have now changed the Report Column Layout to Down, then Across, and the Column, Row spacing of Labels to the original settings we have initially specified on 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 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 she has more than one Printer installed on her machine) and set it up as Default Printer before attempting to print the Report.

Network And Print Page Setup-2

a.p.r. pillaiJune 12, 2009msaccess reports, Reports No comments

Continued from Last Week's Page.

We have seen in the earlier Article how to change Paper Size and Page Orientation of MS-Access Reports automatically through the program for any Printer on the Network. We have copied PrtDevMode Property Values into Memory; modified them to match the Paper Size and Page Orientation of the Report and updated them back into the Report Page Settings before printing it on the default Printer installed on the User's Machine.

**Working with the PrtMip Property**

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

The procedure is almost the same as 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 Bytes 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.

Open one of your Databases with Reports in it.
Display the Visual Basic Editing Window (Alt+Fll).
Insert (Insert - - > Module) a new Standard Module (Global Module).

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

Open one of your existing Reports in Design View.
Select File - ->Page Setup - - > Margins.
Change all four sides (Left, Right, Top, and Bottom) of the Margin settings to 1 Inch.
Save and Close the Report.
Open the Main Switchboard Form of your Application or create a new Form.
Create a new Command Button on the Form.
While the Command Button is in the selected state, display the Property Sheet (View - - >Properties).
Change the Name Property Value to cmdPreview and change the Caption Property Value to Print Preview.
Display the Code Module of the Form (View - -> Code).
Copy and paste the following lines into the Code Module of the Form.
```
Private Sub cmdPreview_Click()
  SetMargins "MyReport"
 End Sub
```
Replace the name MyReport with your own Report Name.
Save and close the Form.
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 been really changed through the program or not.

Note: If any value is less than the allowable range, based on the printer driver settings, the printer may fix it to the allowable range automatically. In that case, you may find some value is greater than what you have 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 Form or 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 other Objects on it were measured internally in Twips rather than in Inches or Millimeters. We are allowed to change the measurements on the Property Sheets of Report, Form, or other Objects in standard measurements manually, like Inches or Centimeters, or in any other regional values. They are automatically converted into Twips internally. But, in programs, we have to do that work before changing the values of the property of objects.

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

We have used Constant values in the Program for Page Size, Orientation, and Margins for simplicity. You can modify the Code to pass these values as parameters along with the Report Name when the Program is called for each Report. This will give more flexibility and the Program can be called for Reports with different Page Settings.

Next, we will see how to change the values on the Columns tab of the Page Setup Dialog Box of the File Menu.

Earlier Post Link References:

Network And Report Page Setup

a.p.r. pillaiJune 05, 2009msaccess reports, Reports No comments

Introduction

When 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 are going to look into another issue, most often faced by Users and solved temporarily by alternative methods. When Ms-Access Reports are designed for a particular Printer on the Network and when all Users share the same Printer then there are no issues. But, if the Users try to print the Report on a different Printer then it is likely that the Report may not print correctly. The User may have access to different Printers on the Network or Local Printer. The default Paper Size, Page Orientation, or Margin Settings on these Printers can be different and the Report format may not appear correctly when printed.

To overcome this issue Users have to preview the Report, if necessary, open the Page Setup Menu and change Paper Size, Page orientation (Portrait or Landscape), and Margins before sending the Report to the Printer. This can be done only if the Report Page Setup Option is provided to the User. If Customized Menus and Toolbars are created in the Application this option probably may not appear in them.

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

Reports PrtDevMode Property

To make life easier for the User we can modify the PrtDevMode Property of the Report through Program to change some of the critical parameters automatically, like Paper Size, Page Orientation (Portrait or Landscape), and Margins before the Report is sent to the Printer. This ensures that the Report will print correctly on any printer.

The PrtDevMode Property of the Report is a 94 Byte long structure with several parameters that can be modified through the Program to make the Printer behave the way we want.

To try out an example, we will concentrate on two simple parameters for our Report. Our sample Report is designed in Landscape Mode and needs to print on an A4 (210 x 297 mm) size Pager. We must change 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 on 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 PrtDevMode Property Value has moved into this structured data area so that we can modify the required element's value and update them back into the Report before printing.

RGB is defined as a member of the str_DEVMODE with 94 Bytes long String data type. This 94 Byte data area consists of 26 different parameter values of various data types and sizes and is defined accordingly under the type_DEVMODE data structure. When we move the data from str_DEVMODE (a single block of 94 characters) into type_DEVMODE we can individually change the required value before updating it 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

To try out our Program open one of your Reports with Landscape Page Orientation in Design View.
Select Page Setup from File Menu.
Select the Page Tab on the Dialog Box.
Change Orientation to Portrait.
Change Paper Size to A6.
Save the Report and open it in Print Preview to check how it looks with the change.
Close the Report after viewing.
Create a Command Button on an existing Form or on a new Form and keep the Form in Design View.
Display the Property Sheet of the Command Button (Alt+Enter or View- - > Properties).
Change the Name Property Value to cmdPreview.
Copy and paste the following code into the Code Module (View - -> Code) of the Form.
```
Private Sub cmdPreview_Click()
     PaperAndOrient "MyReport"
End Sub
```
Replace "MyReport" with your own Report Name.
Save the Form and open it in a normal view.
Click on the cmdPreview button to run the program to change the Page setup correctly and open it in Print Preview.

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

Open the Report again in the design view and check whether the wrong 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.