Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Network And Print Page Setup-3


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.

  1. Import (File - - >Get External Data - - >Import) the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb Sample Database.

  2. Click on the Employees Table and select Report from Insert Menu.

  3. 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.

  4. Remove the checkmark from the Page Header/Footer Option of the View Menu to remove the Page Header/Footer Sections from the Report Design.

  5. 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

  6. 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.

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

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

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

  10. Change the Border Color Property value to 0.

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

  12. 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.

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

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

  15. 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.

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

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

  18. 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

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

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

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

  22. 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.

  23. 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.

  24. 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.


No comments:

Post a 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