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 security of data and objects within the Database and the Database file itself. To learn more about securing a Database on Network click here.

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, any of the Users tries 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 may not print 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 for the Application this option probably may not appear in them.

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

Calendar and Toolbars

Custom Menus and Toolbars

Custom Menus and Toolbars-2

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 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 need to print on 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)

The above options (Orientation and Paper size) are appearing on the Page Tab of the Page Setup Dialog Box of File Menu. We are trying to change these values at runtime through 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

At the beginning of the Code two new User Defined Data Type str_DEVMODE and type_DEVMODE are declared. The Report PrtDevMode Property Value is 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 defined accordingly under 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 Report Design Change Authority to run this procedure.

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

 

2. Select Page Setup from 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 normal view.

 

14. 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 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 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 Program.