Network And Print Page Setup-3 ~ LEARN MS-ACCESS TIPS AND TRICKS

Friday, June 19, 2009

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.

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
```
Create the Report:
Click on the Employees table, then select Report from the Insert menu.
Choose the Design View:
From the list of displayed options, select Design View.
Set Up the Report Layout:
We will design the address labels manually. However, you may use the Label Wizard instead.
Remove Page Header/Footer:
From the View menu, uncheck the Page Header/Footer option to remove those sections from the report design.
Design the Label:
Create a sample label layout in the Detail section of the report, as shown in the image below.

Sample Label and Page Setup in one Image

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

The Design Task
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.
Click outside the Text boxes and drag the mouse over them to select all 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 the File Menu and select the Columns Tab.
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.
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) in 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, 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.
The Column Layout Value and the Column & Row Spacing Values will change through the 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.

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.

LEARN MS-ACCESS TIPS AND TRICKS

Friday, June 19, 2009